Re: Surrogate Keys As Part Of Composite Keys?

Reply back to Thomas inserted at question below:

In article <37ID4.24828$6b1.450335_at_news1.online.no>, "Thomas Muller" <ttm_at_nextra.com> wrote:
>
> Randy Yates <yates_at_207.87.184.178> wrote in message
> news:38DE9DE1.9F43B4_at_207.87.184.178...
> [snip]
> > Let me address Thomas's question of when you would use a separate
> > primary key to B. First, it is "nice" to do this when using
> > Access since you can make the primary key of B an "autonumber"
> > field, so that when you insert a new record programmatically
> > Access will automatically come up with new key value and therefore
> > the program does not have to.
>
> You will find this kind of feature in any RDBMS. This feature's
 existence
> should not, however, in any way affect the model.
>
> > This would not be the case for a
> > non-autonumber key in B. Second, if you did create a primary
> > surrogate key in B, you can still select either i) a set of
> > records corresponding to a specific order number (since order
> > number will be a foreign key in B), or ii) a specific line
> > order using the primary line order key.
> >
> > So, with the above implementation nicety as a reason *to* have
> > a surrogate primary key, I ask again: what reason is there
> > *to not* have a surrogate primary key but rather instead
> > a composite key consisting of the surrogate (non-autonumber)
> > key in B and the foreign key from table A?
>
In the case of the Order table to line item table relationship you would probably not want to use a sequence (auto generated number) for the line number if you wanted the line numbers to start with one for every order.

As a general rule you would not want to create a surrogate key for any table where it will rarely if every be used to access the table. This is often the case for tables that are directly queried alone.

Indeed I do not like surrogate keys and do not use them where business fields are canidate keys since it is often necessary to carry these columns and index them to inforce business rules. If an item may appear only once on an order then you will have to have an order number item number unique index so wouldn't this be a canidate to be the PK? The best solution always depends on the business rules and application needs. But if you created a PK on the line number and a separate nonunique  index on the order number when would you know the line number without first having come into the data via the order? Combining the two columns into one index will meet the PK and application logic needs. Why have the overhead of two indexes where one will work?

> First of all, "surrogate key" is not a common term within RDB
 terminology. I
> think you mean candidate key. 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)
>
> --
>
> Thomas
>
>

--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.