Databases
Database normalization: interview questions and how to answer them
Normalization removes redundancy so that every fact is stored exactly once — which is what makes update, insert and delete anomalies impossible.
Written and reviewed by Sahil Srivastav
What it actually is
Normalization is the process of organising columns and tables so that each fact lives in exactly one place. The normal forms are a ladder of increasingly strict conditions on functional dependencies — statements of the form "this column determines that one" — and each rung removes a specific class of redundancy.
The framing that makes it useful in an interview is to lead with the anomalies rather than the forms. Redundancy is not objectionable because it wastes space; storage is cheap. It is objectionable because the same fact stored twice can disagree with itself, and a database that can contradict itself has no reliable answer to give.
Specifically, three things become possible once a fact is duplicated. An update anomaly: you change a customer address in one row and miss the other forty, so the database now holds two addresses for one customer. An insertion anomaly: you cannot record a new product because no order exists for it yet and product details live only on order rows. A deletion anomaly: removing the last order for a product destroys the only record that the product existed.
Why it matters in production
Because these anomalies are silent. Nothing errors, nothing logs, and the data simply becomes inconsistent over months until someone notices that two reports disagree. By then there is no way to determine which copy was correct, and the reconciliation is manual. A normalized schema makes the whole class of problem structurally impossible rather than something you have to remember to handle.
It also matters because the counter-argument is usually made badly. Denormalization is a legitimate and frequently correct decision, but it is a decision to accept redundancy in exchange for read performance — and that trade is only safe if you have a plan for keeping the copies in agreement. Candidates who denormalize without naming that obligation are describing a future data-quality incident.
How it works
First normal form: atomic values
Each column holds a single value, not a list, and there are no repeating groups like phone1, phone2, phone3. The practical consequence is that you can query, index and constrain the value. A comma-separated list in a column cannot be joined against, cannot be indexed usefully, and cannot have a foreign key — which is the real argument against it, not purity.
Second normal form: no partial dependency on a composite key
With a composite primary key, every non-key column must depend on the whole key rather than part of it. In an order_items(order_id, product_id) table, product_name depends only on product_id, so it belongs in products. Leaving it creates one copy of the name per order line, and those copies can diverge.
Third normal form: no transitive dependency
Non-key columns must depend on the key and nothing else. If orders holds customer_id, customer_city and customer_pincode, the pincode depends on the city rather than on the order — a transitive dependency. The city and pincode belong with the customer, and a customer moving should require one update, not one per order.
BCNF and where 3NF leaves a gap
Boyce-Codd normal form requires that every determinant is a candidate key. It closes a case 3NF permits, where a non-key column determines part of a key. It comes up rarely in practice and almost never in product schemas, but knowing it exists and that it is stricter than 3NF is a reasonable thing to be able to say.
Where to stop
3NF is the practical target for a transactional schema, and most well-designed schemas land there without anyone consciously applying the rules. Higher forms address multi-valued and join dependencies that rarely occur in application databases. "3NF, then denormalize deliberately where measurement justifies it" is the defensible position.
Implementing it
Design to 3NF first for anything transactional. The cost of normalizing later, after inconsistent duplicates have accumulated, is far higher than the cost of a join now.
Distinguish a duplicated fact from a deliberate historical snapshot. Storing the price on an order line is not a normalization failure — the price at purchase time is a different fact from the current price, and it must not change when the product is repriced.
Let constraints do the enforcing. Normalization defines where a fact lives; foreign keys and unique constraints are what stop the schema drifting back toward duplication.
When you do denormalize, write down which copy is authoritative and what keeps the other in agreement — a trigger, a materialized view, a scheduled reconciliation. An undocumented duplicate is the one that diverges.
-- Not in 3NF: pincode depends on city, not on the order.
-- One customer relocating means updating every one of their orders.
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer_id bigint NOT NULL,
customer_city text NOT NULL,
customer_pincode text NOT NULL,
placed_at timestamptz NOT NULL
);
-- 3NF: the address is a fact about the customer, stored once.
CREATE TABLE customers (
id bigserial PRIMARY KEY,
city text NOT NULL,
pincode text NOT NULL
);
CREATE TABLE orders (
id bigserial PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
placed_at timestamptz NOT NULL,
-- NOT a normalization failure: the price when the order was placed is a
-- different fact from the product’s current price.
unit_price_minor integer NOT NULL
);Interview questions and how to answer them
Why normalize at all, if storage is cheap?
Because the problem is contradiction, not space. A fact stored twice can disagree with itself, and nothing errors when it does — you discover it months later when two reports differ and no one can say which copy is right. Normalization makes that class of bug structurally impossible rather than something you have to remember to prevent.
Give an example of an update anomaly.
Customer address stored on every order row. The customer moves, you update the recent orders, and the older ones still carry the old address. The database now holds two addresses for one customer with no way to tell which is current. Storing the address once on the customer makes the update a single row.
Is storing the price on an order line a normalization failure?
No, and this is the distinction that matters most in practice. The price at the time of purchase is a genuinely different fact from the product's current price — it must not change when the product is repriced. Duplication is a failure when both copies are supposed to represent the same fact; a historical snapshot is a separate fact that happens to share a value initially.
How far should you normalize?
3NF for transactional schemas, which is where good design lands naturally. BCNF closes a narrow gap that rarely appears in product data. Beyond that the forms address dependencies you will almost never encounter. The useful discipline is 3NF by default, then denormalize deliberately where measurement justifies it and with a stated plan for keeping copies consistent.
What keeps a normalized schema normalized?
Constraints. Normalization is a design decision; foreign keys, unique constraints and check constraints are the enforcement that prevents drift. Without them, application code eventually writes the duplicate the schema was designed to avoid, and the design exists only in documentation.
Answers that lose the round
- Reciting the normal forms without being able to name the anomaly each one prevents
- Treating normalization as being about saving space rather than about preventing contradiction
- Calling a historical snapshot — price at purchase, address at shipment — a denormalization error
- Denormalizing without naming what keeps the copies in agreement
- Storing comma-separated lists and losing the ability to join, index or constrain
- Normalizing an analytical schema where a star schema is the right answer
- Assuming joins are expensive enough to justify duplication, without having measured
FAQ
Do joins make normalized schemas slow?
Much less than people assume. Joins on indexed keys are cheap, and the planner is good at them. The performance problems attributed to normalization are usually missing indexes or N+1 query patterns, both of which survive denormalization and should be diagnosed before restructuring the schema.
How does this apply to document databases?
The same anomalies exist; the trade is just made differently. Embedding a sub-document duplicates facts and buys single-read access, which is often right — but updating an embedded value everywhere it appears becomes your responsibility rather than the engine's. The reasoning transfers; the enforcement does not.
Is normalization relevant for analytics schemas?
Less so. Analytical workloads favour star and snowflake schemas where dimension data is deliberately denormalized for query simplicity and scan efficiency, and the data is loaded rather than mutated, so update anomalies largely do not arise. Normalization is a transactional-schema concern.
What is a functional dependency, in plain terms?
"If you know A, you know B" — product_id determines product_name. The normal forms are rules about which dependencies are allowed to exist in the same table, and that is why the formal definitions seem abstract until you translate each one into the anomaly it is preventing.