AI Hints
Homeโ€บSQL Tutorialโ€บDesigning Databases
SQL ยท Lesson 78 of 126

Designing Good Tables

Normalization made simple.

โฑ 2 min readโ–ถ Video at 1:49:27๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Designing Good Tables
From the video: Designing Good Tables
  • 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.

Repeated datasame email, many rows
Update problemschange it everywhere
Delete problemslose the customer
Insert problemsno book until ordered

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.

Rule 1one value per cell
Rule 2one thing per table
Rule 3each fact stored once
Link themwith IDs

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.

query.sqlsql
SELECT author, COUNT(*) AS books
FROM books
GROUP BY author
ORDER BY books DESC
LIMIT 4;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Design Practice: Event Bookings โ€” two tables, one link