Chapter 06 · intermediate · 8 min
Normalization II: 3NF, and when to denormalize
Last chapter ended with a customer's city copied onto every one of their orders, and an update that only fixed one copy. This chapter fixes that, finds the same shape of problem one level deeper (3NF), and then makes the case that normalization is a default, not a religion — reporting tables are allowed to denormalize on purpose.
The dataset
Three pairs/sets of tables: customers_2nf/orders_2nf (the 2NF fix), customers_3nf_violation (city and state both stored per-customer, free-text, and drifting) versus cities/customers_3nf (state derived from a single lookup).
Schema
| id | INT |
| name | TEXT |
| city | TEXT |
| order_id | INT |
| customer_id | INT |
| product | TEXT |
| amount | NUMERIC(10,2) |
| id | INT |
| name | TEXT |
| city | TEXT |
| state | TEXT |
| city | TEXT |
| state | TEXT |
| id | INT |
| name | TEXT |
| city | TEXT |
Example data
Split the repeating fact into its own table
customers_2nf holds each customer's name and city exactly once; orders_2nf holds only what actually varies per order. Aisyah's city now exists in exactly one row, no matter how many orders she places.
Before you run it — Aisyah has two orders here. Is her city stored on either of these rows at all?
The city is still there when you need it — via a join
Splitting the table doesn't lose the information; it just means reading it back is a JOIN instead of a free column. Update Aisyah's city once in customers_2nf and every order she's ever placed sees the new value immediately, because there's only one row to update.
Before you run it — this joins city back from a single source. Could this result ever show two different cities for the same customer, the way the old table could?
3NF: a dependency can hide one level deeper
customers_3nf_violation stores both city and state directly on the customer. That looks fine — until you notice state doesn't actually depend on the customer, it depends on the city. Store it per-customer anyway, in free text, and nothing stops the same city from getting two different spellings of its state.
Before you run it — Aisyah and Bala are both in Johor Bahru. Do you expect exactly one state value for that city, or could it show up twice, spelled differently?
The fix: give the dependency its own table
cities holds each city's state exactly once; customers_3nf.city is a foreign key into it. Now a city's state can only ever be looked up one way — there's no second free-text copy to drift.
Before you run it — Aisyah and Bala are both in Johor Bahru again. Now that state comes from one shared lookup row, do their state values agree?
Normalize your tables; denormalize your reports, on purpose
None of this means a flat, repeated-column report is always wrong — it means it shouldn't be how the source data is stored. A JOIN at read time gets you the exact same flat shape the old orders_flat table had, on demand, without ever risking two contradicting copies of the truth. Store normalized, and denormalize deliberately — a report query, a materialized view — only where something reads it.
Before you run it — this looks exactly like the old orders_flat table's shape. Is the underlying city data duplicated the way it was there?
If you've used Excel or Google Sheets
A normalized schema is several sheets linked by VLOOKUP — one "customers" sheet, one "orders" sheet, each fact owned by exactly one of them. A denormalized report is the flattened export you'd generate from those sheets for a chart or a stakeholder — regenerated whenever the source changes, never hand-edited itself. The mistake is treating that export as the place new data gets typed in.
Normalization is about where a fact lives. The next chapter is about something orthogonal: once your tables are shaped well, how the database finds a row fast — and what an index actually costs you for that speed.