Re: Surrogate Keys As Part Of Composite Keys?

"Randy Yates" <qusraya_at_rtp.ericsson.com> wrote in message [snip]
> > I think you mean candidate key.
>
> No, I do not. Let me define surrogate key: A surrogate key is a key that
> is defined solely for the purpose of identifying tuples. It carries no
> information whatsoever. This may be done, e.g., for performance reasons
> if the field(s) that contains the "primary data" (i.e., that could act
> as a primary key) is unusually large, as in a 1024-character text field.

... so it's basically a term for a artificial candidate key.

[snip]
> You haven't answered my question. My question is: why *not* do it
> with a surrogate key? I've known for a week and agree that the method
> above is the proper way to model the table, but I'm missing the theory
> and corresponding insight on *why* I need to do it this way.

Why don't you ask your brother, the database expert from Coca-Cola (Atlanta, GA)?

I found the term somewhere on the net defining is as a candidate key inserted
when all the other candidate keys were "unattractive". The example were key = (first name, middlename, surname) replaced by a field "initials". A sane
modeller would never let "initials" serve as a primary key.

According to good old Navathe, a surrogate key is used when mapping _categories_ to realational tables. A category is a subclass for where the superclasses represent different entity types. Let A,B and C be superclasses to D.

Assume

Entity A with attributes a1, a2, a3 (key = a1)
Entity B with attributes b1, b2, b3 (key = b1)
Entity C with attributes c1, c2, c3 (key = c1)

Let D be a subclass of A, B and C. D may be total of partial, that is, it may include the attributes of all superclasses, or only a subset of the superclasses. To map this construction to relational tables, a surrogate key is added to A, B and C which serves as a reference from D to the proper tuples.

1. (PM(a1), a2, a3, surrogate)
2. (PM(b1), b2, b3, surrogate)
3. PM(c1), c2, c3, surrogate)
4. (surrogate, ....)

Regarding your question of adding a "surrogate" to the orderline table, I'd say you normally gain nothing. You would still have to define "the real key" unique and suffer the penalty of having two indexes on the table. The surrogate could, however, maybe serve a legitimate purpose (I don't believe this is always the case), if the table is referenced from another table, as another clever reply in this thread suggests.

--

Thomas