The assertion in the article (that it is *always* incorrect to have a
multiple field primary key for a junction table) is rubbish. If it
is a *pure* junction table (that is, the table's ONLY use is to
implement a Many-to-Many relationship), then the *preferred* way is
to make the primary key a combination of the two foreign keys
(especially if these foreign keys relate to autonumber primary keys
in the base tables). By adding a surrogate key (autonumber primary
key), you are simply maintaining an additional index for no reason.
That said, if the junction table participates in any *other*
relationships, it is useful to create a surrogate key and create a
unique index made up of the two foreign keys. Even so, it's not
mandatory (see Problem 3 below).
Let me discuss the author's three problems:
Problem 1a:
If you want to delete a record from your junction table, you'd have
to supply values for both fields, rather than a single value. So you
would have to do this:
Currentdb.Execute "DELETE FROM tblSudentClasses WHERE StudentID = " &
me.cboStudent & " AND ClassID = " & me.lstClasses.
instead of this:
Currentdb.Execute "DELETE FROM tblStudentClasses WHERE StudentClassID
= " & me.lstClasses
Perfectly true. But his assertion that it is faster and more
effiecient is just not so. It is, of course, a tad more difficult to
code, but in terms of execution, the the difference would be
negligible. In fact, since Access stores primary keys as a clustered
index, it may even be faster! You'd have to do a benchmark to test
it, but to a human difference will be indistinguishable.
Problem 1b:
"if you ever decide to allow a student to take a class multiple times
you have to change this code to include your new field of the PK
(like class date)"
This is true, but if it is ever possible that a student can take a
class multiple times, then you should have taken that into account in
your initial database design. And even if you didn't, and you had to
make this change, you'd still have to modify your table structure
because you'd want to store this date and the junction table would be
the logical place. And you would *still* want to maintain real-world
uniqueness in your junction table, which would require a unique index
on the three fields anyway.
Problem2:
"What if you wanted to select a record with a multi-field PK from a
combo box?
John Calculus
John Chemistry
Jane French
Jane Biology"
This case is very true, but does not apply to the Junction table.
Remember, we are talking about foreign keys that are related to
surrogate keys in the main tables. You would never have values as
above. This argument is pertinent to the notion of using multiple
field primary keys in general, but not the specific case of a
junction table.
Problem3:
"Now you have tblTests with TestID and want to create
tblStudentClassTests to show what each student got on each test for
each class."
This is the case above where the junction table is not a pure
junction table, ie one that participates in another relationship. In
this case, it IS preferred to create a surrogate key for the primary
key, but it is not MANDATORY. His contention is that you'd HAVE to
store values from both fields in the composite key in the related
table is just plain wrong.
Let's take the case above. You've created your database with
tblStudentClass as a junction table. Later, you are asked to modify
the database to store test scores. You create tblTest and realize
this is also a M:M relationship that requires its own junction table
(tblStudentClassTests). Rather than relating the table on the
primary key of tblStudentClass, you'd create an autonumber field and
add a Unique Index on it (call it StudentClassID). The you could
relate your table on this new field, rather than the primary key.
While we usually create relationships on primary key/foreign key, it
doesn't have to be so. Any field with a unique index will work.
(Note: an autonumber field with a unique index is also guaranteed to
be non-null).
Now I'm not saying I'd design it like that from scratch. If I *knew*
a junction table would participate in other relationships, I *would*
create a surrogate key. However, if I found myself in the situation
where I had to add a new relationship after the fact, I would NOT
have to resort to relating on multiple fields.
All of this is not to say I'm a fan of Natural Keys. I'm not. I use
Surrogate Keys in almost every table EXCEPT in pure junction tables.
--
--Roger Carlson
MS Access MVP
Access Database Samples:
www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L