>>>What is the recommended way to design a database to deal with the following situation:
>>>
>>>A school system has a central database. Data is entered at member schools either directly into the master DB or into detached copies of the master DB. If copies are used, they are to be synchronized periodically with the master DB.
>>
>>Assign each school a unique number. Store it in variable gn_SchoolNumber.
>>
>>Have a function SerialNumber() that, each time it is run, gets the next available number from a table of sequence numbers.
>>
>>Make the primary key of each table where you need this functionality bintoc(gn_SchoolNumber, 2) + bintoc(SerialNumber("TableName")).
>>
>>This way, you require 6 bytes for each PK and FK. As a comparison, an integer-only key needs 4 bytes, a GUID requires 16 bytes.
>
>Thanks for the idea. I am still cooking them and have not made any decision, so all suggestions are welcome.
>
>Alex

"The PK is the field or fields that uniquely..." etc., but I prefer the singular version. With two fields, both the permanent relations and the joins become quite complicated - and probably less efficient, too.

That leaves three options that I am aware of.

1) GUID, or similar algorithm that practically guarantees a unique value anywhere in the world. The only disadvantage is the size. If the PK is big, the FK in all other tables is just as big.

2) Reserving ranges of numbers for each branch (school). The problem here is that it is hard to guess how much each table will grow.

3) My suggestion above. 2 bytes for the school leaves room for 65,000 schools; for bytes for the serial number allows 2e9 records per table.

This last solution seems ideal to me, but I don't have personal experience. And there may be other possibilities I am overlooking.

Hilmar.