Simplifying a “many-to-many” relationship
by
combining 2 “one-to-many”
relationships
using a “Junction-table”
(See
example ‘Datasheet’ View)
In this example, a student may enroll in many courses,
and a course many have many students enrolled in it.
This may be viewed as a “many-to-many” relationship,
with the primary key from each table having a foreign key in the other table.
This could become difficult to maintain,
so it may be better to include another table “between” them,
and use two separate “one-to-many” relationships.
This intermediate “junction” table can
control the relationship
and be used to relate students to their courses.
The Students and Courses tables are indirectly
related to each other :
The Students table is directly related to its foreign key in
the junction table,
and
the courses table is directly related to its foreign key in the
junction table.
The “junction” table
contains the “many” side of two “one-to-many” relationships.
The “Junction Table” is the table between the two data
tables.
The “Junction
Table” can also contain its own data (i.e.: that student’s grade for that
class.)
Note also that the primary and foreign keys in a
relationship
do not necessarily have identical field names.
The student table’s primary key is connected to the Student ID foreign
key
in the “Student Registration” table,
and the course table’s primary key is connected to the Course ID foreign
key
which is also in the “Student Registration” table.
It can also contain additional attributes,
such as the registration date.
Now the 2 foreign keys in the
“Student Registration” table
can be combined to form a composite primary key
which must contain a unique value of both
the Student ID # and the Course ID #.
This ensures that
the same student cannot enroll
in the same course more than once.
If a student tried to enroll in a course again,
it would cause a duplicate primary key
that would contain the student’s ID # plus the course ID#.
Remember that a primary key must contain a value,
and that entire value must be unique.
In this case, each ID # is only part of the
primary key.
A student may enroll
in several courses
and each course can have many students.
However
…
there will never be 2 primary keys that contain
the same student ID # AND the same course ID #.
Looking at the “datasheet” view of the same example :
Study the foreign keys in
the junction table to see how they connect the data.
Running this query :
with this data :
. . . results in the following “view” of the combined data :
Notice that the data from the two tables containing actual data is combined
into one recordset,
and the data from the ‘junction’ table remains ‘behind the scenes.’
