Many-to-Many Relationships (N:M) and Join Tables

A student attends many classes; a class contains many students. A post carries many tags; a tag labels many posts. Neither side can hold a single foreign key — where would the second student's row go? Databases solve this with a third table, the join table, and the ER diagram draws it as a box in the middle with two 1:N edges.

The pattern is worth internalizing because it appears everywhere: enrollments, memberships, likes, follow graphs, product-option matrices, role assignments. Whenever you catch yourself writing 'a list of X on the Y table', you are looking at a many-to-many that has not been given a table yet.

The canonical pattern

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  label VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Three details carry most of the weight. The two foreign keys make every row of post_tags point at a real post and a real tag. The composite primary key (post_id, tag_id) makes each pair unique, so the same tag cannot be attached to the same post twice. And the NOT NULL on both columns means a join row always describes a complete relationship — there is no such thing as 'half a link'.

A worked example: enrollments

The posts-and-tags case is symmetric: both sides treat the relationship the same way. Real domains are rarely symmetric. A student enrolls in a class at a point in time, receives a grade, and may drop out. Those facts belong to the pair, not to the student and not to the class — so they become columns on the join table.

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE classes (
  id SERIAL PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  capacity SMALLINT NOT NULL DEFAULT 30
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  class_id INTEGER NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  enrolled_at DATE NOT NULL DEFAULT CURRENT_DATE,
  grade NUMERIC(4,2),
  dropped_at DATE,
  PRIMARY KEY (student_id, class_id, enrolled_at)
);

Here the primary key includes enrolled_at, which allows a student to retake a class in a later term without violating the constraint. The grade column is nullable on purpose: an enrollment exists before a grade does. If your join table starts accumulating nullable columns that are only sometimes relevant, that is often a sign the relationship has a lifecycle of its own — which is fine, the join table is a table and can model it.

Design decisions that matter

• Composite PK or not? (post_id, tag_id) as the primary key forbids duplicate pairs. If you need duplicates (a tag applied twice for different periods), use a surrogate id and a UNIQUE constraint instead.
• Extra columns belong on the join. A grade on enrollments, an added_at on post_tags, a role on workspace_members — the relationship itself has attributes.
• ON DELETE CASCADE on both FKs: deleting a post should clean its tags automatically. Use ON DELETE RESTRICT instead when an orphaned relationship would mean data loss — for example, never delete a class that still has graded enrollments.
• Index the second column. The composite PK indexes (post_id, tag_id), which answers 'what tags does this post have' but not 'which posts carry this tag'. Add CREATE INDEX ON post_tags (tag_id) for the reverse lookup.
• Name the table after what it represents, not after the mechanism. enrollments and memberships describe domain concepts; posts_tags_join does not.

Querying a many-to-many

Every question about the relationship goes through one join. 'All tags on post 7' joins post_tags to tags. 'All posts with the tag sql' joins in the other direction. The double join for both tables at once is where the composite key pays off.

-- tags for one post
SELECT t.label
FROM post_tags pt
JOIN tags t ON t.id = pt.tag_id
WHERE pt.post_id = 7;

-- posts that carry a given tag
SELECT p.id, p.title
FROM post_tags pt
JOIN posts p ON p.id = pt.post_id
WHERE pt.tag_id = (SELECT id FROM tags WHERE label = 'sql');

-- count tags per post, including posts with none
SELECT p.id, COUNT(pt.tag_id) AS tag_count
FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.id
GROUP BY p.id;

The last query is a reminder that the join table only holds existing pairs. A post with no tags has no rows in post_tags, so counting through an INNER JOIN would silently drop it. Use LEFT JOIN from the entity side when absence matters.

How diagrams show it

Some notations collapse the join table into a single N:M edge; concrete schemas almost always draw the real table, because it exists in the database and often carries columns. ERDGenerator keeps the join table visible with a 'join table' note — paste the SQL above and you will see three boxes and two 1:N edges, which is exactly what the database holds.

Keeping it visible also documents constraints you would otherwise lose: the enrollments diagram shows that a grade lives on the pair, which answers cardinality questions before anyone writes the API.

Common mistakes

• Storing a comma-separated list ('3,17,42') in a TEXT column instead of building the join table. It cannot be indexed for lookups, cannot enforce referential integrity, and breaks the moment an id contains a comma.
• Forgetting the composite primary key. Without it, duplicate pairs accumulate — usually as a side effect of a retry in application code — and every report that counts rows is wrong.
• Missing index on the second FK column, making reverse lookups ('all enrollments for this class') scan the whole join table.
• Cascading deletes on only one side. Deleting a tag then leaves post_tags rows pointing at nothing — or, if the FK is enforced, the delete fails outright.
• Putting entity data on the join table. The class's room number does not belong on enrollments; it belongs on classes. A column belongs on the join only when it describes the pair.
• Modeling three-way relationships as two independent two-way joins. If a fact is true of the triple (student, class, term), the join table needs all three FKs in its key.

Best practices

• Make both FK columns NOT NULL and cascade or restrict explicitly on both; never rely on defaults.
• Let the composite key express your uniqueness rule, and add a second index on the reversed column order for the reverse query.
• Add created_at (or enrolled_at, added_at) with a default to every join table. Relationships almost always benefit from a timestamp, and retrofitting one is a migration.
• If either side can be bulk-deleted by users, test the cascade behavior with real volumes — a cascade from a parent with a million join rows locks longer than people expect.
• Draw the join table in your diagrams, even when a notation offers the N:M shortcut. The diagram should match the DDL you can run.

Frequently asked questions

Do I always need the third table, or can the database express N:M directly? You always need it. SQL has no native many-to-many constraint between two tables; the join table is the implementation, not an optimization. Diagram notations that draw a bare N:M edge are describing the same thing with the middle table hidden.

Should the join table have its own id column? Only if rows need to be addressed individually or duplicates are legal. For a pure pair-uniqueness table, the composite key is simpler and stores less. Adding a surrogate id does not hurt, but then you must still enforce the pair uniqueness with a UNIQUE constraint — the id alone does not do it.

What about self-referencing many-to-many, like users following users? Same pattern, with both FKs pointing at the same table: follows(follower_id, followed_id) with a composite PK and a CHECK constraint rejecting follower_id = followed_id if you want to forbid self-follows at the database level.

When should a join table be promoted to a full entity? When it starts having behavior of its own: statuses, approvals, soft deletes, per-row permissions. Give it a proper name (subscriptions instead of user_plan_links) and treat it as a first-class table.