Re: Surrogate Key vs Production Key

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<WKGdnU6c4swsT_bcRVn-qQ_at_comcast.com>...
>
> i'm jumping into this one a little late, so maybe i've missed a few
> comments...

Yeah. One very important one: why is it that no significant commercial app uses natural keys? Not one! Another even more significant one: why is it that Oracle's OWN apps do not use natural keys?

> the real issue is between natural key structures and surrogate key columns

You may re-label it as many times as you want, it ain't gonna change one yota...

> implementation options will always be debatable, and compromises (not design
> choices, always compromises) will need to be made when implementing the
> conceptual data model

Why? The conceptual model has NOTHING to do with implementation details. Absolutely nothing.

> but when establishing the data model, introducing a surrogate key column to
> replace a natural key column structure seriously weakens the data model and
> often hides (or impedes discovery of) very important facts about the
> business

Discovery of what? If you haven't found it at conceptual model time already, anything you find now probably is incorrect, a misconception or an arbitrary decision.

>
> by modeling this without a surrogate key, the business rule 'a person can
> only be assigned to use a vehicle that is assigned to her department' is
> easily enforced via the relationship 'each person may currently be assigned
> one and only one department vehicle'

"easily enforced". Above, it was "a serious fact about the business" that would be "hidden" by a surrogate key. You see, you can't just change the facts to match the example. Either you provide a concrete example of that "weakness", or you provide an example of a convenient way of specifying an arbitrary relationship. The two are not the same.

> modeling with a surrogate key, you get the structure:
>
> department_vehicle
> - dept_veh_id pk
> - dept_id -- fk to department
> - veh_id -- fk to vehicle
> - ...
>
> person
> - pers_id -- pk
> - dept_id -- fk to department
> - dept_veh_id -- fk to department_vehicle
> - ...
>
> which does not in itself document, enforce, describe, or inform anything
> specific about the person/department vehicle relationship and the person's
> assigned department

Neither does you earlier one. You arbitrarily decided that it was "easily enforced", but that is NOT by any means the same as asserting that the enforcement is somehow automatic with natural keys. And if it is not automatically enforced, then it is an arbitrary relationship. One that, being arbitrary, you can establish EQUALLY well with the surrogate keys above. No more no less.  

> time and time again i have seen very poor data models developed because an
> organization standardized on surrogate keys and replaced every multi-column
> primary key with a single-column surrogate key as soon as the entity was
> discovered

time and again I've seen very poor data models developed because an organization insists on using "natural keys" were there ain't any. And time and again I've seen every commercial system out there never using natural keys. I wonder why? Ah yes: they are ALL "very poorly" designed?

> specific implementation -- they should not be introduced into the conceptual
> data model,

did ANYONE at ANY STAGE ever suggest they should be introduced into the conceptual data model?

> and when introduced into the logical (or physical, depending on
> your semantics) data model, exactly what they are replacing should be
> thoroughly documented

Well, IF there is a conceptual data model then it is documented. Or is it not? Received on Wed Oct 13 2004 - 06:24:44 CDT