Scout Database

[lewisandrea]

I am developing a database for my scout headquarters and it has the following structure that is shown in the attached image.
But we are facing a problem because we would like the ID of both the scout and the team to be unique and sequential.

Will it be possible?
I appreciate your help;)

[bcooney]

Well, first, why not just use http://troopmaster.com ?

 

What team?

[lewisandrea]

I will ponder with my team.

but is there a solution to my question?

[bcooney]

What do you mean by team?

You can assign validation to any field to ensure that it is unique. However, a troop number would not be used as a key in a relationship. Keys are unique and meaningless. all the fields in the above ERD with the suffix Id would be key values, and I would suggest using Get (UUID) as an auto-entered value. (Not a serial number )

[lewisandrea]

For example

Staff Member 1 - 35
Scout - 36
Staff Member 2 - 37
Staff Member 3 - 38
Scout - 39

it is currently impossible to make a unique sequence with the ID because I have 2 for the staff and the scouts

[comment]

This is very confusing. First you said "the scout and the team". Now it seems to be about staff members and scouts.

If staff members and scouts are records in the same table (they don't seem to be in the ERD you have posted), then they will share the same set of IDs. If you're using auto-entered serial numbers, then each ID will be unique - but the numbers of each subset will not be sequential. I don't see a good reason for this requirement. If you delete a record, then the sequence will be broken anyway, so why bother with it in the first place?

If staff members and scouts are records in separate tables, then the IDs can be sequential for each set (again, until you delete a record). You can use different prefixes in each table to make sure they are mutually unique - e.g. S005 for  a scout, P005 for a staff member. Or start the series at different numbers, so that they never overlap.

 

44 minutes ago, bcooney said:

I would suggest using Get (UUID) as an auto-entered value. (Not a serial number )

I would suggest exactly the opposite. Serial numbers are easy to check visually and faster to index. The only reason to use UUIDs, IMHO, is when you have more than one system creating new records independently.

 

Edited March 14, 2020 by comment

[lewisandrea]

Yes, basically that's it!

We have to use a letter to differentiate the staff from the scouts, but it was proposed to use only one serial for the entire database.

In other words, a scout with ID 75 will stay with him forever without the need to change.

[comment]

4 minutes ago, lewisandrea said:

a scout with ID 75 will stay with him forever without the need to change.

Why would it ever need to change?

[lewisandrea]

Yes just for that, and to be easier with the records

[comment]

I am afraid this is not making any sense to me.

 

[lewisandrea]

I understand, but we have other automatic systems that do not accept letters, that is, only numeric and when we say 75 to the system it does not know in which table to look for information.

[comment]

If they are in the same table, then there is only one table to look in. If they are in two tables, and you don't want to use letters, then use different series - e.g. start staff members from 1 and scouts from 10,000. I already mentioned this option earlier.