SQL / DATA MODELLING AND NORMALISATION
Modelling one-to-many and many-to-many links
Decide which side of a relationship carries the foreign key, and model many-to-many links as junction tables keyed by the pair of ids.
What you will learn
- Put the foreign key on the many side, because one column holds one value
- Model many-to-many as a junction table with PRIMARY KEY (left_id, right_id)
- Store facts about the link itself on the junction row, not on either parent
- Count children with LEFT JOIN and COUNT(child_id) so childless parents show 0
Understanding Modelling one-to-many and many-to-many links
A foreign key column holds exactly one value per row, so it can only ever express "this row belongs to one of those". That single fact decides where the column goes: publisher_id lives on book, because a book has one publisher while a publisher has many books. There is nothing you can add to publisher to hold many book ids, which is why the direction of the reference, not the order in which you created the tables, is what records the cardinality.
When both directions are many, as with books and authors, neither table has anywhere to put a single id, so the relationship gets a table of its own. Each row of book_author is one pair, and the pair is what identifies the row, which is why PRIMARY KEY (book_id, author_id) is the default choice. Read the result as two one-to-many relationships aimed inward: a book has many link rows, an author has many link rows, and every link row belongs to exactly one of each.
Cardinality is whatever the constraints permit, not what the diagram intended. NOT NULL on book.publisher_id makes the parent mandatory, a UNIQUE on that same column would quietly turn one-to-many into one-to-one, and dropping the composite key from book_author lets the same pair be inserted twice so the link becomes a bag instead of a set. Because the link is a real table, it can also carry facts that belong to neither parent: the position an author is credited in, the date a student enrolled, whether a tag was applied by a person or a script.
Every query over these shapes has to account for row multiplication. Joining publisher to book to book_author to author produces one row per publisher-book-author combination, so a book with two authors appears twice; that is correct behaviour, not a bug, and it is why aggregates over joined many-to-many data need a GROUP BY on the key you actually want one row per.
CREATE TABLE publisher (
publisher_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
publisher_id INTEGER NOT NULL REFERENCES publisher(publisher_id)
);
CREATE TABLE author (
author_id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE book_author (
book_id INTEGER NOT NULL REFERENCES book(book_id),
author_id INTEGER NOT NULL REFERENCES author(author_id),
PRIMARY KEY (book_id, author_id)
);
INSERT INTO publisher VALUES (1, 'Ravensbrook'), (2, 'Larkfield');
INSERT INTO book VALUES
(1, 'Tidal Systems', 1),
(2, 'Quiet Machines', 1),
(3, 'Field Notes', 2);
INSERT INTO author VALUES (1, 'Okonkwo'), (2, 'Vasquez'), (3, 'Lindqvist');
INSERT INTO book_author VALUES (1,1), (1,2), (2,2), (3,1), (3,3);
SELECT p.name AS publisher, b.title, a.name AS author
FROM publisher p
JOIN book b ON b.publisher_id = p.publisher_id
JOIN book_author ba ON ba.book_id = b.book_id
JOIN author a ON a.author_id = ba.author_id
ORDER BY p.name, b.title, a.name;A foreign key can hold only one value, so it belongs on the many side; when both sides are many, the relationship itself becomes a table keyed by the pair.
Worked examples
Counting the many side, including empty parents
Aggregating a one-to-many link so that a publisher with no books still appears with a count of zero.
CREATE TABLE publisher (publisher_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
publisher_id INTEGER NOT NULL REFERENCES publisher(publisher_id)
);
INSERT INTO publisher VALUES (1,'Ravensbrook'), (2,'Larkfield'), (3,'Holt & Mear');
INSERT INTO book VALUES (1,'Tidal Systems',1), (2,'Quiet Machines',1), (3,'Field Notes',2);
SELECT p.name AS publisher, COUNT(b.book_id) AS books
FROM publisher p
LEFT JOIN book b ON b.publisher_id = p.publisher_id
GROUP BY p.publisher_id, p.name
ORDER BY books DESC, p.name;Example explained
Line 1The link exists only in book.publisher_id; publisher has no column pointing back at its books.
Line 2LEFT JOIN keeps 'Holt & Mear' in the result even though no book row references it.
Line 3COUNT(b.book_id) counts non-null values, so the unmatched publisher scores 0; COUNT(*) would have reported 1.
Line 4GROUP BY on the parent key collapses the fan-out back to one row per publisher.
The composite key is what stops duplicate links
A pure junction table rejecting a second insert of a pair that already exists.
CREATE TABLE student (student_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE course (course_id INTEGER PRIMARY KEY, code TEXT NOT NULL);
CREATE TABLE enrolment (
student_id INTEGER NOT NULL REFERENCES student(student_id),
course_id INTEGER NOT NULL REFERENCES course(course_id),
PRIMARY KEY (student_id, course_id)
);
INSERT INTO student VALUES (1,'Amara'), (2,'Bo');
INSERT INTO course VALUES (10,'SQL101'), (20,'STAT200');
INSERT INTO enrolment VALUES (1,10), (1,20), (2,10);
SELECT s.name, c.code
FROM enrolment e
JOIN student s ON s.student_id = e.student_id
JOIN course c ON c.course_id = e.course_id
ORDER BY s.name, c.code;
INSERT INTO enrolment VALUES (1,10);Example explained
Line 1enrolment stores no data of its own, only the two foreign keys that together name a pair.
Line 2PRIMARY KEY (student_id, course_id) makes the pair the identity of the row, so (1,10) can exist once.
Line 3The final INSERT is refused by that key; the wording of the message differs between engines, the refusal does not.
Line 4Without the composite key the duplicate would be accepted and Amara would count as two SQL101 students.
Attributes that belong to the link, not the parents
A junction table carrying credit order and role, which describe the pairing rather than the book or the author.
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE author (author_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE book_author (
book_id INTEGER NOT NULL REFERENCES book(book_id),
author_id INTEGER NOT NULL REFERENCES author(author_id),
credit_order INTEGER NOT NULL,
role TEXT NOT NULL,
PRIMARY KEY (book_id, author_id),
UNIQUE (book_id, credit_order)
);
INSERT INTO book VALUES (1,'Tidal Systems');
INSERT INTO author VALUES (1,'Okonkwo'), (2,'Vasquez'), (3,'Lindqvist');
INSERT INTO book_author VALUES
(1, 2, 1, 'author'),
(1, 1, 2, 'author'),
(1, 3, 3, 'translator');
SELECT ba.credit_order, a.name, ba.role
FROM book_author ba
JOIN author a ON a.author_id = ba.author_id
WHERE ba.book_id = 1
ORDER BY ba.credit_order;Example explained
Line 1credit_order and role are properties of one book-author pairing, so neither parent table can hold them.
Line 2PRIMARY KEY (book_id, author_id) still prevents the same person being credited twice on one book.
Line 3UNIQUE (book_id, credit_order) prevents two people both claiming first position on the same book.
Line 4ORDER BY ba.credit_order makes the byline order come from stored data instead of insertion order.
Important notes
SQLite ignores REFERENCES unless you run PRAGMA foreign_keys = ON first, so an orphan child insert can appear to succeed; a junction table's composite primary key is enforced either way.
PRIMARY KEY (a_id, b_id) means the pair may exist exactly once, so if the pair legitimately recurs, as with a student retaking a course, the distinguishing column (term, attempt) must become part of the key.
Common mistakes
Putting book_id on publisher instead of publisher_id on book: the publisher then needs one row per book, its name and address repeat on every row, and the parent table has silently become a link table.
Giving a junction table an auto-increment id and no key or UNIQUE on (a_id, b_id): the same enrolment can be inserted twice, so every COUNT over the link is quietly too high and deleting one link leaves the other behind.
Joining two independent many-to-many links of the same parent in one query, such as a book's authors and its tags: 3 authors and 4 tags produce 12 rows, and any SUM or COUNT over that result multiplies instead of counting.
Try it yourself
Change, predict, then run
Create recipe, ingredient and recipe_ingredient tables in the editor, putting quantity and unit on the link row and PRIMARY KEY (recipe_id, ingredient_id), then write one query listing each recipe with its ingredient lines. Finish by inserting the same ingredient into the same recipe twice and read the error the database gives you.
Open the SQL workspaceCheck your understanding
photo and tag are linked by photo_tag(photo_id, tag_id) with PRIMARY KEY (photo_id, tag_id). You then add UNIQUE (photo_id). What does the model now allow?
- Each photo can carry at most one tag, while a tag can still be attached to many photos
- Each tag can be attached to at most one photo, while a photo can still carry many tags
- Nothing changes, because the composite primary key already guarantees uniqueness
- Duplicate (photo_id, tag_id) pairs become possible again
Show answer
UNIQUE (photo_id) lets a photo appear in at most one link row, so a photo gets at most one tag while nothing restricts how many link rows a tag_id appears in; the relationship has collapsed to one-to-many from tag to photo. Option three is tempting because the composite primary key does forbid repeated pairs, but forbidding repeated pairs still permits one photo_id in many rows with different tag_ids, so constraining photo_id alone is a strictly stronger rule.