Chapter 05 · intermediate · 8 min
Normalization I: fixing repeating groups
Normalization is the discipline of shaping tables so each fact is stored exactly once. It sounds academic until you hit the bug it prevents: update one copy of a fact and forget the others, and your data quietly starts contradicting itself. This chapter covers the first two rules — 1NF and 2NF — through two tables shaped the wrong way.
The dataset
Three tables. orders_wide breaks 1NF: it has product_1/product_2/product_3 columns instead of one row per product. orders_flat fixes that but breaks 2NF: customer_name and customer_city repeat on every order row for the same customer. orders_flat_after_edit is the same table one update later — someone corrected Aisyah's city on order 201 only, exactly the mistake the last section walks through.
Schema
| order_id | INT |
| customer_name | TEXT |
| product_1 | TEXT |
| product_2 | TEXT |
| product_3 | TEXT |
| order_id | INT |
| customer_id | INT |
| customer_name | TEXT |
| customer_city | TEXT |
| product | TEXT |
| amount | NUMERIC(10,2) |
| order_id | INT |
| customer_id | INT |
| customer_name | TEXT |
| customer_city | TEXT |
| product | TEXT |
| amount | NUMERIC(10,2) |
Example data
Repeating groups: a list encoded as columns
orders_wide gives every order three product slots. This breaks 1NF (first normal form): a cell should hold one value, not an implicit list spread across product_1/product_2/product_3.
Before you run it — Chong bought 3 products, Bala bought 1. Can you tell that from a single column, or do you have to check three?
Every query has to know the shape in advance
Want a flat list of every product bought, by whom? You can't just read one column — you have to union all three, and if a fourth product slot ever gets added, every one of these queries needs editing too.
Before you run it — 3 orders bought 1, 2 and 3 products respectively. How many total rows should this produce?
1NF: one row per fact
orders_flat fixes the repeating group — one row per (order, product) instead of a growing set of columns. Adding a fourth product to an order is now just another row, not a schema change.
Before you run it — Aisyah's single order (201/202) bought two products. How many rows represent that, now that it's 1NF?
But look what got copied along the way
customer_name and customer_city don't actually depend on the order or the product — they depend only on customer_id. Since they're stored on every order row anyway, Aisyah's name and city are written down twice, identically, once per order.
orders_flat
| order_id | customer_id | customer_name | customer_city | product | amount |
|---|---|---|---|---|---|
| 201 | 1 | Aisyah | Kuala Lumpur | Rice | 18.90 |
| 202 | 1 | Aisyah | Kuala Lumpur | Oil | 9.50 |
| 203 | 2 | Bala | Penang | Noodles | 2.50 |
customers
| customer_id | customer_name | customer_city |
|---|---|---|
| 1 | Aisyah | Kuala Lumpur |
| 2 | Bala | Penang |
orders
| order_id | customer_id | product | amount |
|---|---|---|---|
| 201 | 1 | Rice | 18.90 |
| 202 | 1 | Oil | 9.50 |
| 203 | 2 | Noodles | 2.50 |
the tinted cells repeat on every row of orders_flat — split out once, linked back by customer_id
Before you run it — how many DISTINCT customers are there, versus how many total rows does orders_flat have?
A partial dependency makes updates dangerous
This is called a partial dependency: customer_city depends on part of what identifies a row (customer_id) but not the whole thing. The danger shows up the moment Aisyah moves: orders_flat_after_edit is the same table one update later, where only order 201 got corrected to her new city and order 202 was missed.
Before you run it — one of Aisyah's two order rows was corrected, the other wasn't. Does this show one city on record for her, or two contradicting ones?
If you've used Excel or Google Sheets
Pasting a customer's name and city into every row of an orders sheet — instead of keeping one customer sheet and looking values up with VLOOKUP — is exactly this bug. Update the customer's city in one pasted cell and every other pasted copy is now wrong, with nothing warning you they've drifted apart. A separate customers table (2NF's fix, next chapter) is the database version of "keep one source of truth and look it up".
The fix for a partial dependency is to split the repeating fact into its own table. That's 2NF's actual solution, and the subject of the next chapter.