Junction Tables

When two entities can relate to many rows on both sides, the clean relational move is to create a third table. That table is a junction table: one row per pairing, with foreign keys to both sides.

You will also hear it called a bridge table, join table, or associative table. The names differ, but the design job is the same.

Turning many-to-many into two one-to-many links

A many-to-many relationship is hard to store directly because the relationship itself has many instances. A junction table gives each instance its own row.

In ER form, the resolved design looks like this:

The junction table is the child of both parent tables. Its two foreign keys point outward to the rows being connected.

The pair is the identity

Often the natural primary key of a junction table is the pair of foreign keys. In enrollments, the pair (student_id, course_id) says "this exact student in this exact course."

That composite primary key prevents duplicate pairings. Ada can be enrolled in Math once, not two separate times by accident.

Quick check

QuestionSelect one

What does a composite primary key on (student_id, course_id) prevent in an enrollments table?

One student from taking more than one course.

One course from having more than one student.

The exact same student-course pairing from being inserted twice.

Students and courses from being joined in a query.

Facts about the relationship itself

A junction table can hold more than just foreign keys. Some facts do not belong to either parent alone; they belong to the relationship.

For an enrollment:

• enrolled_on describes when this student joined this course.
• grade describes this student's result in this course.
• role might distinguish learner, assistant, or auditor.

A grade cannot live in students, because Ada has different grades in different courses. It cannot live in courses, because Math has different grades for different students. It belongs on the row that represents Ada-in-Math.

Building a junction table

Run this example and then try changing the final insert to repeat an existing pair. The composite primary key protects the table.

The result reads naturally because the design matches the real-world sentence: "student is enrolled in course, with these details about the enrollment."

When to give the junction its own id

Sometimes you will see a junction table with a surrogate id as its primary key. That can be reasonable if other tables need to point at a specific enrollment row.

Even then, you usually still add UNIQUE (student_id, course_id) so the same pairing cannot be duplicated.

The design principle is the same either way: one row represents one pairing, and the database should prevent accidental duplicate pairings.

Check your understanding

QuestionSelect one

Which statement best describes a junction table?

A table that stores a copy of every column from two parent tables.

A table that stores one row per relationship instance, usually with foreign keys to both related tables.

A table that can never have its own columns beyond foreign keys.

A table used only for speeding up reports.

QuestionSelect one

Where should an enrolled_on date belong in a students-courses design?

In students, because each student has one enrollment date for all courses.

In courses, because each course has one enrollment date for all students.

In enrollments, because the date describes a specific student-course pairing.

In no table, because dates cannot be constrained.

QuestionSelect one

If a junction table uses a surrogate id primary key, what constraint often still protects the design?

CHECK (id > 1000)

UNIQUE (student_id, course_id)

UNIQUE (grade)

CHECK (student_id = course_id)