Re: Surrogate Keys: an Implementation Issue


"Anith Sen" <anith@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:eaavt3$5c2$1@xxxxxxxxxxxxxxxx
I doubt that there are any such rdbms's today. It is like criticism
of
RT because of SQL shortcomings - the lack of faithful implementations
only serves to encourage naive extensions to RT.

The idea of hidden surrogates and associated myths is mistakenly nurtured
by
many who are intimately familiar with current crop of SQL products. For
instance, a cursory inspection of some sql programming newsgroups reveals
several blatant claims like:
-- Surrogates are immutable
-- Surrogates should not be exposed the user
-- Surrogates help performance
-- Surrogates belong to the physical model
-- Surrogates are never verifiable in reality
-- ...

In most cases, claimant misses the fundamental that a surrogate key is
nothing but a simple, stable and generally an irreducible candidate key.
And
that it has nothing to do with the physical model and that once used
outside
the DBMS, it is verifiable as well.

--
Anith



I think Anith's comments above are correct.


I've been skipping over this thread for the most part, because the signal to
noise ratio of the thread didn't look good to me.
So my comment may duplicate what somebody else already wrote.

The elephant in the living room here is whether a surrogate key identifies a
row one the one hand, or identifies a subject matter entity described by the
(key) contents of the row on the other. Comments like "updated the wrong
row" lead inescapably to the idea that there is a "right row", and that the
programmer coding an update has the responsibility to identify the right
row, and manipulate that row.

If surrogate keys are used as row identifiers, then they are not surrogates
for natural keys. They are indirect addressing of the row, as a data
structure. The indirection allows for a certain flexibility in reorganizing
data physically, without losing linkages. But other than that, it largely
misses the point of using either RDM or SQL. If you're going to link rows
with other rows, you're back to the graph data model, whether you know it
or not.

If on the other hand, surrogates are surrogates for natural keys, about
all they provide is immutability in the face of natural key mismanagement,
or real world collisions. That's very useful in some circumstances. But it
doesn't allow Dave to update "the correct row". It allows Dave to update
"the correct data".

An example of real world collisions is having a surrogate key for
Employee_ID that will survive the merger of Delta Airlines with Epsilon
Airlines. Data warehouses often use surrogate keys for this kind of
purpose. Then again, data warehouses often avoid updates completely,
except for error correction. So the issues raised by updates are moot in
that context.

But this has nothing to do with the substance of most of this discussion
(again, unless I missed a key part of the discussion).






.

Relevant Pages

  • Re: Surrogate Keys: an Implementation Issue
    ... RT because of SQL shortcomings - the lack of faithful implementations ... The idea of hidden surrogates and associated myths is mistakenly nurtured by ... stable and generally an irreducible candidate key. ... that it has nothing to do with the physical model and that once used outside ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... The idea of hidden surrogates and associated myths is mistakenly nurtured by many who are intimately familiar with current crop of SQL products. ... In most cases, claimant misses the fundamental that a surrogate key is nothing but a simple, stable and generally an irreducible candidate key. ... And that it has nothing to do with the physical model and that once used outside the DBMS, ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... a database that must provide a complete audit trail of every change. ... Natural keys are often mutable--especially compound keys. ... For myself I view surrogates as being useful in two situations, ... of integrity, given they are, at least initially, under the control of ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... The idea of hidden surrogates and associated myths is mistakenly nurtured ... -- Surrogates belong to the physical model ... If on the other hand, surrogates are surrogates for natural keys, about ... all they provide is immutability in the face of natural key mismanagement, ...
    (comp.databases.theory)