The relational databases split the data across the tables because the repetition is a liability and not a convenience. A book belongs to an author, an author belongs to no particular book, and a loan ties the two of them together for a period of the time. Storing each fact once and linking the facts with the keys is what makes a database correct, and the JOIN is the operation that reassembles the wide and denormalised view that the people actually want to read.
This is the first lesson in the Joining Tables module, and the next lesson in this module is about "How Does an INNER JOIN Match Rows?".
Why do the relational databases split the data across the tables?
The library in this module has six tables, and five of them exist so that some piece of the information is stored exactly once. The alternative, which is one wide table with everything repeated on every row, is not forbidden, and for a small spreadsheet it works fine. It is the wide table that generates every awkward data problem afterwards.
What are the library's tables?
Splitting the data is only half of the answer. The split version needs a shape. Five of the tables hold the library's facts. There are the authors, who wrote it. There are the branches, where it is shelved. There are the books, which are the bridge between the two. There are the members, who borrow. And there are the loans, which are the fact that one member borrowed one book on one date.
The sixth table in this module, books_flat, is the wide version that we are about to argue against, and it exists only so that you can compare the two.
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
country TEXT,
birth_year INTEGER
);
CREATE TABLE branches (
branch_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
);
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER REFERENCES authors(author_id),
branch_id INTEGER REFERENCES branches(branch_id),
published_year INTEGER,
genre TEXT,
pages INTEGER
);
CREATE TABLE members (
member_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
joined_on TEXT,
email TEXT
);
CREATE TABLE loans (
loan_id INTEGER PRIMARY KEY,
book_id INTEGER REFERENCES books(book_id),
member_id INTEGER REFERENCES members(member_id),
loaned_on TEXT,
returned_on TEXT
);Five of the INSERT statements load the catalogue. There are seven authors, three branches, thirteen books, nine members and fourteen loans.
INSERT INTO authors (author_id, name, country, birth_year) VALUES
(1, 'Ursula Vane', 'France', 1971),
(2, 'Tomas Guerrero', 'Peru', 1968),
(3, 'Iris Lindqvist', 'Sweden', 1975),
(4, 'Nnamdi Obi', 'Nigeria', 1980),
(5, 'Marguerite Aubert', 'France', 1965),
(6, 'Kenji Mori', 'Japan', 1972),
(7, 'Selina Voss', 'Kenya', 1990);
INSERT INTO branches (branch_id, name, city) VALUES
(1, 'Riverside Branch', 'Bristol'),
(2, 'Hillcrest Branch', 'Cardiff'),
(3, 'Old Town Branch', 'Glasgow');
INSERT INTO books (book_id, title, author_id, branch_id, published_year, genre, pages) VALUES
(1, 'The Salt Ledger', 1, 1, 2019, 'historical', 342),
(2, 'Harbour of Small Boats', 1, 2, 2021, 'technical', 288),
(3, 'Cartography for Beginners', 2, 1, 2018, 'reference', 246),
(4, 'The Copper Wire', 2, 2, 2020, 'technical', 512),
(5, 'Winter Light', 3, 1, 2022, 'fiction', 301),
(6, 'The Long Thaw', 3, 3, 2017, 'historical', 428),
(7, 'Signals and Noise', 4, 2, 2023, 'technical', 604),
(8, 'The Cartographer''s Daughter', 6, 1, 2020, 'fiction', 356),
(9, 'Letters from the Delta', 5, 3, 2016, 'fiction', 274),
(10, 'Orbit of Dust', 4, 1, 2021, 'fiction', 398),
(11, 'The Quiet Harbour', 5, 2, 2015, 'historical', 331),
(12, 'Field Notes on Birdsong', 6, 3, 2024, 'reference', 219),
(13, 'A History of Fermentation', 5, 2, 2013, 'historical', 467);
INSERT INTO members (member_id, name, joined_on, email) VALUES
(1, 'Priya Shah', '2023-02-14', '[email protected]'),
(2, 'Daniel Okoye', '2022-06-01', '[email protected]'),
(3, 'Hana Sato', '2024-01-20', '[email protected]'),
(4, 'Louis Marchand', '2021-09-05', '[email protected]'),
(5, 'Amara Nwosu', '2023-11-11', '[email protected]'),
(6, 'Ines Duarte', '2022-03-30', '[email protected]'),
(7, 'Rafael Costa', '2024-04-02', '[email protected]'),
(8, 'Ethan Brooks', '2021-05-18', '[email protected]'),
(9, 'Freya Andersson', '2024-08-27', '[email protected]');
INSERT INTO loans (loan_id, book_id, member_id, loaned_on, returned_on) VALUES
(1, 1, 1, '2025-01-13', '2025-01-27'),
(2, 5, 2, '2025-01-16', '2025-02-03'),
(3, 3, 3, '2025-01-21', '2025-02-07'),
(4, 9, 4, '2025-02-04', '2025-02-18'),
(5, 7, 5, '2025-02-11', '2025-03-05'),
(6, 2, 1, '2025-02-19', '2025-03-05'),
(7, 1, 4, '2025-03-06', '2025-03-20'),
(8, 7, 8, '2025-03-12', '2025-03-26'),
(9, 4, 6, '2025-03-18', '2025-04-01'),
(10, 6, 7, '2025-03-25', '2025-04-08'),
(11, 8, 2, '2025-04-02', '2025-04-16'),
(12, 10, 3, '2025-04-09', '2025-04-23'),
(13, 11, 6, '2025-04-15', '2025-04-29'),
(14, 12, 8, '2025-04-22', '2025-05-06');There are two facts in there that will matter later. Selina Voss has no books at all, and thirteen books belong to somebody who has never borrowed one. Those two gaps are what the outer joins of lesson 23 exist to reveal.
What would the flat table look like?
You start from the question of listing every book with its author and the branch that holds it. The wide answer puts an author name on every book row and a branch name on every book row.
CREATE TABLE books_flat (
book_id INTEGER PRIMARY KEY, title TEXT, author_name TEXT, author_country TEXT,
branch_name TEXT, published_year INTEGER, genre TEXT, pages INTEGER
);You populate it from the split tables. It is the same thirteen books, with the name and the country of the author and the name of the branch written out on every row, and the row count is reassuringly ordinary.
INSERT INTO books_flat (book_id, title, author_name, author_country, branch_name, published_year, genre, pages) VALUES
(1, 'The Salt Ledger', 'Ursula Vane', 'France', 'Riverside Branch', 2019, 'historical', 342),
(2, 'Harbour of Small Boats', 'Ursula Vane', 'France', 'Hillcrest Branch', 2021, 'technical', 288),
(3, 'Cartography for Beginners', 'Tomas Guerrero', 'Peru', 'Riverside Branch', 2018, 'reference', 246),
(4, 'The Copper Wire', 'Tomas Guerrero', 'Peru', 'Hillcrest Branch', 2020, 'technical', 512),
(5, 'Winter Light', 'Iris Lindqvist', 'Sweden', 'Riverside Branch', 2022, 'fiction', 301),
(6, 'The Long Thaw', 'Iris Lindqvist', 'Sweden', 'Old Town Branch', 2017, 'historical', 428),
(7, 'Signals and Noise', 'Nnamdi Obi', 'Nigeria', 'Hillcrest Branch', 2023, 'technical', 604),
(8, 'The Cartographer''s Daughter', 'Kenji Mori', 'Japan', 'Riverside Branch', 2020, 'fiction', 356),
(9, 'Letters from the Delta', 'Marguerite Aubert', 'France', 'Old Town Branch', 2016, 'fiction', 274),
(10, 'Orbit of Dust', 'Nnamdi Obi', 'Nigeria', 'Riverside Branch', 2021, 'fiction', 398),
(11, 'The Quiet Harbour', 'Marguerite Aubert', 'France', 'Hillcrest Branch', 2015, 'historical', 331),
(12, 'Field Notes on Birdsong', 'Kenji Mori', 'Japan', 'Old Town Branch', 2024, 'reference', 219),
(13, 'A History of Fermentation', 'Marguerite Aubert', 'France', 'Hillcrest Branch', 2013, 'historical', 467);SELECT COUNT(*) AS rows_in_flat_table FROM books_flat;rows_in_flat_table
------------------
13There are thirteen books and there are thirteen rows. Nothing is wrong yet. The problem shows up in what happens next.
What goes wrong with the wide table?
Every book by an author carries the name and the country of that author. You group the flat table by the author, and the repetition becomes measurable.
SELECT author_name, COUNT(*) AS rows_repeating_the_name
FROM books_flat
GROUP BY author_name
ORDER BY rows_repeating_the_name DESC;author_name rows_repeating_the_name
---------------- ----------------------
Marguerite Aubert 3
Ursula Vane 2
Tomas Guerrero 2
Nnamdi Obi 2
Kenji Mori 2
Iris Lindqvist 2The name and the country of Marguerite Aubert sit on three different rows. Now you imagine correcting her birth year in the flat table. You miss one row, and the database simultaneously believes two different things about the same author. No constraint will stop you, because there is nothing in the schema saying that those three cells are supposed to agree.
The same hazard applies to a title typo, a branch that is renamed, and a country that changes its spelling.
You split the data, and the hazard disappears. An author is one row with one name, and a book stores an integer that points at that row. Correcting the name touches one row, and every book that references it shows the new name automatically. That is the whole reason for the split. One fact, one place.
How does the JOIN give the wide view back?
Nobody wants to query the split tables separately. A join matches the rows across the tables and it produces the wide shape on demand, at the read time, without storing it.
SELECT b.book_id, b.title, a.name AS author, br.name AS branch
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN branches br ON b.branch_id = br.branch_id
ORDER BY b.book_id
LIMIT 6;book_id title author branch
------- ------------------------- -------------- ----------------
1 The Salt Ledger Ursula Vane Riverside Branch
2 Harbour of Small Boats Ursula Vane Hillcrest Branch
3 Cartography for Beginners Tomas Guerrero Riverside Branch
4 The Copper Wire Tomas Guerrero Hillcrest Branch
5 Winter Light Iris Lindqvist Riverside Branch
6 The Long Thaw Iris Lindqvist Old Town BranchNotice the two aliases. The b, a and br are the labels for the tables, and they exist because the books, the authors and the branches all have the columns called name. Every table in the schema carries an integer column that points at a row in another table. The books.author_id points at the authors.author_id, and the books.branch_id points at the branches.branch_id. Those pointing columns are the left-hand sides of the joins, and the key that they point at is the right-hand side.
Lesson 22 makes that matching explicit, and modules 08 and 09 formalise it as a primary key and a foreign key.
What does the split buy you that a wide table cannot give?
- Correctness: the name of an author exists once, so it cannot disagree with itself.
- Space: the country of the author is stored once instead of once per book.
- Speed: a query for "all books by Ursula Vane" can use an index on
books.author_idinstead of scanning every title (lesson 50). - Flexibility: a book with three authors is three rows in a link table, and not three copies of the book.
- Integrity: a branch cannot be deleted while the books still point at it, if the schema says so (lesson 48).
The cost is the one that this module exists to pay. A query that needs the data from three tables must spell out three joins, and the order of the output rows depends on how the tables are chained. That is a fair trade, and it is the trade that every reporting tool in the market is built on.
Quick reference
- The repeated values are a symptom. You store the fact once and you reference it by the key.
- A join reproduces the wide view at the read time, so the duplication never has to be stored.
- The join keys are the matching column values. There is a primary key on one side and a foreign key on the other.
- The table aliases are mandatory as soon as two of the tables share a column name.
- Splitting the data costs the query complexity and it buys the correctness, the space, the speed and the flexibility.
Design drill: you take the flat books_flat table above and you list the two ways that it could end up holding contradictory information about one author. Then you describe the split version that makes each contradiction impossible.