Re: Newbie question about db normalization theory: redundant keys OK?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 19 Dec 2007 11:25:18 -0400
Message-ID: <476937e0$0$5290$9a566e8b@news.aliant.net>

David Cressey wrote:

> "Kevin Kirkpatrick" <kvnkrkptrck_at_gmail.com> wrote in message
> news:40957504-43df-453b-8e8e-396f66b2b1e7_at_r29g2000hsg.googlegroups.com...
>

>>On Dec 17, 9:57 am, "David Cressey" <cresse..._at_verizon.net> wrote:
>>
>>When it comes to keys, it comes down to one question: is the
>>underlying attribute meaningful within the universe of discourse?  If
>>it is (i.e. the clients know what it means, will use it in
>>conversation, want it in reports, specify it in queries, etc.),  it is
>>a natural key and belongs in the database - regardless of whether it
>>is a phonetic name assigned at birth, a social security number
>>assigned upon citizenship, or an id generated and assigned by the
>>database itself. Otherwise, it is garbage (artificial, surrogate,
>>whatever you call it).

>
>
> Fair enough. If this is the accepted meaning of "natural", I can certainly
> use it in that way.
> And I'll agree that the difference between "assigned" like a name, or a
> social security number and "naturally occurring" like biometric data is
> largely moot from the point of view of database theory. Many of the
> discussions that get started in here start from the other point of view,
> but I can adapt.
>
> In addition to the disctinction you've made between "natural" and
> "surrogate", there is a third kind of key, one that doesn't always surface
> in these disucssions. It's a key that generated inside the information
> system but, unlike a surrogate key it isn't generated inside the databse
> layer. It's generated by some process in the application layer. And, in
> general, such keys are not hidden from the users.
>
> An example might be when you reserve a rental car at some airport on a given
> day. You are issued a "reservation number". This key is going to look like
> a "system generated key" to the clerk behind the auto rental desk, and to
> you, but it's going to look like a natural key from the point of view of
> the database layer.
>
> For purposes of database design, the case I've outline can be treated as
> yet another case of a natural key. But for purposes of information system
> design, it has to be treated differently.

A natural key is neither more nor less than a familiar surrogate. It doesn't matter whether the surrogate originates in a person's head, in an application layer, or in the bowels of a dbms. As soon as users interact with it making it familiar, it becomes a natural key. Received on Wed Dec 19 2007 - 09:25:18 CST