Normalization: 1NF, 2NF, 3NF
One messy table, split one rule at a time. Each split removes a specific kind of redundancy — and a specific kind of bug.
Stage
The raw table: one row per order, a comma-separated list of products.
The Table(s) At This Stage
—Redundancy
Normalization: A Practical Guide
Splitting a table so each fact is stored exactly once.
Quick Context
A denormalized table stores the same fact in more than one place, which means updating it can leave two copies disagreeing. Normalization is a sequence of rules, each one removing a specific kind of redundancy by moving data into its own table.
The three rules
- 1NF — atomic values. No column holds a list. "Pen, Notebook" in one cell becomes two rows.
- 2NF — no partial dependency. With a composite key, every other column must depend on the whole key, not just part of it. Customer name depends only on order_id, not on (order_id, product) — so it moves out.
- 3NF — no transitive dependency. A column cannot depend on another non-key column. Customer city depends on customer, not directly on the order — so it moves to its own table.
Interactive Exploration Guide
- Start at raw. One table, one row per order, a multi-valued products column — not even 1NF.
- Normalize to 1NF. Products become atomic — one row per (order, product) — but now customer, city and price all repeat across the exploded rows.
- Normalize to 2NF. Orders and Products split out — price no longer repeats per line item. Redundant cells fall from 8 to 2.
- Normalize to 3NF. Customers splits out from Orders — city no longer repeats per order for the same customer. Redundant cells hit 0.
- Read what is left. order_id and customer still repeat across tables — that is a normal foreign key, not redundancy. Redundancy is a *fact* stored twice; a key linking two facts is the whole point of splitting them apart.
What usually goes wrong
Over-normalizing has a cost too: every extra table is another join at query time. Reporting and analytics workloads often deliberately denormalize back down for read speed, accepting the redundancy because the data is written once and read constantly. Normalize for correctness where writes happen; consider denormalizing where reads dominate.
Key Takeaway
1NF removes multi-valued columns, 2NF removes columns that depend on only part of a composite key, and 3NF removes columns that depend on another non-key column rather than the key itself. Each step moves a fact into the one table where it is defined once, which is what makes update anomalies structurally impossible rather than merely unlikely.