472,145 Members | 1,507 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

What to use for primary key of a many-to-many table?

Hi.

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 ?

Thanks....
Please email me too if possible.

Fred
Nov 12 '05 #1
4 8973
I usually use a regular good old regular autonumber?

There is no need for anything else...is there?
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn
=
Nov 12 '05 #2
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
Nov 12 '05 #3
For me, it depends.

If the junction table is further involved as the one in a one to many, then
I'll add an autonumber as the primary key (but also put a unique index on
the combination of the 2 foreign keys).

If it's not involved, then I'll just set the 2 foreign keys as the primary
key.

--
Joan Wild
"Fred" <fg***************@yahoo.com> wrote in message
news:bj************@ID-76446.news.uni-berlin.de...
Hi.

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 ?

Thanks....
Please email me too if possible.

Fred

Nov 12 '05 #4
RE/
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 ?


Not having any extra personal processing power to spare, when I create a table I
create a (usually autonumbered) numeric field as the PK. Period. Just a plain
old dumb number.

Hasn't failed me yet and the few times I haven't done it I've wished I did.

OTOH when I look at some of the multi-field joins some people have to do to get
the same results I get really confused really fast.
-----------------------
PeteCresswell
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by t53864 | last post: by
29 posts views Thread by asj | last post: by
2 posts views Thread by arijitchatterjee123 | last post: by
11 posts views Thread by Geoff Jones | last post: by
6 posts views Thread by Emma Middlebrook | last post: by
9 posts views Thread by Steven C. | last post: by
115 posts views Thread by LurfysMa | last post: by
3 posts views Thread by sandy | last post: by
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.