Data Engineer · #060 · September 29, 2026 · 2 min read
What are 1NF, 2NF and 3NF? Normalization with the memory trick
The anomalies normalization kills, the three normal forms with the key-whole-key-nothing-but trick, the smells of a leaky schema, and when denormalizing is the right call.
Get the free PDF
One page, print-ready, free to share. No signup needed.
One giant table is not a database. The same customer name copied onto 40,000 order rows means one typo creates two customers, and one rename is 40,000 updates. One page on the three normal forms and the smells that call for them. The print-ready A4 PDF is at the bottom.
The idea
- Each fact lives once, so updates touch one place.
- The failure modes have names: update, insert and delete anomalies.
- Normalize = split into tables linked by keys.
1NF: atomic
- One value per cell: no
"red, blue"lists. - No repeating columns: phone1, phone2, phone3 are rows in another table.
- Every row reachable by a real key.
2NF: the whole key
- Applies to composite keys: no column may depend on part of the key.
- On
(order_id, product_id),product_namedepends onproduct_idalone. It moves to products.
3NF: nothing but the key
- No non-key column may explain another: if
zipdeterminescity, city does not belong on orders. - The memory trick: the key, the whole key, and nothing but the key.
The split, in one before/after
-- before: one fact, 40,000 copies
-- orders(id, customer_name, customer_city, ...)
-- after: the fact lives once
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL, city TEXT);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT REFERENCES customers(id));
A rename is now UPDATE one row, not UPDATE 40,000 where you hope the spelling matched.
The trap: which anomaly bit you
| Anomaly | The tell | The cure |
|---|---|---|
| update | same edit, many rows | store the fact once |
| insert | cannot add a customer with no order | own table |
| delete | last order gone, customer gone | own table |
Denormalize on purpose
- OLTP stays near 3NF: writes must be safe.
- Star schemas flatten dimensions on purpose: reads dominate, joins are the cost.
- The interview follow-up is always which side you are on. Know it.
Interview phrasing worth memorizing: normalize for writes, denormalize for reads, and never do either by accident.
Frequently asked questions
What is the point of database normalization?
Each fact lives exactly once, so every update touches one place. Denormalized tables suffer three anomalies: update (the same edit must hit many rows and typos fork the data), insert (you cannot add a customer who has no order yet), and delete (removing the last order silently removes the customer). Normalization splits the data into tables linked by keys so each fact has one home.
What is the difference between 1NF, 2NF and 3NF in plain terms?
1NF: atomic cells (no comma-separated lists, no phone1/phone2/phone3 columns) and a real key. 2NF: with a composite key, no column may depend on just part of the key (product_name does not belong on a (order_id, product_id) table). 3NF: no non-key column may depend on another non-key column (if zip determines city, city does not belong on orders). The memory trick: every column depends on the key, the whole key, and nothing but the key.
How do you know a schema needs normalizing?
The smells: the same string repeated on thousands of rows (one typo creates two customers), an update that must touch dozens of rows to change one fact, a delete that loses unrelated data, and columns named v1/v2/v3 or phone1/phone2. Each smell maps to an anomaly that a split table would kill.
Is denormalization ever correct?
Yes, on purpose and on the read side. OLTP systems stay near 3NF because writes must be safe. Analytical warehouses denormalize deliberately: a star schema keeps dimensions flat and repeats values because reads dominate and joins are the cost. Knowing which side of that line you are on is the actual interview answer.
Get the free PDF
One page, print-ready, free to share. No signup needed.