Re: Surrogate Keys As Part Of Composite Keys?

Thomas Muller wrote:
> [...]
> First of all, "surrogate key" is not a common term within RDB terminology.

Perhaps not, but Date uses the term in his book, and my brother, who is a database expert at Coca-Cola (Atlanta, GA) was familiar with the term as well.

> 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.

> A table may have several candidate keys (a key
> being a single/composite field(s) uniquely identifying a row), and you
> choose one of them as the primary key (PM).
>
> I don't fully understand the question, but regarding the order <-> orderline
> relation, there's no need for a single field serving as candidate key,
> simply because this field wouldn't carry any useful information. The proper
> way to model the order/order_line relation is like this:
>
> order(order_id, date, ....)
> order_line(order_id, line#, article#, ...) // PM = (order_id, line#), line#
> ranges from 1...infinite for each order_id)

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.

-- 
Randy Yates
DSP Engineer
Ericsson / Research Triangle Park, NC, USA
qusraya_at_rtp.ericsson.se, 919-472-1124