In my data model I have some static data that is added to over time as the system changes. For instance:

create table region (id SERIAL,crname TEXT);

customer has a link to the region, my system adds regions over time. new regions are manually added by the dba executing an insert statement provided by the dev team for a release. I'm looking for a Chris Date style perspective on how this should be modeled and implemented. Should the key be managed by a database sequence where each database instance can have different keys for region "South West Denver" depending on when they are added, or is it acceptable to have key 32 for "South West Denver" in every instance of the region table for dev/test/qa/pre-prod etc? Effectively not using a sequence or database managed key.

  • Why not use the region name which is the natural key? It will eliminate all your distribution challenges, simplify your queries by eliminating the join, and explicitly enforce data consistency. – SQLRaptor Aug 9 at 18:36
  • then the name would be copied to other tables that deal with region. create table sales_rep( id SERIAL, name TEXT, region_id integer ); – Mark Lybarger Aug 9 at 20:49
  • what guarantee is there that "South West Denver" would have the exact same meaning in two different databases? – Jasen Aug 9 at 21:41
  • Correct. And what’s wrong with that? – SQLRaptor Aug 9 at 22:45

If the databases are independent from each other, there is no problem if the internal, automatically generated primary keys are different in different databases.

If you want to exchange data, it could be an advantage to use the same keys. One way of doing this is to use the name itself as the primary key, but that means that these names cannot be changed.

Other than that, you'd need a central service that deals out id numbers for regions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.