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

"JOG" <jog_at_cs.nott.ac.uk> wrote in message news:a5cca123-f9a1-496a-a6a4-ba43b1f1eccd_at_i12g2000prf.googlegroups.com...
> On Dec 19, 7:12 pm, "David Cressey" <cresse..._at_verizon.net> wrote:
> > The worst confusion that occurs here in c.d.t. regarding surrogate key
is
> > 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
extent
> > of plonking a few more people, but it does affect a lot of the
discussion
> > 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