Re: Surrogate Keys: an Implementation Issue
Date: Fri, 28 Jul 2006 08:32:30 -0500
Message-ID: <ead3t6$d34$1_at_nntp.aioe.org>
Keith,
The source of this discussion is mostly poor key selection. It has nothing
to do with surrogates. Many SQL products have value generators like
SEQUENCE, IDENTITY columns etc. And the columns using them are generally
non-updateable. So folks try to re-invent the concept of surrogate based on
such features and tend to draw conclusions to suit the product
specifications. That way, many can justify a particular design that uses
such features.
>> > Current: Jane Jones Married Proposed: Jane Smith Divorced
>
> Do Jane Jones and Jane Smith represent different people?
> Or did Jane Jones get Divorced?
To the user who knows Jane in person, they are the same. To the DBMS, the propositions are distinct.
>> Because the only key is
> mutable, there isn't enough information to answer that.
Mutability of an attribute has nothing to do with it. In fact there is no attribute that identifies Jane is declared to the DBMS at all.
>> > Another problem involves temporal constraints. Consider
> the following states for the people table and a transition
> constraint, Single people can't become Divorced:
>
> Current: Jane Jones Married Proposed: Jane Jones Married
> Current: Jane Smith Single Proposed: Jane Smith Divorced
>
Since the DBMS cannot identify them as separate propositions, this is a faulty premise to begin with. To the user (external ) it may make sense, but to the DBMS it does not ( internal predicate ).
This is not a case of temporal constraints. "Foundation of databases" has a set of mechanisms how the state changes are addressed by transition/temporal constraints by a variety of mechanisms.
>> > Should the proposed state be rejected? Or is it possible
> that Jane...
[brevity]
The whole paragraph is based on the faulty assumption that the external predicate should be understood by the DBMS even when no identifying attribute is declared to the DBMS.
>> > It should be obvious that surrogates solve these problems.
Actually no.
-- AnithReceived on Fri Jul 28 2006 - 15:32:30 CEST