Normalization is the discipline of organising tables so that every fact is stored in exactly one place, which makes it impossible for the database to contradict itself. Denormalization is the deliberate, measured reversal of that discipline to make reads cheaper. Interviewers probe both together because the real question is not "recite the normal forms" but "can you tell me what breaks when a fact is stored twice, and what you are buying when you accept that break on purpose?" For a Backend or AI engineer this decision appears every time you add a cached total, a copied name, a search document, or an embeddings table that mirrors a source row: each is a denormalized copy with a synchronization bill attached.
orders_flat (PK = order_id + product_id)
+----------+-------------+-------+---------------+-----------+----------+
| order_id | customer_id | name | customer_city | product_id| unit_price|
+----------+-------------+-------+---------------+-----------+----------+
| 1 | 10 | Asha | Pune | 100 | 50 |
| 1 | 10 | Asha | Pune | 101 | 20 |
| 2 | 10 | Asha | Pune | 101 | 20 |
| 3 | 11 | Ravi | Delhi | 100 | 50 |
+----------+-------------+-------+---------------+-----------+----------+
Asha's city is stored 3 times. Every copy must be updated together.
UPDATE anomaly : change city in one row -> two contradictory cities for Asha
DELETE anomaly : delete Ravi's only order -> Ravi (and his city) vanish
INSERT anomaly : cannot record a new customer before their first order
(order_id is part of the PK, so it cannot be NULL)Run against SQLite, this is what actually happens when one row of Asha's is updated and Ravi's order is deleted. The exact same behaviour occurs in PostgreSQL, MySQL and SQL Server, because nothing in the schema tells the engine that three rows are supposed to agree:
-- after UPDATE ... SET customer_city='Mumbai' WHERE order_id=1 AND product_id=100
customer_id cities n
----------- ----------- -
10 Mumbai,Pune 2
11 Delhi 1
-- after DELETE FROM orders_flat WHERE order_id = 3
rows_for_ravi
-------------
0The root cause is a functional dependency the schema does not know about: customer_id → customer_city holds in the real world, but the table's key is (order_id, product_id), so the engine cannot enforce it. Normalization is the process of moving each functional dependency into a table where its left-hand side is a key. Once customer_id is the primary key of customers, the city exists in one row and there is nothing left to disagree with.
Functional dependency X → Y : if two rows agree on X they must agree on Y.
1NF Each cell holds one atomic value; no repeating groups; rows are identifiable.
Bad : phones = '98xx, 99xx' Good: one row per phone in user_phones
2NF 1NF + no PARTIAL dependency: no non-key column depends on only PART of a
composite key.
key (order_id, product_id): product_id → product_name (partial!)
order_id → customer_id (partial!)
Fix: move product_name to products, customer_id to orders.
3NF 2NF + no TRANSITIVE dependency: non-key column must not depend on another
non-key column.
order_id → customer_id → customer_city (city depends on customer_id)
Fix: move customer_city to customers.
BCNF Stricter 3NF: for EVERY non-trivial X → Y, X must be a superkey.
(3NF lets Y escape if Y is part of some candidate key; BCNF does not.)The mnemonic is "the key, the whole key, and nothing but the key": every non-key attribute must depend on the key (1NF), on all of it (2NF), and on nothing else (3NF/BCNF). 2NF only matters when the key is composite; a table with a single-column primary key is automatically in 2NF if it is in 1NF. The fixed shape of the flat table above is four tables, and the earlier city change is now a one-row update visible to every order:
customers(customer_id PK, name, city)
products(product_id PK, name, price)
orders(order_id PK, customer_id FK)
order_items(order_id FK, product_id FK, qty, PK(order_id, product_id))
-- after UPDATE customers SET city='Mumbai' WHERE customer_id=10 (real sqlite3 output)
order_id name city product qty line_total
-------- ---- ------ -------- --- ----------
1 Asha Mumbai Keyboard 1 50
1 Asha Mumbai Mouse 2 40
2 Asha Mumbai Mouse 1 20
3 Ravi Delhi Keyboard 3 150enrollment(student, course, instructor)
Rules: each instructor teaches exactly ONE course instructor → course
a student has one instructor per course (student, course) → instructor
Candidate keys: (student, course) and (student, instructor)
All three columns are prime (part of some key) => the table IS in 3NF.
But instructor → course has a non-superkey left side => NOT in BCNF.
Symptom (real output): rename Smith's course for one row only
instructor courses
---------- ------------
Jones OS
Smith Databases,DB
BCNF decomposition:
instructor_course(instructor PK, course)
student_instructor(student, instructor, PK(student, instructor))
Cost: the rule (student, course) → instructor can no longer be enforced by a
single key. The database happily accepts:
INSERT INTO student_instructor VALUES ('Ann','Smith'), ('Ann','Lee');
-- Smith and Lee both teach DB, so Ann now has two DB instructors.
student course n_instructors (detection query, real output)
------- ------ -------------
Ann DB 2This is the honest edge case worth knowing: BCNF is always achievable as a lossless-join decomposition, but it is not always dependency-preserving. 3NF is always achievable both lossless and dependency-preserving, which is why textbooks often say "aim for 3NF, go to BCNF when the anomaly is real and you can enforce the lost rule elsewhere (a trigger, a constraint on a join, or application code)." The decomposition trades a redundancy anomaly for a rule that now needs custom enforcement — it is a trade-off, not a free upgrade.
Normalized read of an order total:
orders JOIN order_items GROUP BY order_id SUM(qty*unit_price) -- work on every read
Denormalized: orders.total_amount (a stored copy of that SUM)
read : SELECT total_amount FROM orders WHERE order_id = 1 -- one row
write : every path that touches order_items MUST also update the copy
What goes wrong (real output, copy not updated):
order_id stored recomputed
-------- ------ ----------
1 90 150
What a guard looks like (trigger; SQLite syntax) and the result after it runs:
order_id stored recomputed
-------- ------ ----------
1 70 70Denormalization does not mean "no design". It means the normalized tables remain the source of truth and a derived copy is added for a specific read path, with an explicit answer to "who keeps this copy correct, and what happens when it is stale?" Options are a trigger or same-transaction application update (immediately consistent, more write work and lock contention on the hot row), a periodic recompute or materialized view (eventually consistent, cheap writes), or a CDC or outbox-driven read model (async, can live in a different store). Do not confuse a snapshot with a denormalized copy: storing unit_price on order_items at purchase time is not redundancy to be normalized away, because the order must keep the price that was charged even after the product price changes. It is a different fact.
-- Run with: sqlite3 :memory:
.headers on
.mode column
CREATE TABLE orders_flat (
order_id INTEGER, customer_id INTEGER, customer_name TEXT, customer_city TEXT,
product_id INTEGER, product_name TEXT, unit_price INTEGER, qty INTEGER,
PRIMARY KEY (order_id, product_id));
INSERT INTO orders_flat VALUES
(1, 10, 'Asha', 'Pune', 100, 'Keyboard', 50, 1),
(1, 10, 'Asha', 'Pune', 101, 'Mouse', 20, 2),
(2, 10, 'Asha', 'Pune', 101, 'Mouse', 20, 1),
(3, 11, 'Ravi', 'Delhi', 100, 'Keyboard', 50, 3);
-- update anomaly: change city in ONE row only
UPDATE orders_flat SET customer_city = 'Mumbai' WHERE order_id = 1 AND product_id = 100;
SELECT customer_id, GROUP_CONCAT(DISTINCT customer_city) AS cities,
COUNT(DISTINCT customer_city) AS n
FROM orders_flat GROUP BY customer_id;
-- customer_id cities n
-- 10 Mumbai,Pune 2
-- 11 Delhi 1
-- deletion anomaly
DELETE FROM orders_flat WHERE order_id = 3;
SELECT COUNT(*) AS rows_for_ravi FROM orders_flat WHERE customer_id = 11;
-- rows_for_ravi = 0 (Ravi's existence is gone)
-- Now normalize (new session): customers / products / orders / order_items
PRAGMA foreign_keys = ON;
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, price INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers);
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders, product_id INTEGER REFERENCES products,
qty INTEGER NOT NULL, PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (10,'Asha','Pune'),(11,'Ravi','Delhi');
INSERT INTO products VALUES (100,'Keyboard',50),(101,'Mouse',20);
INSERT INTO orders VALUES (1,10),(2,10),(3,11);
INSERT INTO order_items VALUES (1,100,1),(1,101,2),(2,101,1),(3,100,3);
UPDATE customers SET city = 'Mumbai' WHERE customer_id = 10; -- ONE row changes
SELECT o.order_id, c.name, c.city, p.name AS product, oi.qty, p.price*oi.qty AS line_total
FROM order_items oi JOIN orders o USING(order_id) JOIN customers c USING(customer_id)
JOIN products p USING(product_id) ORDER BY 1,4;
-- order_id name city product qty line_total
-- 1 Asha Mumbai Keyboard 1 50
-- 1 Asha Mumbai Mouse 2 40
-- 2 Asha Mumbai Mouse 1 20
-- 3 Ravi Delhi Keyboard 3 150
-- Denormalization drift (new session)
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, total_amount INTEGER NOT NULL DEFAULT 0);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, qty INTEGER, unit_price INTEGER,
PRIMARY KEY (order_id, product_id));
INSERT INTO orders (order_id, customer_id) VALUES (1, 10);
INSERT INTO order_items VALUES (1,100,1,50),(1,101,2,20);
UPDATE orders SET total_amount = (SELECT SUM(qty*unit_price) FROM order_items WHERE order_id=1) WHERE order_id=1;
UPDATE order_items SET qty = 5 WHERE order_id=1 AND product_id=101; -- forgot to update total
SELECT o.order_id, o.total_amount AS stored,
(SELECT SUM(qty*unit_price) FROM order_items i WHERE i.order_id=o.order_id) AS recomputed
FROM orders o;
-- order_id stored recomputed
-- 1 90 150 <-- the copy drifted
CREATE TRIGGER items_sync AFTER UPDATE ON order_items
BEGIN
UPDATE orders SET total_amount = (SELECT SUM(qty*unit_price) FROM order_items WHERE order_id=NEW.order_id)
WHERE order_id = NEW.order_id;
END;
UPDATE order_items SET qty = 1 WHERE order_id=1 AND product_id=101;
-- re-run the SELECT: stored = 70, recomputed = 70
-- PostgreSQL: same idea as a materialized view (not run here)
-- CREATE MATERIALIZED VIEW order_totals AS
-- SELECT order_id, SUM(qty*unit_price) AS total FROM order_items GROUP BY order_id;
-- REFRESH MATERIALIZED VIEW order_totals; -- stale until you refresh
Q: What is normalization?
A: It is decomposing tables so every fact depends on the key, the whole key and nothing but the key, which removes the redundancy that causes update, insert and delete anomalies. In practice that means moving each functional dependency such as customer_id → city into a table whose primary key is the left-hand side, and linking with foreign keys. The trade-off is more tables and joins in exchange for a schema where the engine can enforce consistency instead of relying on every code path to remember it.
Q: Why denormalize?
A: To move work from read time to write time when a specific read path is too slow or too expensive, for example precomputing an order total or copying a product name into a feed row so a hot screen needs one lookup instead of a five-way join. You pay with redundancy: every copy needs a synchronization mechanism (trigger, same-transaction update, materialized view refresh, or async pipeline) and you accept either extra write cost or bounded staleness. It is the wrong choice when the join is fast enough with proper indexes, when the copied data changes often, or when correctness must be immediate and you have no reliable way to keep the copy in sync.
Follow-up: If a table is in 3NF, can it still have anomalies?
A: Yes, in the rare case where a non-superkey determinant points at a prime attribute, which is exactly the instructor/course example: 3NF permits it because the dependent column is part of a candidate key, but a redundancy anomaly remains. BCNF closes that gap. The caveat is that BCNF decomposition may not preserve every functional dependency, so a rule like "a student has one instructor per course" may need a trigger or application check afterwards.
Follow-up: Does normalization hurt performance?
A: It depends on the workload. Writes generally get cheaper and smaller because each fact is changed once, and hot rows contend less. Reads that need data from several tables pay for joins, which with indexes on foreign keys are generally fast for OLTP-sized lookups but can become the bottleneck for large analytical scans, which is why warehouses use star schemas. The right answer is to measure with EXPLAIN on real data, not to denormalize on instinct.
Follow-up: Is storing unit_price on order_items a normalization violation?
A: No. The product's current price and the price charged on that order are two different facts with different lifetimes; normalizing them into one would silently rewrite historical invoices when the price changes. Denormalization is redundancy of the same fact; a historical snapshot is a separate fact that happens to have the same value today.
Follow-up: How do you denormalize safely in a system with multiple writers?
A: Keep the normalized tables as the only source of truth and make the copy derivable from them, so it can always be rebuilt. Update it in the same transaction as the source when you need immediate consistency and can afford the extra lock time, or via an outbox or change-data-capture pipeline when bounded staleness is acceptable. Add a periodic reconciliation query (like the stored-versus-recomputed check above) so drift is detected rather than discovered by a customer.
In a system-design answer, the normalized relational schema is your system of record and every other representation is a derived, denormalized view of it: a Redis cache of a product page, an Elasticsearch document, a feed table, a materialized daily-sales table, or a vector-store row holding chunk text plus source metadata. Naming each of those as a denormalized copy forces you to state the three things interviewers want to hear: what the source of truth is, how changes propagate (same transaction, outbox, CDC, scheduled rebuild), and what staleness the product can tolerate. For AI workloads the same reasoning applies to embeddings: the embedding row is a derived copy of a document chunk, so a schema that stores the document id, version and content hash alongside the vector lets you find and rebuild stale copies. It also explains why OLTP schemas lean toward 3NF while reporting stores lean toward wide, pre-joined tables: the two have opposite read/write ratios and different tolerance for staleness.
Normalization buys correctness and smaller writes at the cost of joins and more schema objects; denormalization buys cheaper reads at the cost of redundancy, synchronization logic, and the risk of silent drift. The default should be normalized to roughly 3NF for the system of record, then denormalize only specific, measured hot paths and record the maintenance mechanism next to the column. Denormalization is the wrong choice when the underlying data changes frequently relative to how often it is read, when you cannot guarantee every write path updates the copy, or when the query is slow only because a foreign key lacks an index. Pushing normalization to BCNF or beyond is the wrong choice when the decomposition would lose a rule you cannot cheaply re-enforce, and over-normalizing into many tiny tables makes ordinary queries needlessly expensive and harder to reason about. Engines differ in how well they support the derived-copy machinery: PostgreSQL has materialized views (refreshed manually or on a schedule, not incrementally on its own), SQL Server offers indexed views that are maintained automatically, and MySQL has neither natively, so the mechanism you choose depends on the engine you run.