Why are Junction Tables necessary?

[Guest]

I have been reading about databases for just a few hours, so think of me as a
complete muppet for the purpose of explanation...

I don't understand why many-to-many relationships are not possible, and why
we need two tables, each with a one-to-many relationship with a third table.

Thanks for your help.

 

[Rick Brandt]

I have been reading about databases for just a few hours, so think of
me as a complete muppet for the purpose of explanation...

I don't understand why many-to-many relationships are not possible,
and why we need two tables, each with a one-to-many relationship with
a third table.

Thanks for your help.

Table1 PrimaryKey [ID_1]

Table2 PrimaryKey [ID_2]

If I need to store data that have the relationships below...

ID_1 ID_2
1 1
1 2
2 4
3 2

I cannot store this in Table1 because I cannot have duplicates in ID_1 and I
cannot store it in Table2 because I cannot have duplicates in ID_2. I must use
a third table that contains fields for both ID_1 and ID_2.

A good example is Students and Classes. Each student can attend many classes
and each class has many students. The Students table has exactly one record for
each student and the Classes table has exactly one record for each class. A
third table "Enrollment" is required to store the data for which students are
enrolled in which classes. Each record in that table would contain the primary
key value for a student and the primary key value for a class.

 

[Jeff Boyce]

I'll try by way of an example...

You have students. If you want to add another student, you just add him/her
to the tblStudent.

You have classes (i.e., a classroom, a topic, an instructor). If you want
to add another class, just add it to the tblClass.

Now, how do you show which student is enrolled in which class?

You can't really add that as a field in the tblStudent unless you are only
allowing your students to enroll in a single class, and are not interested
in any previous class they took. Ditto for why you couldn't add a student
to the tblClass. But you described a "many-to-many" relationship, so
neither of these is appropriate.

Now create a third table, trelEnrollment. Put one StudentID and one ClassID
in a new row, plus any other "facts" about the enrollment, like
DateEnrolled.

Does it make any more sense with an example?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Let me elaborate on Jeff's response, using the student/course example.
A database is meant to store FACTS. Say two of the facts are: John Doe takes
Math101, Jane Smith takes Chem102. Where do store these? Let's assume for the
sake of argument that you put them in the student table, like so:

John Doe (address, dob, etc.) Math101
Jane Smith (address, dob, etc.) Chem102

But now John Doe enrolls in Chem102 - where do we store that fact? Let's add
a field for the second course, like this:

John Doe (address, dob, etc.) Math101 Chem102
Jane Smith (address, dob, etc.) Chem102 NULL

How far do we take this? How many course fields do we create in the Student
table? OK, so maybe we can set a reasonable limit on number of courses and
create that many fields.

Now the teacher for Chem102 wants the list of students in her class. How do
we create it? Where in the student table do we find that information? It is
in a different place for each student, making the query difficult to write
and slow to execute. And your query would have to be rewritten if the course
limit per student changes.

A naive user might attempt to store each fact TWICE - once in the Student
table to create a list courses for each student, once in the Course table to
create a list of students in a course. Bad idea. First of all, duplicating
facts may lead to inconsistencies between the duplicates. What do you do if
Chem102 exists in John Doe's record, but John Doe is absent from the Chem102
record?
Second, you need to set a limit of students per course, so you know how many
student fields to create for each - typically this varies from course to
course. If you create fields for the largest course, how do you limit entries
for courses that allow fewer students? With this structure, merely writing a
query to count students for a course is non-trivial.

That's where the junction table comes in - it uniquely stores single facts
about TWO entities in your database, in a structure through which it is very
easy and efficient to search, report, count, calculate etc.