Relational databases split data across tables because repetition is a liability, not a convenience. A book belongs to an author; an author belongs to no particular book; a loan ties the two together for a period of time. Storing each fact once and linking the facts with keys is what makes a database correct, and JOIN is the operation that reassembles the wide, denormalised view that people actually want to read.
Why do relational databases split data across tables?
The library in this module has six tables, and five of them exist so that some piece of information is stored exactly once. The alternative — 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 the answer — the split version needs a shape. Five tables hold the library's facts: authors (who wrote it), branches (where it is shelved), books (the bridge between the two), members (who borrows) and loans (the fact that one member borrowed one book on one date). The sixth table in this module, books_flat, is the wide version we are about to argue against, and it exists only so 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 INSERT statements load the catalogue: seven authors, three branches, thirteen books, nine members and fourteen loans. Two facts in there 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.
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');
What would the flat table look like?
Start from the question "list 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
);
Populate it from the split tables — the same thirteen books, with the author's name and country and the branch's name 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
------------------
13
Thirteen books, 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 that author's name and country. Group the flat table by 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 2
Marguerite Aubert's name and country sit on three different rows. Now imagine correcting her birth year in the flat table. Miss one row and the database simultaneously believes two different things about the same author — and no constraint will stop you, because there is nothing in the schema saying those three cells are supposed to agree. The same hazard applies to a title typo, a branch that is renamed, a country that changes its spelling.
Split the data and the hazard disappears. An author is one row with one name; a book stores an integer pointing 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 JOIN give the wide view back?
Nobody wants to query the split tables separately. A join matches rows across tables and produces the wide shape on demand, at 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 Branch
Note the two aliases: b, a and br are labels for the tables, and they exist because books, authors and branches all have columns called name. Every table in the schema carries an integer column that points at a row in another table — books.author_id points at authors.author_id, and books.branch_id points at branches.branch_id. Those pointing columns are the joins' left-hand sides; the key 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: an author's name exists once, so it cannot disagree with itself.
- Space: the author's country 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, not three copies of the book.
- Integrity: a branch cannot be deleted while books still point at it, if the schema says so (lesson 48).
The cost is the one this module exists to pay: a query that needs 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
- Repeated values are a symptom: store the fact once and reference it by key.
- A join reproduces the wide view at read time, so the duplication never has to be stored.
- Join keys are matching column values — a primary key on one side, a foreign key on the other.
- Table aliases are mandatory as soon as two tables share a column name.
- Splitting data costs query complexity and buys correctness, space, speed and flexibility.
Design drill: take the flat books_flat table above and list the two ways it could end up holding contradictory information about one author — then describe the split version that makes each contradiction impossible.