On Jan 22, 1:26*pm, "Neil" wrote:
Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

I don't claim to know more about db theory than the cdt regulars, or
to have more experience than the many practioners who have contributed
to this thread.

I do know that the debate over relationship representations has been
going on for years, neither side giving any ground. Some aspects of it
are never addressed. For example, making the two foreign keys in a
junction table a composite PRIMARY key prohibits NULL values from
either key, but declaring a separate surrogate primary key together
with a UNIQUE constraint on the two-foreign-key-composite-key does
allow NULL values for either (or both!). This can be useful for
representing unrelated entity tuples, either childless parents or
orphans.

Practioners in the audience may be interested in this page:

http://www.sfdbs.com/toplevel/fasttrack/fasttrack.shtml

of my website in which I describe an entirely new way to represent
relationships. Or this page:

http://www.sfdbs.com/solopages/relcardtypes.shtml

where the representational capabilities of this new representation
(called Aggregate-Link) are compared to the two more traditional
representations.

Please note, I am not suggesting the use of this representation for
conventional database design. Merely consider it new, and possibly
interesting. (It may not be possible to deploy Aggregate-Link in MS
Access.)

The theorists have already had a field day dumping on this new
representation in this thread:

http://groups.google.com/group/comp....514365a600841#

But most theorists seem to be immune to the practical considerations
of IT. In particular, they don't begin to understand that IT
employers' appreciation for Access is based on cost, not beauty.

Rob