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

From: David Cressey <>
Date: Thu, 20 Dec 2007 07:10:22 GMT
Message-ID: <yzoaj.5827$Xh1.2443_at_trndny03>

"JOG" <> wrote in message
> On Dec 19, 7:12 pm, "David Cressey" <> wrote:
> > The worst confusion that occurs here in c.d.t. regarding surrogate key
> > whether a surrogate key refers to the person, place, or thing, that the
> > contents of some tuple describes, or whether it refers to a "row of a
> > table".
> I think part of the confusion comes from the use of keys in both E/R
> and RM, (when there really should be different terms, perpetuating the
> (understandable) difficulty people have with distinguishing conceptual
> and logical layers.
> > In the second case, the surrogate key is indeed being used as a
> > substitute for a pointer, and that's the start of a slippery slope. I
> > don't think you, Bob, are affected by that confusion, except to the
> > of plonking a few more people, but it does affect a lot of the
> > in c.d.t. nonetheless.
> Let's see what people make of the following definitions. I'm going to
> distinguish the use of the term 'key' as used in different layers as:
> 1) A 'Key' is a set of attributes, used at the logical layer to
> uniquely identify a proposition.
> 2) An 'Identifier' is a set of attributes, used at the conceptual
> layer to uniquely identify an entity.

I like this!

> * A 'Surrogate Identifier' is an attribute designated at design time
> to stand in place of natural identifiers that are either: (i)
> Impractical to observe, (ii) Impractical to manipulate.

I would add (iii) Untrustworthy

Untrustworthy might be a consequence of identifier misuse beyond the control of the database.
Misuse can be due to incompetence, informality, or fraud.

> * Once a 'Surrogate Identifier' has been created it merely becomes an
> unfamiliar 'Natural Identifier' (it too must become an observable
> attribute of the entity it applies to).
> * A 'Surrogate key' is merely a key that contains an attribute that
> was a surrogate identifier at the conceptual layer.
> Any comments?

Excellent! Received on Thu Dec 20 2007 - 08:10:22 CET

Original text of this message