Many-to-Many in an ERD Shouldn't Mean Hand-Building the Junction Table

Disclosure

I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR

Relational databases cannot store a many-to-many relationship directly, so most ERD tools make you hand-build the junction table - Microsoft documents six manual steps for one relationship. In Schemity, you drag between the two entities and pick N:N; the junction table appears with its foreign keys, composite primary key, and convention-following name already in place, as a real table you can rename and extend.

One refinement: keep that composite primary key when both parents live in the same bounded context, but give a junction its own id plus a unique constraint over the pair when it bridges two contexts or grows a lifecycle of its own, like a subscription - so the pairing can recur and the contexts can split databases later without rewriting the table's identity.

The one-gesture principle

A many-to-many relationship is one conceptual fact, and expressing one fact in your diagram should cost one gesture - not a hand-assembled third table with copied key columns and two carefully aimed relationship lines. The junction table is a mechanical consequence of the relationship, and mechanical consequences are exactly what a tool should produce for you.

Most ERD tools disagree. Microsoft's documentation for the database diagram designer in SQL Server Management Studio lays out six manual steps to map one many-to-many relationship:

1. Add the two tables.
2. Right-click to create a third.
3. Rename it in a dialog.
4. Copy the primary key columns from each parent into it.
5. Set the primary key to include all of the copied columns.
6. Define a one-to-many relationship from each parent - making sure the junction lands on the "many" side both times.

Six steps, five objects (three tables and two relationships), one fact. And this is not a legacy-tool quirk. dbdiagram.io - a DSL-first tool built by people who think hard about schema notation - had users asking for native many-to-many support for years before the <> syntax shipped in June 2022. As one user put it in February of that year: "I have no idea why this is not implemented and it needs a vote, its basic ERD stuff." Until then, every book-author pairing meant writing the associative table out by hand.

Hand-built junction tables are where schemas quietly go wrong

The six steps are tedious, but tedium is the smaller problem. Each step is also a chance to encode a mistake that the diagram will happily render as correct:

• Forget the composite key and nothing stops the same pairing from being inserted twice. A student_courses table without uniqueness over (student_id, course_id) records the same enrollment as many times as your application bugs allow.
• Aim a crow's foot the wrong way and your junction sits on the "one" side of a relationship, which quietly turns the whole construction back into a 1:N.
• Name the keys inconsistently - studentId in one junction, student_id in the next - and the schema drifts one hand-typed field at a time.

These are not beginner-only failures. They are what happens when a tool outsources a mechanical construction to a human, on the tenth junction table of a long modeling session. The difference between a 1:N and an N:N relationship is precisely the junction table and its composite key - get either wrong and the diagram claims a cardinality the schema does not enforce.

How do I create a many-to-many relationship in an ERD?

Conceptually, the answer is always the same: a many-to-many relationship cannot hold a foreign key on either side alone, so you resolve it into a junction table - a third table carrying one foreign key per parent - which turns one N:N into two 1:N relationships. That resolution is fully determined the moment you decide the relationship is N:N. Nothing about it requires human judgment.

So Schemity treats it as output, not input. Drag from one entity to the other and pick N:N in the relationship dialog, and the junction table is created automatically: one foreign key field per parent, the two keys forming a composite primary key so each pairing is unique, and a name generated from your naming convention, like students_courses. Prefer to place the table yourself? Draw two relationships into an empty entity and Schemity detects it as a junction table and does the same wiring. Foreign keys follow the snake_case or camelCase convention you set per connection, and land below the primary key instead of wherever the cursor happened to be.

Comparison: Hand-built (SSMS-style) vs. Schemity

The junction table is a real table, not a notation trick

Some tools go to the opposite extreme and hide the junction entirely - a single N:N line with no table behind it. That reads nicely until the pairing needs data of its own, which it almost always eventually does: an enrollment gets a grade and an enrolled_at, a role-permission grant gets a granted_by. A junction your tool refuses to show is a junction you cannot extend.

Schemity's auto-created junction is an ordinary entity. Rename it, recolor it, add the attribute columns the relationship accrues, and when a pairing needs extra scoping, add a named composite unique constraint - each unique set gets its own colored U badge, so two overlapping rules on the same junction stay distinguishable. The full crow's foot notation on both lines keeps the two 1:N halves readable, with cardinality, ON DELETE, and ON UPDATE all configurable. You get the honesty of the physical schema and the convenience of the one-gesture shorthand - not a choice between them.

That combination matters most in the schemas that lean hardest on junctions. A multi-tenant RBAC design is essentially junction tables all the way down - members to roles, roles to permissions - and each one needs exactly the wiring described above. Building that model in a desktop ERD tool that constructs every junction correctly by default means the tenth one is as sound as the first, whether you are sketching offline on a laptop or working against a live development database. And because the auto-junction is derived from the relationship's cardinality rather than painted on, it obeys the same rule that keeps 1:1 relationships from being drawn as 1:N: the diagram only says what the constraints actually enforce.

When should a junction table have a composite primary key vs. its own id?

There are two legitimate shapes for a junction table, and choosing between them is not a matter of taste - it is a matter of where the two parents live.

The first shape is the one the auto-junction gives you: the two foreign keys are the primary key. The pairing itself is the row's identity, uniqueness comes free from the key, and there is nothing extra to maintain.

The second shape gives the junction its own surrogate id, keeps the two foreign keys as ordinary fields, and enforces the pairing with a composite unique constraint over both. Same duplicate protection - a different answer to the question of what the row is.

The rule that decides between them is the context boundary

• Both parents in the same bounded context: take the composite primary key. The link is an internal detail of one domain - nothing outside the context will ever address a link row directly, the two tables will live and move together, and the auto-created shape is exactly right. Drag, pick N:N, done. Maximum convenience, zero ceremony.

• Parents in two different contexts: give the junction its own id and a unique-together constraint. A junction that bridges contexts is not an internal detail - it is the coupling point between two domains, and you cannot know how long those domains will keep sharing one database. If one context is ever extracted into its own service and schema, a foreign key cannot cross the database boundary anymore: the constraint gets dropped and the column degrades gracefully into a soft reference. When that column was an ordinary field guarded by a unique constraint, the split costs one small ALTER, and the row's own id - the thing audit records, events, and APIs have been pointing at - survives untouched. When that column was half of the primary key, the split forces you to restructure the table's identity, and everything that referenced it, at the worst possible moment.

There is a second hint that points to the surrogate shape, and it works even inside a single context: the junction has a lifecycle of its own. Some link tables never stay bookkeeping. A subscription is, structurally, the junction between a customer and a plan - a customer subscribes to many plans, a plan serves many customers - yet nobody models it as a bare pairing, because a subscription is born, trials, goes past due, and gets canceled; Stripe's API gives the subscription object eight distinct lifecycle statuses, from trialing to canceled. An enrollment is the same story one industry over.

The moment a link row starts changing state, two things quietly break the composite-primary-key shape:

• Other tables start pointing at the link row. Invoices reference the subscription, certificates reference the enrollment. With a surrogate id, each of those is one clean foreign key; with a composite primary key, every referencing table must carry both parent keys forever.
• The pairing stops being unique for all time. A customer cancels and later resubscribes to the same plan; a student retakes the same course next term. Uniqueness becomes a scoped business rule - one active subscription per customer-plan pair - which a named unique constraint can be evolved or re-scoped to express, and which a primary key can never become without rebuilding the table.

So the full heuristic: take the composite primary key while the junction is pure bookkeeping inside one bounded context, and reach for the surrogate id plus unique-together constraint when the junction crosses a context boundary or the moment you catch it accruing a lifecycle. In practice the two signals travel together - the link tables that grow into real business entities are usually the very ones coupling two domains.

Comparison: Composite primary key vs. own id + unique-together constraint

In Schemity

• Composite primary key shape: Drag between parents, pick N:N.
• Surrogate id shape: Create the entity yourself - the active entity template already gives it its id primary key - then drag a relationship from each parent: both foreign keys land right below the primary key, named by your convention. Because the table now contains a field that is not a foreign key, the junction auto-detection stays out of the way and the surrogate shape is preserved. Finish by spanning the two foreign keys with a named composite unique constraint, and its colored U badge announces the pairing rule on the diagram itself.

The boundary that drives this decision is also visible in the tool. A context view is a saved, focused view of your schema that shows only a subset of entities and the relationships between them - the practical form of a bounded context ERD. Put each domain in its own context view, and every cross-context junction you model shows up on the Context Map as a dependency arrow with its foreign keys counted - so the junctions that deserve the surrogate-key treatment are precisely the ones the map highlights.

Your ERD tool knows what a many-to-many relationship implies. It should be the one doing the implying - and when you deliberately overrule the default shape at a context boundary, it should make that shape just as cheap to build.