Take this table to third normal form, then tell me when you would deliberately denormalise — and what does ACID guarantee?

Getting to 3NF by naming the dependency

Normalisation is one idea applied repeatedly: store every fact exactly once, so that changing it is a single write and cannot leave two disagreeing copies behind. The normal forms are just the checkpoints on the way, and a strong answer walks through them by naming the dependency it is eliminating rather than quoting the definitions.

Start with a flattened orders table holding order_id, product_id, quantity, product_name, customer_id, customer_city, and city_tax_rate.

First normal form requires each column to hold a single atomic value with no repeating groups — no comma-separated product_ids in one field, no item1, item2, item3 columns. Second normal form applies once the key is composite. If the key is (order_id, product_id), then product_name depends on product_id alone: a partial dependency on part of the key. Every product name is duplicated across every order line that mentions it, so a rename has to touch all of them. Split products into their own table.

Third normal form removes transitive dependencies, where a non-key column determines another non-key column. Here customer_id determines customer_city, and customer_city determines city_tax_rate. Neither depends on the order at all, so both are stored redundantly on every order and can be updated inconsistently. Move the customer attributes to a customers table and the tax rate to a cities table. That is 3NF, and the usual compression of it is exact: every non-key attribute depends on the key, the whole key, and nothing but the key.

BCNF tightens this to require that every determinant is a candidate key, which catches a case 3NF allows — when a non-key attribute determines part of a candidate key. It is worth naming as the next step even if the interviewer does not ask.

Denormalising as a stated trade

Denormalisation is duplicating a fact on purpose, accepting that you now own the job of keeping the copies consistent. It buys read performance, and the price is paid on every write. The reason it is asked is that the answer separates people who normalise reflexively from people who model deliberately.

The legitimate cases are narrow. A read path that joins five tables on every request, where the join is measurably the cost and the joined data is nearly static, is the classic one — carrying customer_city on the order row removes a join at the cost of an update fan-out when a customer moves. Analytical schemas do it structurally: a star schema's dimension tables are deliberately not in 3NF, because the workload is scans and aggregations rather than updates, and a snowflaked dimension buys tidiness nobody benefits from. Aggregates are the third case — a comment_count on a post, maintained by trigger or by application code, because computing it on read is a scan the read path cannot afford. And caching a computed result in a materialised view is denormalisation with the consistency problem made explicit as a refresh schedule.

One case that looks like denormalisation and is not: recording the price on an order line. The current price lives on the product, but the price charged at the time of that order is a different fact with its own lifetime, and it belongs on the order. Candidates who call that denormalisation have mistaken a temporal fact for a duplicated one, and interviewers notice.

What ACID does and does not guarantee

This is the half where confident answers are usually wrong, because the acronym is memorable and its meaning is not.

Atomicity is all-or-nothing per transaction. The database ensures a partially applied transaction is never visible and never survives a crash, typically by keeping enough undo information to reverse it. It says nothing about correctness — an atomic transaction can commit nonsense perfectly.

Consistency is the misunderstood one. In ACID, it means only that a transaction moves the database from one state satisfying its declared constraints to another: primary keys, foreign keys, uniqueness, check constraints. It is not a promise that your business rules hold, because the database has never been told what they are. If your rule is "an account balance may not go below zero" and there is no check constraint expressing it, ACID does not defend it. This C is also unrelated to the C in CAP, which is about replicas agreeing — conflating the two is the single most common error in this answer.

Isolation is the one with dials, and it is graded on whether you know that. Isolation is not binary; the SQL standard defines four levels by which anomalies they permit.

• READ UNCOMMITTED permits dirty reads: seeing another transaction's uncommitted writes.
• READ COMMITTED forbids dirty reads but permits non-repeatable reads, where re-reading a row within your transaction returns a changed value.
• REPEATABLE READ forbids that too, but classically permits phantoms, where a re-run query returns rows that did not previously match.
• SERIALIZABLE forbids all of them, and the result must be equivalent to running the transactions one after another.

Implementations differ from the standard in ways worth knowing. PostgreSQL has no true READ UNCOMMITTED — requesting it gives you READ COMMITTED — and its REPEATABLE READ is snapshot isolation, which does prevent phantoms. Snapshot isolation, however, still permits write skew, where two transactions each read a consistent snapshot and write disjoint rows in a way that jointly breaks an invariant neither could see being broken. PostgreSQL's SERIALIZABLE detects that and aborts one transaction with a serialisation failure, which means code running at SERIALIZABLE must be prepared to retry.

Durability promises that a committed transaction survives a crash. What it specifically promises is that the write-ahead log record was flushed to durable storage before the commit was acknowledged — not that the data page was written. That is why the data files may legitimately be stale after a crash and recovery replays the log to catch them up, and it is why durability collapses if a disk or virtualisation layer lies about having flushed.

ACID's C is the weakest letter in the acronym: it enforces the constraints you declared, and nothing about the invariants you merely intended.