Two email addresses for one customer
A small shop started with one table, order_lines: one row per item ordered, and each row carries everything needed to print it. The order's id and date, the customer's id and email, the product's SKU and name, the quantity, the price charged. For a while nothing goes wrong.
Every row mixes facts about a line, an order, a customer and a product.
Then SELECT DISTINCT customer_email FROM order_lines WHERE customer_id = 42 returns two rows. She changed her email last month, and the code that handled it updated only the lines of her open orders. Each row was correct when it was written. The table stores one fact, customer 42's email, once per line she has ever ordered, and an update that misses one copy leaves the table disagreeing with itself. That is an update anomaly.
What the table cannot hold
The catalogue team adds a coffee grinder, SKU G-310, that nobody has ordered yet, and there is nowhere to put it: every row needs an order_id and a line_no, so recording the grinder means inventing an order. That is an insertion anomaly. And when the only order for a discontinued teapot is cancelled and its lines are deleted, the teapot's name goes with them, because those lines were the only place it was written. That is a deletion anomaly.
All three have one cause: each row holds facts about four different things, and the facts about the order, the customer and the product are repeated on every line that mentions them, existing only as long as one of those lines does.
One fact, one place
Normalisation is the discipline of giving each fact one home. Its tool is the functional dependency: column B depends on column A when any two rows that agree on A must agree on B. customer_email depends on customer_id (the shop wants her current email, not the one she ordered with), which is exactly the rule the table broke. product_name, the catalogue's current name, depends on sku. qty depends on the whole key, (order_id, line_no), because it is a fact about one line. The usual summary of the goal is that every non-key column depends on the key, the whole key and nothing but the key.
Three normal forms, three repairs
First normal form (1NF) asks for a key and one value per column in each row. order_lines has both; a list such as skus: K-100,G-310 in one column would not, because no quantity or foreign key can attach to one item inside it.
Second normal form (2NF) says no non-key column may depend on only part of the key. placed_at, customer_id and customer_email depend on order_id alone, so a three-line order stores its date and email three times. Moving them to an orders table stores them once per order. Customer 42 is not fixed: she has 14 orders, and her email is still written 14 times.
Third normal form (3NF) removes that: no non-key column may depend on another non-key column. Both rules are stated here for a table with one candidate key, like orders; the formal definitions check every candidate key, each set of columns that identifies a row with no column to spare. In orders, customer_email depends on customer_id, which is not the key of orders, so it moves to a customers table and is written once. In order_lines, product_name depends on sku and moves to products. An email change is now one row, the grinder can exist before anyone orders it, and deleting an order leaves the teapot in the catalogue. price_at_purchase stays on the line: two lines for one SKU can carry different prices, bought before and after a price change.
Each fact has one home. order_lines keeps only what is true of one line.
3NF does not mean no value ever repeats. customer_id appears on every order by design: a foreign key is how one table points at another. Stricter normal forms exist for rarer kinds of dependency, but for most application schemas 3NF is where anomalies like these stop.
Putting a copy back on purpose
Every order total is now a sum over that order's lines. For one customer's 20 orders that is cheap: the primary key on (order_id, line_no) lets the database fetch one order's lines without reading the rest. Then the fraud team wants every order over €500 last quarter, sorted by total, and the database must sum the lines of every order in the quarter before it can filter or sort one.
Denormalisation stores a fact a second time, derived from the place that owns it, so a read does not have to derive it. Here, an orders.total column, indexable like any other. Check first that you need it: a better index or query fixes many slow joins, and Indexing & query optimisation shows how to read the plan.
The copy brings back the update anomaly, on purpose. Before order 1107 ships, the customer cuts one line from two kettles to one; the order-editing code updates order_lines, and the update to orders.total lives in a function this path never calls. The order page says €84, the sum of its lines says €45. A copy is only as correct as the code that maintains it, so every copy needs an owner you can name.
Who keeps the copy correct
- The same transaction. Whatever changes a line updates the total before it commits, so readers see both or neither. That holds only while every path that writes lines goes through that code; an admin script or a hand-written SQL fix bypasses it. Two transactions that change different lines of the same order and each write a recomputed sum can each miss the other's change. Lock the order row before touching its lines, or apply the difference (
total = total - 39.00); Transactions & isolation levels explains why the naive version can lose an update. A trigger moves this work into the database: it runs inside the same transaction and also catches writes from other code, but it has the same race if it recomputes the sum, and a bulk load with triggers disabled skips it. - An asynchronous job. The write commits, and a job or event consumer updates the copy afterwards. The copy is stale until the job runs, and a job that crashes, skips an event or applies two out of order leaves it wrong until something recomputes it. Fine for a dashboard count, not for a total a customer is charged; Materialized View covers read models kept this way.
Whichever owner you pick, the lines still own the fact, so the copy can be rebuilt, and a scheduled query that compares orders.total with the sum of its lines is cheap insurance. Adding the column to a live table also means backfilling every existing order, which Schema migrations without downtime covers.
The note says what the column copies, who keeps it correct, and how drift would be caught.
A copy, or a different fact?
On the day of the order, price_at_purchase usually holds the same number as products.price, but it answers a different question: what this line was charged. Ask of any repeated value: if the source changes, should this change too? For orders.total, yes, so it is a copy and needs an owner. For price_at_purchase, no: a price rise next month must not rewrite last month's invoices. It is a fact of its own, and replacing it with a join to products.price would be a bug, not a cleanup.
A shipping address works the same way. The order must keep saying where it was sent after the customer moves, so it keeps its own copy, or points to an address row nobody edits once an order uses it; editing that row in place rewrites history. A document that embeds related data faces the same question, and SQL vs NoSQL covers embedding versus referencing.
Write the note on E-Commerce Schema
E-Commerce Schema asks you to use a note to justify any denormalisation you keep. Start normalised, naming what each column depends on. If you keep a stored total, attach a Note that says what it copies, what keeps it correct, and what would make it wrong. Then run the copy-or-fact test on the price paid and the address. Keys go in entity names on a Challenge Grid, as Entity-relationship modelling explains.