>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.
Saludos, Hilmar.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
