Normalization
A small online store starts with one table, because one table is easy. Every row is an order line, and each row also carries the customer's name, phone and address, and the product's name and price. It works for a year. Then a customer changes their phone number. Support updates it on the three rows they can find, misses the other forty, and the next delivery rider calls a number that has been disconnected for a month.
Normalization is the discipline of storing each fact exactly once, so that it can only ever be wrong in one place. It sounds academic, it comes up in nearly every database interview, and the core idea fits in one sentence.
Look at the table described above. Name one thing that goes wrong when a customer changes their phone number, and one thing that goes wrong when a product has never been ordered.
The table that causes trouble#
| order_id | int | PK | Part of the key, together with product_id. |
| product_id | int | PK | Part of the key. |
| customer_name, customer_phone | text | Repeated on every line of every order the customer ever placed. | |
| customer_city | text | Repeated too. | |
| product_name, product_price | text, numeric | Repeated on every line that sold this product. | |
| quantity | int | The only column that is really about this order line. |
Three kinds of damage, each with a name.
Update anomaly. Asha's phone is stored on 43 rows. Change it on 42 and the data now disagrees with itself, and nothing tells you which copy is right.
Insert anomaly. You cannot add a new product until someone orders it, because a row needs an order id. The catalogue literally cannot exist on its own.
Delete anomaly. Delete a customer's only order and you have deleted the customer too, along with the only record of what that product cost.
The fix: one fact, one place#
Split the table so that each fact lives in exactly one row, and the tables point at each other with keys.
| customers (id, name, phone, city) | table | PK | One row per customer. The phone number exists once and is changed once. |
| products (id, name, price) | table | PK | The catalogue exists on its own, ordered or not. |
| orders (id, customer_id, placed_at) | table | PKFK | Who ordered and when. customer_id points at customers. |
| order_lines (order_id, product_id, quantity, unit_price) | table | PKFK | What was in each order. unit_price is copied deliberately, see below. |
Notice unit_price on the order line. That looks like the duplication we just removed, but it
is a different fact: not "what does this product cost" but "what did this customer pay on that
day". If the price changes next month, old orders must not change. Knowing which copies are
genuinely different facts is most of the skill.
The normal forms, in plain words#
The textbook names are a ladder. Each step removes one kind of duplication.
First normal form (1NF). Each cell holds one value, and there are no repeating groups.
A phones column holding "98450 12345, 99001 22334" breaks it, and so do columns item1,
item2, item3. Put many values in many rows.
Second normal form (2NF). 1NF, and every non-key column depends on the whole key.
In the table above, the key is (order_id, product_id), but product_name depends only on
product_id. That partial dependency is what 2NF removes.
Third normal form (3NF). 2NF, and non-key columns depend on nothing but the key.
If orders stored both pincode and city, then city depends on pincode, not on the order.
That chain, key to pincode to city, is a transitive dependency, and 3NF moves it out.
Boyce-Codd normal form (BCNF). A slightly stricter 3NF: anything that determines another column must itself be a key. It matters in unusual tables with overlapping keys and is rarely the focus of an interview beyond naming it.
The classic way to remember 2NF and 3NF together: every column depends on the key, the whole key, and nothing but the key.
When to break the rules on purpose#
Normalization optimises for correct writes. Some systems mostly read, and for them it is reasonable to store a fact twice and accept the job of keeping the copies in step. This is denormalization, and it should always be a decision with a reason, never an accident.
Common, defensible cases: a comment_count on a post so the feed does not count rows on
every view; a reporting table rebuilt nightly in a wide shape analysts can query without
joins; a materialized view that precomputes an expensive join. And document databases like
MongoDB or DynamoDB lean towards storing data
in the shape it is read, which is denormalization by design.
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Normalized (3NF) | Each fact stored once, so updates are simple and the data cannot contradict itself. | Reads need joins, and very hot read paths can get expensive. | The default for transactional data: orders, payments, users. |
| Denormalized copies | Reads become single lookups with no joins. | Every write must update every copy, and a missed one is a silent bug. | Read heavy paths, counters, reporting tables, with a clear owner for keeping copies in step. |
Every denormalized value needs one place in the code that keeps it correct, such as a trigger, a single service, or a scheduled rebuild. "We update it wherever we change the source" works until the first developer who does not know about it ships a feature.
Checkpoint#
1. You cannot add a new product until it has been ordered at least once. What is this called?
2. order_lines has key (order_id, product_id) and a product_name column. Which normal form does that break?
3. Why is copying unit_price onto order_lines not a normalization mistake?
- One fact, one place. Duplicated facts drift apart.
- The key, the whole key, and nothing but the key: 2NF and 3NF in one line.
- Three anomalies: update, insert, delete.
- Denormalize on purpose, with an owner for keeping copies in step.
Normalization means storing each fact once so it cannot contradict itself. A wide table that repeats customer and product details causes update anomalies when a copy is missed, insert anomalies when something cannot exist without something else, and delete anomalies when removing one thing removes another. First normal form means one value per cell, second means every column depends on the whole key, third means it depends on nothing but the key, and BCNF tightens that further. I would normalize transactional data to third normal form by default, and denormalize deliberately for read heavy paths, like counters or reporting tables, with one clear owner keeping the copies in step.