Data Gets Out of Sync

An online store's reporting team exported every order line into one wide table so analysts never need a second table. Each row is 1 product on 1 order, with customer and product details copied beside it.

order_id	order_date	customer_id	customer_name	customer_email	product_id	product_name	unit_price	qty
1001	2024-03-02	42	Priya Nair	priya@nair.io	P7	Desk Lamp	34.00	1
1001	2024-03-02	42	Priya Nair	priya@nair.io	P2	USB Cable	9.00	3
1002	2024-03-05	17	Tom Ola	tom@ola.dev	P7	Desk Lamp	34.00	2
1003	2024-03-06	42	Priya Nair	priya@nair.io	P9	Felt Pad	4.50	10
1004	2024-03-09	42	Priya Nair	priya@nair.io	P2	USB Cable	9.00	1

Every value on every row is true, yet customer 42's email is written 4 times, the Desk Lamp's price twice, the USB Cable's name twice. The disk this costs is cheap. The expensive part is that 4 copies of 1 fact can hold 4 different values, and nothing in the table prevents it.

The update that lands halfway

Customer 42 changes their email to priya@nair.dev. The engineer updates the rows for order 1001, the order number on the ticket, and closes it. Rows 1 and 2 now carry the new address and rows 4 and 5 the old one, with no error raised. Where customer 42's receipts go now depends on which row a report reads first. This query finds the damage.

SELECT
  customer_id,
  COUNT(DISTINCT customer_email) AS email_versions
FROM order_report
GROUP BY
  customer_id
HAVING COUNT(DISTINCT customer_email) > 1

customer_id	email_versions
42	2

This is an update anomaly. The number of places a fact is stored is the number of ways an update to it can fail.

Facts with nowhere to live

The store adds P12, a Monitor Arm at 59.00, which no order contains yet. Here a product exists only as part of an order line, so recording P12 means inventing an order line: an empty order_id, an empty customer, a quantity of 0, and a fake row every report must filter out. A customer who registers before buying hits the same wall. This is an insert anomaly: the table cannot hold a fact around 1 thing without asserting a fact about another.

Deleting one thing erases another

Order 1003 is cancelled and its row deleted. It was the only row mentioning P9, so the Felt Pad's name and price leave with it: removing an order removed a product. This is a delete anomaly.

All 3 anomalies share a cause. A fact is a single statement about a single thing: customer 42's email is priya@nair.io, product P7 costs 34.00. This table puts customer, product and order facts on 1 row, so each customer fact is copied once per order line. Normalization splits a table until each fact is stored once, in a table about the thing it describes, so changing a fact means changing 1 row.

A key is the column, or the set of columns, whose value picks out exactly 1 row. Neither order_id nor product_id does that alone here, since order 1001 has 2 rows and P7 appears on 2 orders; the pair (order_id, product_id) does. Each normal form is a rule about how the columns outside the key relate to the key.

An employees table has 1 row per employee: employee_id, name, dept_name, dept_floor. The Finance department moves from floor 3 to floor 5. What happens to the table?

The insert anomaly applies: the new floor cannot be recorded until Finance hires at least 1 new employee.
Every Finance employee's row must change, and any row the update misses leaves Finance on 2 floors at once.
Nothing breaks, because dept_floor is a fact about the employee and each row holds its own correct copy.
The delete anomaly fires: the move deletes the old Finance rows and recreates them on floor 5.

About this lesson

This section is part of the Normalization lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.