skip to content

1:1, 1:N and M:N Implementation

20 roadmaps5 questions

Where the foreign key actually goes for each cardinality — and why many-to-many always needs a junction table. 'How do you model M:N?' is a near-universal screening question for backend roles.

part ofRelational database conceptsoverview, primer and where to startread it →
on this pageshow

explore

questions

5

How do you implement a many-to-many relationship between two tables in a relational database, and why can't a single foreign key column express it?

level: juniorimportance: must knowfreq 82%

answer

  1. M:N → third table, one FK per side
  2. Composite PK = identity + no duplicate pairs
  3. One M:N decomposes into two 1:N
  4. Link attributes (grade, quantity) live on the link row
  5. Second index on reversed column order

basics

~20 s

Use a third table holding one foreign key to each side, keyed on the pair of them. A single foreign key column stores one value per row, so it cannot record many links from the same row.

solid answer

~60 s

A many-to-many relationship is implemented with a **junction table** (also called associative, link, or bridge table). It holds one foreign key column per participating table, and its primary key is normally the composite of those two columns, which also guarantees a pair cannot be recorded twice. A plain foreign key column can hold exactly one value per row, so it can only express "many rows point at one row" — that is 1:N. To let a student have many courses *and* a course have many students, the pairing has to live in its own row. Two practical points I'd add: - Any attribute that describes the *pairing* rather than either side (enrolled_on, grade, role) goes on the junction row. - The composite primary key indexes the pair in one direction only, so I add a second index on the reversed column order to make lookups fast from the other side. Delete behaviour is usually `ON DELETE CASCADE` from both parents: removing a student should remove their enrolments, not orphan them.

code

sql · 11 lines
sql
CREATE TABLE enrollment (
    student_id   BIGINT      NOT NULL REFERENCES student(id) ON DELETE CASCADE,
    course_id    BIGINT      NOT NULL REFERENCES course(id)  ON DELETE CASCADE,
    enrolled_on  DATE        NOT NULL,
    grade        SMALLINT,
    PRIMARY KEY (student_id, course_id)
);

-- serves lookups that start from the course side
CREATE INDEX enrollment_course_student_idx
    ON enrollment (course_id, student_id);

go deeper

for a junior

Be able to name the junction table, put one foreign key to each side in it, and say why a single column can only express one-to-many.

for a middle

Add the key and constraint detail: composite primary key prevents duplicate pairs, both foreign keys NOT NULL, cascade on delete, relationship attributes live on the link row.

for a senior

Talk about access paths — the leading-column rule, the second index on the reversed pair, index-only joins, and when a link deserves a surrogate key.

for a principal

Frame it as choosing a representation: composite-key link table by default; surrogate-keyed associative entity when links are referenced elsewhere or must repeat over time; and be explicit about the write-amplification and constraint cost of each.

## What a many-to-many relationship is A relationship is many-to-many (M:N) when a row on each side can be related to many rows on the other side. Students and courses: one student takes many courses, one course has many students. Authors and books, orders and products, users and roles — all M:N. Contrast with one-to-many (1:N), where only one side is "many": a customer has many orders, but an order belongs to exactly one customer. ## Why a foreign key column cannot do it A foreign key is a column (or set of columns) in one table whose value must match a key value in another table. Because a column holds exactly one value per row, a foreign key can only encode "this row points at that one row". Putting `course_id` on `students` limits a student to one course. Putting `student_id` on `courses` limits a course to one student. Putting both on both sides is worse: it stores the same fact twice with nothing keeping the two copies in agreement. The non-relational escapes people reach for — a comma-separated `course_ids` text column, or repeating columns `course_1, course_2, course_3` — violate first normal form. They cannot be constrained by a foreign key, cannot be joined or indexed sensibly, break as soon as someone needs a fourth course, and force string surgery for the simplest query. ## The junction table The relational answer is to make the relationship itself a table. Each row of the junction table represents one link: ``` enrollment(student_id -> student.id, course_id -> course.id) ``` The M:N relationship is thereby decomposed into two 1:N relationships pointing *into* the junction table: one student has many enrolment rows, one course has many enrolment rows, and each enrolment row references exactly one of each. That is why the mapping rule is often stated as "every M:N becomes a new relation whose key is the union of the two participating keys". ## Keys and constraints The default primary key is the composite `(student_id, course_id)`. It buys three things at once: identity for the row, a guarantee that the same pair cannot be inserted twice, and an index on that column pair. If instead you give the junction table a surrogate `id`, you **must** still add a unique constraint on the pair, or duplicates will appear and every count query becomes wrong. Both foreign keys should be `NOT NULL` — a link to nothing is not a link — and each usually declares `ON DELETE CASCADE`, because a junction row has no meaning once either parent is gone. That is exactly the case where cascade is safe: you are deleting a relationship, not business data. ## Relationship attributes Facts that depend on *both* sides belong on the junction row: the grade a student got in a course, the date a user joined a team, the quantity of a product on an order, the role a person plays on a project. They cannot go on either parent, because a student has one grade *per course*, not one grade. If the junction table carries such columns it is often called an associative entity, and it is a perfectly normal table — it can have its own check constraints, defaults, and audit columns. ## Indexing and access paths The composite primary key index on `(student_id, course_id)` supports lookups that supply `student_id` (the leading column) — "which courses does this student take". It does **not** efficiently support "who is enrolled in this course", because the leading column is not given. So a second index on `(course_id, student_id)` is standard. Both indexes cover the pair entirely, so joins can often be satisfied from the index without touching the table rows. Many databases also require an index on a foreign key column for parent deletes to be efficient, which is another reason the reverse index earns its keep. ## Querying Retrieving the related rows is a two-join query: parent → junction → other parent. Counting per side is a group-by on the junction table alone, which is cheap because it is narrow. This is another advantage over any denormalised representation: the link table is small, dense, and index-friendly. ## Common wrong turns Modelling the pair with nullable foreign keys "just in case"; forgetting the uniqueness constraint when using a surrogate key; storing lists in a text column; or promoting the junction to a full entity with its own `id` before there is any reason to. Start with the composite key and add the surrogate only when something else needs to reference an individual link.

  • When would you give the junction table a surrogate primary key instead of the composite one?
    When an individual link needs to be referenced by other tables — say each enrolment has payment rows or attachments — a single-column key is far more convenient than propagating a two-column key everywhere. Also when the same pair may legitimately recur over time (a student re-taking a course), because the composite key would forbid it. In both cases you keep a unique constraint on the natural pair, or on the pair plus a validity date, so duplicates cannot creep in.
  • Someone proposes storing the related ids as a comma-separated string in one column. What do you tell them?
    It breaks first normal form and gives up everything the database does for you: no foreign key can validate the ids, no index can serve a lookup by a single id, and every query becomes string parsing. Inserting or removing one link means rewriting the whole value, which also makes concurrent updates lose data. A junction table costs one table and gives correctness, indexing and constraints for free.

A junction table is the guest list of a party: neither the people table nor the parties table can hold the pairing, so you keep a separate sheet with one line per (person, party) — and that line is also the natural place to note what dish they brought.

saying these in an interview costs you the question

  • Claiming you can model M:N with a foreign key on each side of the two tables
  • Storing related ids as a comma-separated list or as repeating columns like tag1, tag2, tag3
  • Adding a surrogate id to the junction table and forgetting the unique constraint on the pair, so duplicate links appear
  • Putting relationship attributes such as a grade or quantity on one of the parent tables
  • Assuming the composite primary key alone makes lookups fast from both directions

context

resolving links…

open as a page

In a one-to-many relationship such as customers and orders, which table gets the foreign key column, and how do you model whether the child row is allowed to exist without a parent?

level: juniorimportance: must knowfreq 74%

basics

~10 s

The foreign key goes on the many side: orders holds customer_id referencing customers. If every child must have a parent, declare it NOT NULL; if it may stand alone, allow NULL. Index that column.

open as a page

For a link table holding two foreign keys, how do you choose between a composite primary key on those keys and a separate surrogate id, and what indexes does the table need so lookups are fast from both sides?

level: middleimportance: should knowfreq 48%

basics

~20 s

Composite primary key on both foreign keys is the default: it blocks duplicate pairs and indexes the pair. Add a second index on the reversed order for the other direction. Use a surrogate id only when the link itself is referenced or may repeat.

open as a page

What are the ways to implement a one-to-one relationship between two tables, and when is splitting the attributes across two tables worth it instead of keeping one wider table?

level: middleimportance: should knowfreq 50%

basics

~20 s

Either share the primary key — the child's primary key is also a foreign key to the parent — or put a unique foreign key on one side. Split only for optional, bulky, rarely read, or separately secured attributes.

open as a page

Where do attributes that describe a relationship itself belong — for example a student's grade in a course, or the date a user joined a team — and how does the model change when that relationship has to keep history?

level: seniorimportance: should knowfreq 40%

basics

~20 s

They belong on the junction row, because they depend on both sides and on neither alone. Once the same pair can recur over time, add validity dates to the key or a surrogate key, and the link becomes an entity with its own lifecycle.

open as a page