What does normalization actually buy you?
It means that every fact about your data is written down in exactly one place, so that the data cannot contradict itself.
-- Unnormalised: the customer city is repeated on every order row.
CREATE TABLE orders_flat (
id INTEGER PRIMARY KEY,
customer_name VARCHAR(120) NOT NULL,
customer_city VARCHAR(80) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders_flat (id, customer_name, customer_city, amount) VALUES
(1, 'Ada Lovelace', 'London', 250.00),
(2, 'Ada Lovelace', 'London', 120.00),
(3, 'Grace Hopper', 'New York', 900.00);
So what does that actually buy you? Three things, and they are all about what happens when the data changes.
The first one is that there is no update problem. If Ada moves to Paris, you change one row in the customers table. In the flat table above you would have to find every one of her orders and change the city on each of them, and if you miss one then the data says she lives in two places.
The second one is that there is no insertion problem. In the flat table you cannot record a customer until they have placed an order, because there is nowhere to put their name on its own. That is why the flat table above has no row for a customer who has not ordered yet.
The third one is that there is no deletion problem. If you delete the last order of a customer in the flat table, you lose their name and their city as well, because those were only stored on the order.
So the rule to remember is this. A fact belongs in one place. The customer's city belongs in the customers table, and every place that wants to show it joins to that table.
The next two questions in this topic look at when the rules go the other way, and at the normalised version.