Designing Good Tables
Normalization made simple.
You've used Helen's database for hours. It works well, because it was designed well. But imagine a beginner's version. One giant spreadsheet, with every order, the customer's name, their email, the book title, and the price, all on one row. It seems easy at first. Then it falls apart.

- One giant spreadsheet?
- Easy at first
- Then it falls apart
What goes wrong
In one giant table, the same customer's email appears on every one of their orders. If they change it, you must update dozens of rows. Miss one, and your data disagrees with itself. Deleting a customer's only order deletes the customer too. And you can't add a new book until someone orders it.
The three simple rules
Normalization is the name for fixing this. You don't need the theory. Just three simple rules. One, each cell holds one value, not a list. Two, each table is about one kind of thing. Customers, books, orders. Three, every fact is stored once, and other tables point to it with an ID.
Spotting repeated data
Helen's books table still has one small repeat. The author's name is typed on every book. Run it. Ada Obi appears four times. If she changed her pen name, we'd update four rows.
SELECT author, COUNT(*) AS books
FROM books
GROUP BY author
ORDER BY books DESC
LIMIT 4;โโโโโโโโโโโโโโฌโโโโโโโโ โ author โ books โ โโโโโโโโโโโโโโผโโโโโโโโค โ Ada Obi โ 4 โ โ Tom Avery โ 3 โ โ Sofia Ruiz โ 3 โ โ Raj Mehta โ 3 โ โโโโโโโโโโโโโโดโโโโโโโโ
Splitting it out
So we could move authors into their own table. Create the table, then fill it from the distinct authors in books. Insert into, followed by a select, copies the results straight in. Run it. Ten authors, each stored once. Books would then keep an author ID instead of the name.
CREATE TABLE authors (
author_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
INSERT INTO authors (name)
SELECT DISTINCT author
FROM books
ORDER BY author;
SELECT * FROM authors LIMIT 4;โโโโโโโโโโโโโฌโโโโโโโโโโโโ โ author_id โ name โ โโโโโโโโโโโโโผโโโโโโโโโโโโค โ 1 โ Ada Obi โ โ 2 โ Ben Ortiz โ โ 3 โ Jo Park โ โ 4 โ Kai Lund โ โโโโโโโโโโโโโดโโโโโโโโโโโโ
Summary
One value per cell, one thing per table, each fact stored once. That's good design in three rules. Next, design a small database yourself.
Key takeaways- One value per cell
- One thing per table
- Each fact once, linked by IDs