What is normalization (1NF, 2NF, 3NF) and when would you denormalize?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
Normalization removes redundancy by splitting data into focused tables: 1NF requires atomic values and no repeating groups, 2NF removes partial dependencies on part of a key, 3NF removes transitive dependencies on non-key columns. It keeps writes consistent but costs JOINs at read time — so you denormalize (duplicate or precompute data) when reads dominate and the JOIN cost hurts.
Detailed explanation
Unnormalized data repeats itself: an orders row storing the customer's name, email, and address on every order means a name change requires updating thousands of rows — and partial updates cause anomalies. 1NF fixes the shape: each cell holds one atomic value, each row is unique — no comma-separated tags columns, no 'address1, address2, address3' groups; repeating things move to child tables. 2NF fixes partial dependence: in a table with a composite key (order_id, product_id), every non-key column must depend on the whole key — product_name depends only on product_id, so it moves to a products table. 3NF fixes transitive dependence: non-key columns must depend only on the key — a zip_code determining city means city depends on zip, not the order key, so cities move out. The result: each fact lives in exactly one place, writes are anomaly-free. The price is read complexity — reassembling an order needs several JOINs. Denormalization deliberately reintroduces redundancy for read speed: store the customer's name on the order, keep a orders_count counter on users, precompute daily aggregates. It is justified when the workload is read-heavy, the JOIN path is measurably slow, and you can tolerate the write-side cost (keeping copies in sync via transactions, triggers, or background jobs). Most production schemas are normalized first, then selectively denormalized where profiling proves it pays.
Example
-- 3NF: each fact lives once
CREATE TABLE customers (id SERIAL PRIMARY KEY, email TEXT UNIQUE);
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price_cents INT);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id),
product_id INT REFERENCES products(id),
qty INT NOT NULL
);
-- Denormalized for read speed: precomputed total avoids a JOIN + math per read
-- ALTER TABLE orders ADD COLUMN line_total_cents INT;
-- (kept in sync by the app or a trigger; stale if the writer forgets)Interview tip
State each normal form as the anomaly it kills — repeating groups, partial dependency, transitive dependency — then give one concrete denormalization (a counter cache) with its sync cost.
Common mistakes
- Reciting the normal forms without explaining the anomaly each prevents.
- Denormalizing by default instead of normalizing first and optimizing proven hot paths.