Re: Surrogate Keys: an Implementation Issue

"Paul Mansour" <paul_at_carlislegroup.com> wrote in message news: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.
>
> Is this a bad conclusion?
>
> Do any current commercial RDBMSs already work this way under the
> covers?
>

Can't wait until Celko weighs in. This is going to be interesting! The surrogate key/natural key mapping is terrible. Think about it. Now, there are very good reasons to employ a surragate key, but your idea is not one of them.

Rich

Rich Received on Wed Jul 19 2006 - 23:54:28 CEST