Surrogate Keys: an Implementation Issue
Date: 19 Jul 2006 08:44:20 -0700
Message-ID: <1153323860.787392.82180_at_75g2000cwc.googlegroups.com>
I'm working on a little RDBMs project, and I've been pondering the general arguments for and against surrogate keys, and have come to following conclusion:
The primary key should indeed be a natural key, if one is available, at the model level, but at the implementation level the table should have a system-supplied, auto-incremented, and hidden, "super primary key". This super primary key, (SPK) is then used when populating FKs in other tables (again, hidden from the user). It is up to the DBMS to do the necessary conversions to insulate the user from knowledge of the SPK. If there is no natural key, then the SPK can simply be exposed to the user as the PK for that table.
Thus, from the user's perspective, it appears that, say, Social Security Number is sprinkled across a dozen different tables, when under the covers its just meaningless 32 bit ints, and the SS number is only stored once in the Employee table.
This seems to give all of the advantages of surrogate keys, (very easy to change, saves on space) and the advantages of natural keys (meaningful, etc), and conversely, none of the drawbacks of either. In other words, the best of both worlds.
Do any current commercial RDBMSs already work this way under the covers? Received on Wed Jul 19 2006 - 17:44:20 CEST