One-to-many vs many-to-many relationships, with examples
Published
Every relationship between two tables is one of three kinds, and the kind decides one practical thing: where the foreign key goes. In a one-to-many relationship it goes on the “many” table. In a one-to-one it goes on either, with a unique constraint. In a many-to-many it cannot go on either table, so it goes in a third one.
The quickest test is to ask the question in both directions. Can one author have many books? Can one book have many authors? Two answers, and the pair tells you the kind.
One-to-many
One row on one side, any number on the other. An author has many books; a book, in this model, has one author. It is by far the most common relationship in any schema: orders to a customer, comments to a post, line items to an order.
The foreign key lives on the many side. Each book stores the id of its author. The author row stores nothing about its books, which is why you can add a thousandth book without touching the author.
Table authors {
id integer [pk]
name varchar [not null]
}
Table books {
id integer [pk]
title varchar [not null]
author_id integer [not null, ref: > authors.id]
}On an ER diagram this is the line with a single tick at the authors end and a
crow’s foot at the books end. If the column allows nulls, the relationship is
optional: a book may have no author yet.
One-to-one
One row on each side. A user has one profile, and a profile belongs to one user. It is a one-to-many with one extra rule: the foreign key is also unique, so no two rows can point at the same parent. The tidiest way to say that is to make the foreign key the primary key of the second table.
Table users {
id integer [pk]
email varchar [not null, unique]
}
Table user_profiles {
user_id integer [pk, ref: - users.id]
bio text
avatar_url varchar
}A one-to-one is always a choice, because the two tables could be one. It earns its keep when the second table is optional, large, or sensitive: columns most queries do not need, or ones with stricter access. If neither is true, merge them.
Many-to-many
Any number on both sides. A student takes many courses, and a course has many students. No
column on students can hold a list of courses, and none on courses can hold a list of students, so the relationship gets a table of its own.
Table students {
id integer [pk]
name varchar [not null]
}
Table courses {
id integer [pk]
title varchar [not null]
}
Table enrollments {
student_id integer [not null, ref: > students.id]
course_id integer [not null, ref: > courses.id]
enrolled_on date [not null]
grade varchar(2)
Indexes {
(student_id, course_id) [pk]
}
}That middle table goes by several names: join table, junction table, associative table. It is two one-to-many relationships back to back, and its primary key is usually the pair of foreign keys, which also stops the same student being enrolled in the same course twice.
Notice that it is the natural home for facts about the pairing itself. The date of enrolment
and the grade belong to neither the student nor the course, only to the two together. When a
join table starts collecting columns like these, it has become an entity in its own right, and
is worth naming for what it is (enrollments) instead of for what it joins
(students_courses).
A table that references itself
The two sides of a relationship can be the same table. An employee has a manager, who is another employee. That is an ordinary one-to-many whose foreign key points back at its own table, and it is how trees are stored: categories, comment threads, org charts.
Table employees {
id integer [pk]
name varchar [not null]
manager_id integer [ref: > employees.id]
}The column has to allow nulls, or the row at the top of the tree could never be inserted.
How to tell which one you have
| Can one A have many B? | Can one B have many A? | Kind | Foreign key goes |
|---|---|---|---|
| Yes | No | One-to-many | On B |
| No | No | One-to-one | On either, and unique |
| Yes | Yes | Many-to-many | In a join table |
The answers come from the business, not the data you have today. Every book in your database may have a single author right now; the question is whether a co-authored book is allowed to exist. Changing a one-to-many into a many-to-many after launch means a new table and a data migration, so it is the question worth five minutes at the start.
See it drawn
Relationships are much easier to check as a picture than as a list of columns. Paste any example above into the ER diagram tool to see the lines and their crow’s feet, or open a schema template to read the relationships of a full system. The syntax for each kind is on the DBML cheat sheet.