many-to-many relationship

[TSG]

I researched to figure how and when to create a m:m relationship. I'
building a database that will have classes and students in it. I hav
a table named class and one named stupersonal. One student may hav
several classes and one class may have many students, so I made a thir
table named StudentClass. All the table has as of now are two PKID
which are the same as the ones from each of the other tables. I have
one-to-many relationship from Student ID to Class ID in StudentClass an
I have a one-to-many relationship from Class ID to Student ID i
StudentClass. First of all, I want to know if I've set it up right?
Do I need to add all the fields from my two tables to the junctio
table? When designing forms and writing reports; will have to use th
junction table as the source? I'm trying to understand in plain terms
how the junction table acts in this relationship. I am fairly new t
this and seem to be "stuck" with this obstacle. Any help is greatl
appreciated in advance

 

[Nikos Yannacopoulos]

You have done the right thing! That's exactly how you should tackle
this. No, you don't need any other field from the classes and student
tables in the StudentClass one(you could call it Enrollments, if it
makes more sense). You could have other fields there, though, relating
to a each particular enrollment, such as year, semester, major/minor,
grade etc.

HTH,
Nikos

 

[Jeff Boyce]

In addition to Nikos' comments, I'll add that you absolutely do NOT want to
duplicate data in your "junction"/"relation"/"resolver" table.

When it comes time to display (query, form, report) the details underlying
"Enrollment", use a query that joins all three (student, class, enrollment)
tables together. This way, you only need to record a fact one time, not
over and over again.