Re: Newbie question about db normalization theory: redundant keys OK?
Date: Wed, 19 Dec 2007 11:34:13 GMT
Message-ID: <Vk7aj.11409$DO.5530_at_trndny08>
"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.
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
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.