Re: Normalization, Natural Keys, Surrogate Keys

From: drop the numbers <Paul>
Date: Wed, 22 May 2002 17:59:32 GMT
Message-ID: <>

In article <>, says...
> Database RI can only tell you that a particular value for a surrogate
> is not valid. It cannot prevent you from putting Atlanta in Oklahoma
> if the city and state tables are related only by a surrogate. (no
> relation to real tables, at least not in our systems).

        Neither will natural keys prevent someone from mis-associating Atlanta to Oklahoma.   

> My preference would be the natural key as the PK, and the surrogate as
> the "alternate". (but then you likely would not need the surrogate)

        Except maybe for usage in an application layer.  

> It's not just extra work. When ALL (or nearly all) relationships in a
> system are identified by surrogate keys (read sequence numbers) you
> get brain dead programmers using the sequence number from the
> customer's address as the key to finding his current products
> purchased. (sorry, didn't mean to rant.)

        Well, that silly. No matter what the relationship is based on, the proper query should properly span Customer and Order entities and return the necessary information. Surrogate versus natural keys has no effect on the possible loss of integrity on the output side of things.  

> (Did I go ballistic??)

        Not you. Plus, it's hyperbole... :)

> surrogate keys are okay in the proper place.
> I just think they are overdone in many cases.


(Any opinions expressed are strictly mine only and not my employer's)

Paul Tiseo, Intermediate Systems Programmer Reply To: (drop the numbers) Received on Wed May 22 2002 - 19:59:32 CEST

Original text of this message