Re: Surrogate Keys As Part Of Composite Keys?

In article <mPoD4.24171$6b1.437249_at_news1.online.no>, "Thomas Muller" <ttm_at_nextra.com> wrote:
>
> Randy Yates <yates_at_207.87.184.178> wrote in message
> news:38DDF993.3D111DEC_at_207.87.184.178...
> > If I have two tables, A and B, with a one-to-many relationship
> > between A and B, so that table B has as part of its data a foreign
> > key from A, and I use a surrogate key for the records in B, then
> > that surrogate key is unique (e.g., the autonumber type field in
> > Access). That means that it can serve as a primary key alone. On
> > the other hand, if it weren't for the fact that a surrogate key
> > existed in table B, the primary key for table B would have to be
> > composite. So my question is, what is the proper way to model table
> > B, 1) use a surrogate key that is unique across the table, in which
 case
> > it is the primary key, or 2) use a surrogate key that is unique only
> > across the group of records associated with a specific foreign
> > key, in which case the primary key must be the composite key
 consisting
> > of the surrogate key and the foreign key?
>
> If A where Department and B where Employee, and the business rules
 stated
> that no employee could work in more that one department, you would
 model it
> according to suggestion 1), that is, have a unique emp_id serving as
 the
> primary key for Employer, and the dep_id from A would be the foreign
 key
> referencing the proper department. If dep_id where defined as part of
 the
> primary key for Employee, you're business rule would change to "an
 employee
> may work in several departments", but this would be pore design
 unless you
> separated the combination of the keys in another table, due to
 redundancy
> issues.
>
> The general rule is that for 1-n relationships from A to B, B has a
 primary
> key excluding the reference to A. In a n-m relationship, a new table
> including only the composite key as added "between" the original
 tables.
>
> --
>
> Thomas
>

I am not sure what Thomas is trying to say, but if you have a parent to child relationship from A to B, let us say like, order header to order line items, it is normal to carry the Key of A, the order number, to B so that order number and line number would be the unqiue key of B. The is no need to create a seperate primary key to B because when will you ever use it? You will be accessing B based either on all lines for the order number in A or by a specific order number, line item number combination.

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