Normalization is the process of organizing tables so that every fact is stored exactly once. The payoff isn’t tidiness for its own sake — it’s that a fact stored once can’t disagree with itself. When a customer’s city lives in one place, updating it is one write. When it’s copied across six order rows, you get to find out what happens when you update five of them.
The normal forms are usually taught as definitions to memorize. It’s much easier to watch the mechanic. Step through the stages below — each one names the specific problem it’s fixing, highlights the columns that move, and counts how many rows physically carry a customer’s city:
Notice the counter goes 3 → 6 → 3 → 2. Getting to 1NF briefly makes duplication worse — that’s not a mistake, it’s the point: 1NF exposes redundancy that a comma-separated list was hiding, and 2NF and 3NF are what clear it up.
First Normal Form (1NF): one value per cell
A table is in 1NF when every cell holds a single, atomic value and there are no repeating groups.
The starting table fails because products contains 'Mouse, Keyboard'. That one decision poisons everything downstream: you can’t join products, can’t count them, can’t filter by one without LIKE '%Mouse%' (which happily also matches “Mouse Pad”). Splitting the list into one row per product fixes it — and makes the real key visible: it takes both order_id and product to identify a line.
Second Normal Form (2NF): no partial dependencies
A table is in 2NF when it’s in 1NF and every non-key column depends on the whole key, not just part of it.
Partial dependencies are only possible against a composite key, so a table whose candidate keys are all single columns is in 2NF automatically. One precise caveat, because it trips people up: the normal forms are defined against every candidate key, not just the one you happened to name the primary key. Bolting a surrogate id onto a table doesn’t normalize it — if the natural composite key still identifies rows, the partial dependency is still there, just hidden.
In the 1NF table the key is (order_id, product), but customer and customer_city depend on order_id alone. That’s a partial dependency, and it’s why Ava’s name and city repeat on every line of her order. The fix is to split the table: order-level facts go with the order, line-level facts stay with the line.
Third Normal Form (3NF): no transitive dependencies
A table is in 3NF when it’s in 2NF and no non-prime column (one that isn’t part of any candidate key) depends on another non-key column.
Look at the 2NF orders table: customer_city doesn’t really depend on order_id — it depends on customer, which depends on order_id. That indirection is a transitive dependency, and it means “Ava lives in Austin” is still stored once per order. Pulling customers into their own table stores it once, full stop.
A useful informal summary: every non-key column must depend on the key, the whole key, and nothing but the key.
Beyond 3NF there’s BCNF (Boyce–Codd), which requires that every determinant be a candidate key — strictly stronger than 3NF, which tolerates a non-superkey determinant as long as what it determines is part of a key. BCNF matters when candidate keys overlap, which is uncommon in practice. It’s also not free: a BCNF decomposition can lose dependency preservation, pushing a constraint the schema used to enforce out into application code, whereas 3NF always admits a lossless, dependency-preserving decomposition. That trade is why most designs stop at 3NF. (4NF and 5NF exist above BCNF, dealing with multivalued and join dependencies.)
Functional dependencies, in one line
All of this rests on one idea. A functional dependency A → B means “if you know A, you know exactly one B.” customer → city (each customer has one city). order_id → customer. Normalization is just the discipline of making sure each dependency is recorded in the table whose key is on the left-hand side.
When to denormalize on purpose
Normalization optimizes for correct writes. Analytics optimizes for fast reads — and those pull in opposite directions, because a fully normalized model makes you join to answer anything.
First, a caution: reflex denormalization is one of the more expensive mistakes in database work. A join on an indexed key is cheap, and the index is maintained for you — whereas a duplicated column is maintained by whoever remembers. Reach for denormalization when you’ve measured a real problem, not preemptively.
With that said, it’s a legitimate, deliberate choice:
- Warehouses and star schemas flatten dimensions on purpose so a dashboard reads one wide table instead of joining eight.
- Read-heavy paths may keep a duplicated column to skip a join in a hot query.
- Reporting tables are frequently pre-joined snapshots.
The rule of thumb: normalize the system of record; denormalize the copies you read from. The write side stays correct, the read side stays fast, and the pipeline in between is responsible for keeping the copy in sync. If a column is duplicated and nothing owns keeping it current, that’s not denormalization — that’s a bug waiting to happen.
Frequently asked questions
What are 1NF, 2NF, and 3NF? 1NF: every cell holds a single atomic value, no repeating groups. 2NF: 1NF plus no non-key column depends on only part of a composite key. 3NF: 2NF plus no non-prime column depends on another non-key column.
Why normalize a database? To prevent anomalies — update (changing a value in some copies but not others), insert, and delete. Saving storage is an incidental side effect, not the goal.
Does 2NF matter if my table has a single-column primary key? Partial dependencies require a composite key, so if all the candidate keys are single columns the table is in 2NF automatically. Careful, though: it’s about candidate keys, not just the primary key you picked — adding a surrogate id on top of a real composite key doesn’t remove the dependency.
What’s the difference between 3NF and BCNF? 3NF forbids non-key columns depending on other non-key columns. BCNF goes further: every determinant must be a candidate key. They differ only when candidate keys overlap.
Is denormalization bad? Not when it’s deliberate. Analytical models denormalize on purpose for read speed. The danger is accidental duplication with no process keeping the copies consistent.
Does normalization make queries slower? It can add joins, which is why analytical copies are often denormalized. Well-chosen indexes on the join keys keep normalized models fast for transactional work.
Where the two shapes meet
Most organizations need both: a normalized system of record and denormalized copies for analytics. Keeping those in sync — reliably, on a schedule — is exactly what ET1 is for. Join, aggregate, and flatten as nodes on a visual canvas with a live preview at each step, so the denormalized table your dashboard reads is a derived artifact with an owner, not a copy someone made once and forgot.
Keep learning: CREATE TABLE to define the shape, joins to put it back together, indexes to keep it fast, and views to hide the joins.