The spreadsheet that became a table
Every project has one: a wide table where each row repeats the customer, their city, the product and its price. It is easy to write and easy to read, and it goes wrong in three specific ways that have names.
bookings_raw
customer customer_city door door_price qty day
Arclith Interiors Baku Vesper 890 2 2024-03-01
Arclith Interiors Baku Calastra 1650 1 2024-03-01
Orsenne Ltd London Orsenne 1200.5 3 2024-03-12
Orsenne Ltd Manchester Caivor 1980 1 2024-04-02 ← which city is right?The three anomalies
Normalisation is the process of splitting a table until each fact lives in one row of one table. The anomalies then become impossible rather than unlikely.
| Anomaly | What happens | In this table |
|---|---|---|
| Update | a fact is stored many times, and one copy is missed | Orsenne moved, and only the newer row says so |
| Insert | you cannot record one fact without another | a new door cannot exist until somebody books it |
| Delete | removing one fact destroys another | delete the Berlin booking and Silica Design is gone |
The forms, in the only version anyone needs
First normal form: one value per cell. No "oak, walnut" in a text column, no phone1, phone2, phone3. A repeating group is a table.
Second: every column depends on the whole key. In a table keyed by (order, door), the door's catalogue price does not depend on the order — so it belongs in doors.
Third: no column depends on another non-key column. customer_city depends on customer, not on the booking — so it belongs in customers.
That is the whole of it for practical purposes: every non-key column describes the key, the whole key, and nothing but the key. The higher forms exist and almost never change a design.
The copies you keep on purpose
order_items.unit_price is a duplicate of doors.price — and it is correct. The catalogue price is what a door costs today; the line price is what this customer agreed then. They are two different facts that happen to be equal at the moment of sale, and an invoice that changes when the price list changes is a bug in every jurisdiction.
A cached orders.total, a stored full_name, a counter of comments: all fine when the read pattern demands it and something keeps them true — a trigger, a job, or a single code path. The failure is not the copy; it is the copy nobody owns.
Splitting the table, in SQL
Then a booking keeps person_id instead of the name and the city, and the old columns are dropped. Real migrations run this in a transaction, with the old and new columns side by side for a release, so a rollback is possible.
create table people (
id integer primary key,
name text not null unique,
city text
);
insert into people (name, city)
select customer,
(select customer_city from bookings_raw b2
where b2.customer = b.customer
order by day desc limit 1) -- the most recent spelling wins
from bookings_raw b
group by customer;