How to implement many-to-many? Do I need primary key defined?

[Siegfried Heintze]

If I have table student and table classes

(1) What is your favorite naming convention to creating the many-to-many
table that holds the foreign keys for students and the foreign keys for
classes?
(2) Should the primary key in my many-to-many relation contain the foreign
key of (a) the student or (b) the class or (c) neither?
(3) I tried to make the foreign key for student the primary key but since a
student might have multiple clases, that primary key might have duplicates
and that caused problems. Do I need to have a primary key? Cannot I just
have two fields that are indexed? MSAccess complains when I dont' have a
primary key.

Also: does any one have an example of using a language like VB.NET to
programmatically create an MSAccess database? Which would be easier, ADOX or
DAO?

Thanks,
Siegfried

 

[Roger Carlson]

1) If the table is simply a construct to resolve the M:M, I'd call it
lnkStudentClass. However, some linking table are entities in and of
themselves. In that case, I create a real-world name for it.

2) Usually, I have the Primary Key a compound one created by both foreign
keys. The exception is when this linking table has a relationship with
another table. Then I will give it an Autonumber Primary Key and create a
Unique Index on both the foreign keys. But I know developers who do the
later all the time on the principle that it will work in all circumstances.
I don't have a problem with this.

3) You can't make the PK of one table the PK of the linking table because
there is a One-to-Many relationship between them. Do one of the two things
in #2 above. Yes, every table should have a PK. It's not required in
Access (it should be) but it's good design practice.

Also: Personally I think DAO is easier, faster, and more flexible than ADO.
It was created for Access and MS thinks so too. In Access 2003, DAO is now
a default Reference.

--
--Roger Carlson
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

 

[Tim Ferguson]

(1) What is your favorite naming convention to creating the
many-to-many table that holds the foreign keys for students and the
foreign keys for classes?

I have a horror of one-size-fits-all naming mechanisms. Remember that
"junction tables" are just tables and are modelling some real life
entity, and that is what should dominate the naming. For example, if the
table is modelling what students turned up for which classes, then I
would call it TurnedUpFor. If it's about registrations, I would call it
Registrations. Alternatives would be BannedFrom, CaughtSmokingIn,
QualifiedIn and so on.

For me, names like StudentClasses are anathema because they don't tell
anyone what they mean. And what happens if you need two relationships
(RegisteredFor, AutomaticPassIn) -- how helpful is StudentClasses and
ClassesStudents?

(2) Should the primary key in my many-to-many relation contain the
foreign key of (a) the student or (b) the class or (c) neither?

I am a purist: I would make the PK out of the two FKs and nothing else.

(3) I tried to make the foreign key for student the primary key but
since a student might have multiple clases, that primary key might
have duplicates and that caused problems.

That's why it's a many-to-many relationship...

Do I need to have a primary key?

Rule one: if there is no PK then it's not a R table.

Cannot I just have two fields that are indexed? MSAccess
complains when I dont' have a primary key.

In the UI, you can ctrl-click the two fields and then click the primary
key toolbar button: this creates a compound key using both fields.

Also: does any one have an example of using a language like VB.NET to
programmatically create an MSAccess database? Which would be easier,
ADOX or DAO?

SQL.

CREATE TABLE Registrations (
StudentID INTEGER NOT NULL REFERENCES Students,
ClassCode NCHAR(5) NOT NULL REFERENCES Classes,

DatePaid SMALLDATETIME NULL // etc etc

CONSTRAINT PK_Registrations PRIMARY KEY (StudentID, ClassCode)
)


You can execute this using anything like ADO, DAO, ADO.NET that you have
handy.

Hope that helps


Tim F

 

[Siegfried Heintze]

(2) Should the primary key in my many-to-many relation contain the

I am a purist: I would make the PK out of the two FKs and nothing else.

Why (or when) would this be advantagous? To benefit from this I would always
have to know both foriegn keys. The whole reason for having the table is
that I only know one or the other.
I need to create a database, not just a database table.

 

[Tim Ferguson]

Why (or when) would this be advantagous? To benefit from this I would
always have to know both foriegn keys. The whole reason for having the
table is that I only know one or the other.

I think we must be at cross-purposes here. I was talking about a "junction
table" recording, for example, registrations of students to classes. You
may well have times when you have a registration form a particular student
who has forgotten to fill in the box for which class he wants to join: but
I'd suggest that this would not be a particularly useful thing to record in
the database.

The Only Purpose of this table is to create links between students and
classes: it doesn't make sense to do that when you only know one or the
other.

I need to create a database, not just a database table.

Oh, okay.

Hope that helps


Tim F

 

[Siegfried Heintze]

I think we must be at cross-purposes here. I was talking about a "junction
table" recording, for example, registrations of students to classes.

Tim (and anyone else):
I really want to understand this. Please correct any false assumptions
because
I don't believe we are at cross purposes here.

(1) I assume a junction table is the name of table used any time you have a
M:M
relationship and that this is very common.
(2) I assume that performing joins is very common. When Access performs a
join,
(for example) it enumates each student from which it gets the studentID. (I
like to
be consistent and always store this in a auto increment integer field called
"id"
-- do you do the same?)
Then, it uses this value and finds all the rows in the junction table whose
fkStudent
columns matches this ID.

Now can someone explain to me how having a composit primary key consisting
of both
fkStudent and fkClasses helps Access quickly find all the matching rows for
our join
when we only know the fkStudent value?

Can someone explain why having a primary key at all is advantageous here?
(Assuming
primary keys do not allow duplicates?)
It seems I must be missing something here! It seems to me that we want to
allow
duplicates in both the fkStudent and fkClasses columns (assuming this is a
pure link table and
we are not storing any data like a grade that describes the relationship
between the class and
the student) and we want both columns to be indexed so we can not only
quicly find all the
classes a student has, but also quicly find all the students a class has.


Now there was a comment earlier that one should always have an autoincrement
integer
primary key just in case you need to, for example, uniquely identify a row
in the link
(or junction table -- same thing?) because you have stored some additional
data there like a building and room number, or a grade.

What is the ramification of having multiple indices in a single relation?
Let us say
you have three fields: id (primary key), fkStudent (integer, indexed) and
fkClass (integer, indexed).

Everytime I insert into this, I have to update three index structures!
Should this be a disk space concern?
Should this be a data structure corruption concern? Should this be execution
time concern?



Thanks,
Siegfried

 

[Roger Carlson]

Having a compound primary key of both foreign keys will only allow one
unique combination for fkStudent and fkClasses on the assumption that one
student can be in any particular class only one time. (By class here, I
mean a particular instance of a Course.) Because it is a compound primary
key, it allows duplicates of a StudentID and of a ClassID, but no duplicates
of a combination of the two.

When I mentioned earlier that I will add an autonumber primary key
sometimes, that is in the case where the linking table has some relationship
to another table. For instance, if you wanted to track assignments handed
in, the Assignments table would have a relationship to the linking table
(lnkStudentClass). Therefore the relationships would look like this:

tblStudent lnkStudentClass tblClass
======== =========== ======
StudentID ----< fkStudent |----ClassID
others fkClass >------|
SCID
| tblAssignments
| ===========
| AssignmentsID
|-------< fkSC

In this case, I would STILL create a Unique Index on the combination of
fkStudent and fkClass.

The optimizer for the Jet engine uses indexes (primary keys ARE indexes) to
optimize queries, so they should be used. Any field which you use to Join
tables, sort on, or used frequently as a criteria in a query should have an
index. Also, in order to create Relationships (with Referential Integrity
ON) you MUST have a primary key on the One side of a One-to-Many
relationship.

--
--Roger Carlson
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

 

[Siegfried Heintze]

Having a compound primary key of both foreign keys will only allow one
unique combination for fkStudent and fkClasses on the assumption that one
student can be in any particular class only one time.

Ah hah! Finally I understand. Thanks Roger.

Now what about performance?

In scenerio "A" we have no primary key. We have indices fkStudent and
fkClass and are at risk of having duplicate Student-class assignments. We
perform a join to find out all the classes a student has. Jet gets the first
Student, gets his ID and finds all the rows in the link table that have that
value for fkStudent. This should be fast because we are indexed on
fkStudent. No composit keys.

In scenerio "B" we take your advice: We have composit primary key consisting
of at least fkStudent and fkClass. How fast is the lookup going to be when
doing a join and (I assume) Jet is only going to know the value for
fkStudent? Is this going to be a linear search because we don't the other
values (segments) of the primary key?

Thanks for sticking with me on this!

Siegfried