A many-to-many relationship is an association between two entities in which each instance of one entity relates to many instances of the other, and the reverse is also true. Students enroll in many courses and each course has many students; products appear in many orders and each order contains many products. In data modeling, it is one of the three basic relationship cardinalities, alongside one-to-one and one-to-many.
Relational databases cannot store a many-to-many relationship directly between two tables. It is resolved with a junction table, also called an associative, bridge or link table, which turns one many-to-many relationship into two one-to-many relationships.
How a junction table resolves many-to-many
A junction table resolves many-to-many by storing one row per pairing of the two related entities. Each row holds a foreign key to each parent table, and the combination of the two keys identifies the pairing. A student_course table with student_id and course_id records every enrollment as its own row.
Placing the relationship in either parent table instead would require a repeating group, such as a list of course identifiers inside the student row. That violates first normal form, makes the list impossible to index or constrain properly, and turns every enrollment change into a rewrite of the parent row.
| Column | Role | Typical constraint |
|---|---|---|
| student_id | Reference to the first entity | Foreign key, not null |
| course_id | Reference to the second entity | Foreign key, not null |
| enrolled_at | Attribute of the relationship itself | Not null, default current time |
| grade | Attribute of the relationship itself | Nullable until assessed |
| (student_id, course_id) | Identity of the pairing | Composite primary key or unique constraint |
Columns such as enrollment date or grade belong to neither parent alone: they describe the pairing. When a junction table gathers several such attributes, it often becomes a full entity with its own name, such as Enrollment or Order line.
Many-to-many compared with other cardinalities
Cardinality states how many instances on each side can participate in a relationship, and it decides where foreign keys live in the physical model.
| Cardinality | Example | Relational implementation |
|---|---|---|
| One-to-one | Person and passport record | Foreign key with a unique constraint |
| One-to-many | Customer and orders | Foreign key on the many side |
| Many-to-many | Products and orders | Junction table with two foreign keys |
| Recursive many-to-many | Users following users | Junction table with two keys to the same table |
| Ternary | Supplier, part and project | Junction table with three foreign keys |
In an ER diagram, many-to-many is drawn with a crow’s foot at both ends of the line in crow’s foot notation, or as M:N in Chen notation. Conceptual models may show it directly; the logical model replaces it with the junction entity before tables are created.
Many-to-many in different database models
Each database model represents many-to-many differently, and the choice influences query cost and integrity guarantees.
| Model | Representation | Trade-off |
|---|---|---|
| Relational | Junction table | Strong integrity; joins needed to traverse |
| Document | Arrays of references in one or both documents | Fast reads; no enforced referential integrity |
| Graph | Edges between nodes, with properties | Natural traversal; less suited to bulk aggregation |
| Star schema | Bridge table between a dimension and a fact | Correct allocation requires weighting factors |
In dimensional modeling, many-to-many appears when one fact relates to several dimension members, such as one bank account with several holders. A bridge table with an optional weighting factor prevents double counting when measures are summed across the bridge.
Designing and querying many-to-many relationships
A well-designed junction table is narrow, constrained and indexed in both directions. Queries traverse it from either side, so both access paths must be efficient.
- Declare a composite primary key or unique constraint on the two foreign keys to block duplicate pairings, unless repeated pairings are meaningful.
- Add a second index in reverse column order, because the composite key index serves lookups by its leading column only.
- Choose ON DELETE behavior deliberately: cascading deletes of pairings is usual, cascading to the other parent almost never is.
- Use a surrogate key only when other tables must reference individual pairings, and still keep the unique constraint on the natural pair.
Typical queries join the parent, the junction table and the other parent. Aggregations such as courses per student read only the junction table, which is why keeping it narrow pays off on large data volumes.
Common mistakes with many-to-many
Most problems arise from modeling the relationship too late or too loosely. They usually surface as duplicated data, wrong totals or slow queries.
| Symptom | Cause | Fix |
|---|---|---|
| Comma-separated IDs in a column | Relationship stored as a list | Introduce a junction table |
| Duplicate pairings | No unique constraint on the pair | Add a composite key |
| Inflated sums after a join | Fan-out across the junction table | Aggregate before joining or use a bridge weight |
| Slow reverse lookups | Only one index direction | Add the reversed index |
| Orphaned pairings | Missing foreign keys | Enforce referential integrity |
The idea in brief
Think of a school sign-up sheet. A student can join several clubs, and every club has several members. Writing all clubs on the student’s card, or all members on the club’s poster, quickly gets messy. The tidy solution is a separate register with one line per membership: this student, this club, joined on this date.
That register is what databases call a junction table. It keeps each fact in one place and makes it easy to ask who belongs where from either direction.
A practical scenario
A streaming service models films and actors. An early design stored a text field of actor names in each film row, which made it impossible to list all films of one actor reliably because names were spelled inconsistently.
The data team creates an actor table and a film_actor junction table with film_id, actor_id, the character name and the billing order. A composite primary key prevents duplicates, and a reverse index on actor_id supports actor pages. About 60,000 films and 300,000 credits migrate in one release. Actor pages now load from a single indexed lookup, and corrections to a name happen once instead of in hundreds of film rows.