fg***************@yahoo.com (Fred) wrote in
<bj************@ID-76446.news.uni-berlin.de>:
What is the usual and what are the possible fields to use for the
primary key of an intersecting table of a many-to-many
relationship?
I would think the typical, most common fields would be to set the
intersecting table to have its own unique primary key. Right?
OR
It was suggested using the 2 foreign keys from the two other
tables. Comments?
OR
How about including a date/time field in the primary key for
history tracking ?
Well, it all depends.
To me, the most common PK for a many-to-many join table would be
your option 2: a unique index on the two foreign keys.
I would only use an Autonumber surrogate key (your option 1) when I
needed to store the join table's PK as foreign key in another
table. This happens suprisingly often.
Your option 3 has nothing to do with determining the primary key,
and really is only about what other attributes the join between the
two tables happens to have in your data schema. If you were
thinking that a date/time field along with the two foreign keys
might give you a PK, I'd say you probably have a schema design
error. The real structure you should use is to have the join with
no attributes of its own, and then have the multiple date/time
records in a table attached to the join table.
I've had the kind of structure in many apps, where a join table
establishes a relationship between two entities and then a table
related to that join records the history of that relationship over
time, with multiple attributes.
In that case, I'd give the join table it's own AutoNumber surrogate
primary key and store that AutoNumber in the related history table.
I would, however, retain the unique index on the two foreign keys
in the join table -- even though they no longer function as PK in
the schema, they should still be unique.
--
David W. Fenton
http://www.bway.net/~dfenton
dfenton at bway dot net
http://www.bway.net/~dfassoc 
