Re: Surrogate Keys: an Implementation Issue
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 20 Jul 2006 12:27:38 GMT
Message-ID: <_EKvg.12756$pu3.293026_at_ursa-nb00s0.nbnet.nb.ca>
>>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.
>>
>>Is this a bad conclusion?
>>
>>Do any current commercial RDBMSs already work this way under the
>>covers?
Date: Thu, 20 Jul 2006 12:27:38 GMT
Message-ID: <_EKvg.12756$pu3.293026_at_ursa-nb00s0.nbnet.nb.ca>
Damien wrote:
> Paul Mansour wrote: >
>>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.
>>
>>Is this a bad conclusion?
>>
>>Do any current commercial RDBMSs already work this way under the
>>covers?
> > Just a quick question. What if, for whatever reason, you did NOT want > "ON UPDATE CASCADE" semantics? It seems you cannot avoid them in your > situation...
Why do you say that? The delete will simply fail if referencing tuples exist. Received on Thu Jul 20 2006 - 14:27:38 CEST