Re: Surrogate Keys As Part Of Composite Keys?

From: Thomas Muller <ttm_at_nextra.com>
Date: 2000/03/26
Message-ID: <mPoD4.24171$6b1.437249_at_news1.online.no>#1/1


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
Received on Sun Mar 26 2000 - 00:00:00 CET

Original text of this message