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_at_news.aliant.net>
>
>
> 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.
Date: Wed, 19 Dec 2007 11:25:18 -0400
Message-ID: <476937e0$0$5290$9a566e8b_at_news.aliant.net>
David Cressey wrote:
> "Kevin Kirkpatrick" <kvnkrkptrck_at_gmail.com> wrote in message
>
>>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 - 16:25:18 CET