Re: Surrogate Keys: an Implementation Issue

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 19 Jul 2006 21:39:00 GMT
Message-ID: <UDxvg.12548$pu3.287913_at_ursa-nb00s0.nbnet.nb.ca>


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?

Paul, I think you are confusing the logical level and the physical level. Whether one uses some kind of pointer (or any other structure) that is hidden from the user is entirely a physical consideration. Received on Wed Jul 19 2006 - 23:39:00 CEST

Original text of this message