Links between tables: JOIN and normalization

Normalization is spreading data across tables so that each fact is stored once. A JOIN rebuilds the whole by gluing rows whose keys match.

One big table, and what is wrong with it

Back to orders. The most obvious solution is one table with everything in each row: the order number, the customer’s name and phone, the service, the price, the category. See how it behaves, and compare it with the same data spread across tables.

SELECT items.title, categories.name
FROM items
JOIN categories
  ON items.category_id = categories.id;

A big table has three troubles, and each has a name.

• The update anomaly. A service appears in three orders, so its price is written in three rows. You change two and forget the third: now the database holds two prices for one service, and nobody knows which is right.
• The insert anomaly. There’s nowhere to add a new service that has no orders yet: every row of this table is an order.
• The delete anomaly. A customer canceled their only order, we deleted the row, and with it went their phone number and everything else we knew about them.

The cure is called normalization. The idea is one: every fact is stored exactly once, and every other place points to it by key. The price lives in the services table, the phone number in the customers table, and an order holds just a pair of numbers: “customer 3, service 2”.

Normal forms in human words

Normalization has strict rules, the normal forms. Here are the first three, without the math.

1. First (1NF): one value in a cell. Not “phone: 111, 222”, and not columns “phone1, phone2, phone3”. If there are several values, they are separate rows of a separate table.
2. Second (2NF): facts about a service (its name, its price) depend on the service, not on the order. So they live in the services table. The rule matters when a key has several columns: every other column must depend on the whole key, not on a part of it.
3. Third (3NF): a column must not depend on another non-key column. A category’s name depends on the category, not on the service, because a category can be renamed, and then the name would have to change beside every service. So it lives in the categories table.

You can remember all three with a saying attributed to the engineer Bill Kent: every fact depends on the key, the whole key and nothing but the key. There are more normal forms, but in practice three almost always do.

JOIN: putting it back together

Data that has been spread out has to be put back together. That’s what JOIN does. It takes the rows of two tables and glues the pairs whose keys match.

SELECT items.title, categories.name
FROM items
JOIN categories ON items.category_id = categories.id;

The part after ON is called the join condition: it says which column of one table equals which column of the other. Column names are written with the table name and a dot (items.title): when both tables have an id column, it would otherwise be unclear which one is meant. Switch between the kinds of JOIN on the first tab of the diagram and watch what stays in the answer.

• INNER JOIN (also written just JOIN) keeps only the matching pairs. A service with no category drops out of the answer: it found no match.
• LEFT JOIN keeps all the rows of the left table. Where there’s no match, the right side is NULL. After the ordinary join, this is the most useful kind: it answers the question “what is missing?”
• RIGHT JOIN is the same for the right table. It can almost always be replaced by a LEFT JOIN with the tables swapped, so it’s rarely written. There’s also FULL JOIN (keep both sides) and CROSS JOIN (every pair), but websites rarely need them.

A trick that is used all the time: how to find rows with no match. You need a LEFT JOIN and the condition WHERE right_table.id IS NULL:

SELECT items.title
FROM items
LEFT JOIN categories ON items.category_id = categories.id
WHERE categories.id IS NULL;

You can join as many tables as you like: each new one gets its own JOIN. A many-to-many relation (a person leads many services, a service has several leaders) is stored in a third table of pairs of keys, and then a query walks through two joins.

Two traps. The first: if a key repeats in one of the tables, the rows multiply: the JOIN returns more rows than the left table had. The second: a WHERE condition on a column of the right table after a LEFT JOIN throws away the rows where the right side is NULL, and the LEFT JOIN turns into an ordinary one. A condition on the right table of a LEFT JOIN is written in the ON, and only IS NULL stays in the WHERE.

When normalization is broken on purpose

Normalization isn’t a religion, it’s a way to avoid contradictions. Sometimes a copy of the data is justified:

• A historical fact. An order needs the price at the time of purchase. If the service’s price changes later, old orders must still show the old one. Here the price in the order isn’t a duplicate but a different fact: “what was paid”.
• Speed of reading. The number of inquiries shown on every page can be stored as a ready number instead of being counted by a query each time. The price: the number has to be updated with every new inquiry, or it drifts from the truth.
• A search copy. For search, a separate structure is built with the text glued together (lesson 6). That’s a copy too, built for reading.

The general rule: normalize first, and make a copy when you have measured that it’s slow without one and you know who keeps the copy up to date. A copy that nobody updates is worse than a slow query.

Practice

You’re in the era of 2010. CMS-based sites already spread their data across tables: posts apart, categories apart, authors apart, with the page put together by JOINs. Do the same for your own project.

The page has four boxes with SQL. Create the tables categories and people, and link items to them with foreign keys. Add the data from your brief, leaving one service without a category. Write a query with two JOINs, and a query with a LEFT JOIN that finds the service without a category. On the last line, say where a copy of the data is justified in your project.

What was wrong with 2010

Many CMSs of that time kept “everything else” in one table of the form “owner, field name, value”. WordPress’s extra fields for posts are built like that, for example. It’s convenient: a new field is added without changing the schema. But the price is paid in joins: to assemble one page with a dozen fields, the table has to be joined to itself a dozen times, and on big sites that is slow. A flexible schema and fast queries pull in different directions, and each CMS chooses in its own way. We’ll come back to this in part 8.