SQL Interview Question: Explain Normalisation (1NF, 2NF, 3NF)

Short answer: normalisation organises tables to reduce duplicated data and prevent update anomalies. Each normal form removes a specific kind of redundancy.

Starting point

orders_raw
order_id | customer | customer_city | products              | product_prices
101      | Asha     | Pune          | "Mug, T-shirt"        | "349, 799"

1NF: atomic values, no repeating groups

Each cell holds one value. Split the lists into one row per order item:

order_items: order_id | product | price | customer | customer_city

2NF: no partial dependency on a composite key

With key (order_id, product), the customer depends only on order_id and the price only on product. Move them to their own tables:

orders(order_id, customer_id)
products(product_id, name, price)
order_items(order_id, product_id, quantity)

3NF: no transitive dependency

customer_city depends on the customer, not the order. Store it once:

customers(customer_id, name, city)

Memory aid: every non-key column depends on "the key, the whole key, and nothing but the key".

Anomalies it prevents

• Update: Asha moves city; without 3NF you must update every order row and might miss some.
• Insert: you can't add a product until someone orders it.
• Delete: deleting the only order for a product loses the product's price.

When to denormalise

• Read-heavy reports and dashboards (precomputed totals, materialised views).
• Historical snapshots: an order should store the price at the time of purchase, which is correct duplication, not a mistake.
• Document databases and caches, where data is shaped for reads.

Good answer structure: normalise by default for correctness, then denormalise deliberately where measurements show a need.