A model says a student may enrol in many courses and a course may hold many students. How is that many-to-many association represented in relational tables, what is the extra table's key, and why can it not be done with a column on either side?
answer
- third table, one row per pair
- PK = the pair of FKs by default
- a column holds one value
- pair attributes live here
- repeats legitimately? key needs more
basics
~20 sIt becomes a third table holding one row per pair — student key and course key — with a foreign key to each parent and a primary key over the pair. Neither parent can hold the link because each side has many partners, and a column stores only one value.
solid answer
~50 sA many-to-many relationship maps to its own table — a junction, associative or link table — with one row per associated pair. It holds the primary key of each parent as a foreign key. Its primary key is normally the pair of those columns together, which does two useful things: it identifies the row and it enforces that a given student is enrolled in a given course at most once. If duplicates are legitimate — re-taking a course — then the pair is not the key and something else, such as the term, joins it. It cannot be done with a column on either parent, because a column holds one value and each side has many partners. Repeating slots such as course1, course2, course3 cap the number arbitrarily and make querying miserable, and a delimited list defeats typing, foreign keys and uniqueness. Any attribute that belongs to the pair — enrolment date, grade — goes in this table, because it is the only place where the pair exists.
code
sql · 7 linesCREATE TABLE enrolment (
student_id BIGINT NOT NULL REFERENCES student(student_id),
course_id BIGINT NOT NULL REFERENCES course(course_id),
enrolled_on DATE NOT NULL,
grade VARCHAR(2),
PRIMARY KEY (student_id, course_id)
);go deeper
State the rule and the reason: a third table with both foreign keys, keyed by the pair, because one column cannot hold many partners.
Add where relationship attributes go and when the pair is not a valid key because the association can legitimately repeat.
Discuss surrogate-versus-composite key on the junction and the duplicate-row failure mode when the natural uniqueness is dropped.
Talk about when the association should be promoted to a first-class entity with its own lifecycle and identity, and what that means for consumers referencing it.
## The rule A many-to-many relationship between A and B maps to a third table containing A's primary key and B's primary key, each declared as a foreign key to its parent. One row means "this A is associated with this B". The table is variously called a junction, join, link, associative or bridge table. ## Why neither parent can hold it A column in a row holds one value. In a one-to-many relationship the many side has at most one partner, so one column suffices — that is exactly why a foreign key works there. In a many-to-many, both sides have many partners, so no single column on either side can record the association. The two workarounds people reach for both fail: - **Repeating columns** (course1, course2, course3) impose an arbitrary cap, scatter one logical fact across several columns, and make "who is in course X" a search over every slot. - **A delimited string** of ids gives up column typing, referential integrity, per-value uniqueness and any efficient lookup, and turns updates into string surgery. The junction table has none of those problems: it grows by rows, each row is fully typed and constrained, and lookups work from either direction. ## Choosing the key The default primary key is the pair of foreign keys. That choice is not cosmetic — it encodes the business rule that the same association is recorded at most once. Before accepting it, ask whether the pair can legitimately repeat. A student re-taking a course, a person holding a role twice over different periods, a product bought on two separate orders — whenever repetition is real, the pair alone is wrong and the key must include the discriminating attribute, most often a term, a period start or a sequence. Whether to add a surrogate key alongside is a separate decision. It helps when the junction row must be referenced from elsewhere — an enrolment with its own payments or attendance records — and it costs an extra column plus the need to keep the natural uniqueness enforced separately, which is easy to forget and the usual source of duplicate rows. ## Where relationship attributes go Any fact about the pair belongs in this table: the enrolment date, the grade, the quantity on an order line, the role someone holds in a project. This is the only place in the schema where the pair exists, so it is the only place such a fact can live without being duplicated or attached to the wrong owner. A grade on Student would force one grade across all courses; a grade on Course would force one grade across all students. Both are obviously wrong once stated, and both are common in real schemas. ## When the junction becomes an entity If the association acquires enough of its own life — a status, an approver, a history, references from other tables — the model is telling you the relationship was really an entity all along. Enrolment, Booking and Assignment are the usual names. Nothing structural changes overnight; you keep the same two foreign keys and add what the entity needs, but recognising the shift stops you resisting the attributes it wants to carry. ## Reading it back Queries traverse the junction with two joins, one to each parent, and the direction is symmetric: courses for a student, or students for a course, are the same table read the other way round. That symmetry is the practical benefit of the design and the reason it is worth insisting on even for small lists.
- When is the pair of foreign keys the wrong primary key for a junction table?When the same pair may legitimately be associated more than once — a student re-taking a course in a later term, or a contractor rehired for a second engagement. Then the key must include the discriminating attribute, such as the term or period start. Keeping the pair as the key in those cases silently blocks valid data.
- Would you add a surrogate key to a junction table?Only when the association needs to be referenced from elsewhere — an enrolment with its own payments or attendance rows — or when a tool insists on a single-column key. If you add one, keep a unique constraint on the natural combination, because otherwise the surrogate happily admits duplicate pairs, which is the most common defect in that design.
saying these in an interview costs you the question
- Suggesting repeated columns or a comma-separated list of ids on one of the parents
- Adding a surrogate primary key and dropping the unique constraint on the pair, allowing duplicates
- Putting a per-pair attribute such as a grade on one of the parent tables
- Assuming the pair must always be unique, without checking whether repetition is legitimate
- Calling the junction table redundant and proposing to store the association twice, once on each side