Normalization questions in interviews rarely want the textbook definitions of third normal form. They want to know whether you can look at a table and say which facts are stored twice, what goes wrong when one copy changes, and when storing a copy on purpose is the right call. Senior candidates are expected to defend a denormalization, not just avoid one.
The schemas below were created and queried on PostgreSQL 16.15.
One flat table, three facts tangled together
Here’s an orders export that someone turned into the main table:
CREATE TABLE orders_flat (
order_id int, ordered_at date,
customer_email text, customer_city text,
sku text, product_name text, unit_price numeric(10,2), qty int
);
INSERT INTO orders_flat VALUES
(1, '2026-09-01', 'ana@example.com', 'Lisbon', 'MUG-01', 'Stoneware mug', 12.00, 2),
(1, '2026-09-01', 'ana@example.com', 'Lisbon', 'TEE-02', 'Logo tee', 20.00, 1),
(2, '2026-09-15', 'ana@example.com', 'Lisbon', 'MUG-01', 'Stoneware mug', 12.00, 1);Each row mixes facts about three different things: the customer (email, city), the product (name) and the order line (price paid, quantity). The customer’s city appears three times. The product name appears once per sale. Ana moves to Porto, and the code that handles address changes only updates her latest order:
UPDATE orders_flat SET customer_city = 'Porto' WHERE order_id = 2;
SELECT customer_email, array_agg(DISTINCT customer_city) AS cities
FROM orders_flat GROUP BY 1; customer_email | cities
-----------------+----------------
ana@example.com | {Lisbon,Porto}Where does Ana live? The database now holds two answers, and no query can tell which is right. That’s an update anomaly. The flat design has two more: you can’t record a product until someone buys it (insertion anomaly), and deleting the last order for a product deletes the only record of its name (deletion anomaly).
The formal normal forms describe these problems precisely. In practice, one question gets you most of the way: does every non-key column describe the key, the whole key, and nothing but the key? customer_city describes the customer, not the order line, so it belongs in a table keyed by customer.
Splitting by what each fact describes
CREATE TABLE customers (id int PRIMARY KEY, email text NOT NULL UNIQUE, city text NOT NULL);
CREATE TABLE products (sku text PRIMARY KEY, name text NOT NULL, list_price numeric(10,2) NOT NULL);
CREATE TABLE orders (
id int PRIMARY KEY,
customer_id int NOT NULL REFERENCES customers,
ordered_at date NOT NULL,
ship_city text NOT NULL
);
CREATE TABLE order_lines (
order_id int REFERENCES orders,
sku text REFERENCES products,
unit_price numeric(10,2) NOT NULL,
qty int NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, sku)
);Ana’s current city now lives in one row, so moving her is one update and it can’t half-succeed. Product names live in products. Foreign keys make orphaned lines impossible.
Look closely at two columns that seem to break the rule: orders.ship_city and order_lines.unit_price. Isn’t the city already in customers and the price in products?
id | email | ship_city | current_city | name | unit_price | list_price
----+-----------------+-----------+--------------+---------------+------------+------------
1 | ana@example.com | Lisbon | Porto | Stoneware mug | 12.00 | 14.00
1 | ana@example.com | Lisbon | Porto | Logo tee | 20.00 | 20.00
2 | ana@example.com | Porto | Porto | Stoneware mug | 12.00 | 14.00They’re different facts. ship_city is where order 1 was shipped, which stays Lisbon forever, even though Ana now lives in Porto. unit_price is what she paid (12.00); the mug’s list price has since gone up to 14.00. Recomputing an old invoice from current prices would be a bug, possibly a legal one. This is the most common normalization mistake in the other direction: “normalizing away” historical values by joining to the current ones. A good interview answer names the distinction: a snapshot taken at event time is its own fact, not a duplicate.
When to store a copy on purpose
Normalized schemas pay for correctness with joins and aggregation at read time. Usually that’s cheap. Sometimes a page runs a count(*) over millions of rows on every view, or a dashboard re-aggregates a year of orders each time someone opens it. That’s when deliberate denormalization makes sense, and the honest way to propose it is: the read problem you measured, the copy you’ll add, and the mechanism that keeps it correct.
A counter maintained by a trigger. Say order pages need the line count without counting:
ALTER TABLE orders ADD COLUMN line_count int NOT NULL DEFAULT 0;
UPDATE orders o SET line_count = (SELECT count(*) FROM order_lines l WHERE l.order_id = o.id);
CREATE FUNCTION bump_line_count() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE orders SET line_count = line_count + 1 WHERE id = NEW.order_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE orders SET line_count = line_count - 1 WHERE id = OLD.order_id;
END IF;
RETURN NULL;
END $$;
CREATE TRIGGER order_lines_count
AFTER INSERT OR DELETE ON order_lines
FOR EACH ROW EXECUTE FUNCTION bump_line_count();After adding a line to order 2 and deleting one from order 1, the counters read 1 and 2, matching the real counts. Because the trigger runs inside the same transaction as the insert or delete, the copy and the source commit together. The costs are real, though: every concurrent insert for the same order now updates the same orders row, so they serialize on its row lock. That’s fine for order lines and a serious hot spot for “likes on a viral post”. This trigger also ignores updates that move a line to another order, and TRUNCATE doesn’t fire row triggers at all. Each gap is a way for the copy to drift, so pair it with a check you can run on a schedule:
SELECT o.id
FROM orders o
LEFT JOIN order_lines l ON l.order_id = o.id
GROUP BY o.id, o.line_count
HAVING o.line_count <> count(l.order_id);A materialized view for reporting. For aggregates that can be a little stale, store the query result and refresh it:
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT o.ordered_at AS day, sum(l.unit_price * l.qty) AS revenue
FROM orders o JOIN order_lines l ON l.order_id = o.id
GROUP BY 1;
CREATE UNIQUE INDEX ON daily_revenue (day);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;CONCURRENTLY lets readers keep using the old contents during the refresh, and it requires a unique index on the view. The trade-off is freshness: the dashboard is as old as the last refresh, which has to be scheduled (cron, pg_cron, or a job runner). PostgreSQL recomputes the whole query on each refresh; it doesn’t update incrementally.
Copies in other systems (a search index, a cache, a reporting warehouse) follow the same rules with weaker guarantees, since they can’t commit atomically with the source. They need an event stream or a change-data-capture pipeline, plus a reconciliation job.
Misconceptions interviewers listen for
- “Normalized means slow.” Joins on indexed keys are what relational databases are built for. Denormalize for a measured problem, not a feared one.
- “Denormalization is just duplication.” Unmanaged duplication is a bug. Denormalization is duplication with an owner, a sync mechanism and a drift check.
- “JSON columns avoid the question.” A
jsonbblob with the customer’s address inside each order has the same update anomaly; it’s just harder to query. JSON is fine for data that’s genuinely document-shaped or a snapshot. - “Every table must reach 3NF.” Normal forms are a tool for spotting anomalies. An append-only event log or a data warehouse star schema breaks them deliberately, because updates aren’t the main workload.
Interview practice: the product page that counts reviews
A product page shows avg(rating) and count(*) from a reviews table with 80 million rows. At peak it runs 2,000 times a second and the database CPU is pinned. A teammate proposes adding review_count and rating_sum columns to products. Evaluate the proposal.
Answer
It’s a reasonable denormalization, but I’d want the details pinned down. First, confirm the cost is the aggregate: with an index on reviews (product_id) each page still reads every review for that product, and popular products have tens of thousands. If EXPLAIN ANALYZE shows that, storing review_count and rating_sum (not the average, so it can be updated incrementally and avoids rounding drift) makes the read a primary-key lookup.
Then the maintenance plan. A trigger on reviews for insert, delete and rating updates keeps the columns transactionally correct. The risk is contention: every review for one product updates the same products row, and that row is also read constantly. For most products that’s fine. For a viral product, I’d consider writing reviews normally and updating the aggregates in a batch every minute, accepting slightly stale numbers, or sharding the counter into several rows that get summed. Whichever we pick, add a nightly reconciliation that recomputes from reviews and reports mismatches, and document that reviews is the source of truth. An alternative worth mentioning is caching the computed pair in Redis or the CDN for 60 seconds, which avoids schema changes if the product team accepts the staleness.