What is database normalization?

Q: What is database normalization?

**Target Audience:** Beginners who have built a few tables and want to understand why schemas are split the way they are, plus candidates who need to define 1NF to BCNF precisely. **Difficulty:** Medium **Engines:** The idea is engine independent. Every query here ran on SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use) and PostgreSQL 16.15 with the same results; the one difference found is called out in section 4. > **How to read this doc:** Each idea is first explained in plain language, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error shown was produced by actually running it; the **Verified** section quotes each example output in full. ## 1. Why This Even Matters — A Story First A small shop keeps its orders in one spreadsheet. Every row has the order number, the date, the customer name, email and city, the product code, name and price, and the quantity. It works fine for a month. Then Asha

The problem

Question presented to candidate: "What is database normalization? Walk me through 1NF, 2NF and 3NF with an example, and tell me what problems normalization actually prevents."

What a strong answer should cover:

Normalization is the process of splitting tables so that each fact is stored once, in the table whose key it depends on, to remove redundancy and the update, insert and delete anomalies it causes
The normal forms as rules about keys and functional dependencies: 1NF (atomic values, no repeating groups), 2NF (no non-key column depends on only part of a composite key), 3NF (no non-key column depends on another non-key column), BCNF (every determinant is a candidate key)
A concrete decomposition: a flat orders sheet split into customers, products, orders and order_items, joined back with foreign keys and no information lost
Subtle modelling points: a price at the time of sale belongs on the order line, not on the product; a repeated value is not always redundancy
Trade-off: more tables and joins on reads, cleaner writes and constraints; most OLTP schemas aim for 3NF and denormalize deliberately where reads demand it

Code / implementation expected: Optional — queries that expose redundancy in a flat table, and the normalized tables joined back into the same report.

Schema used by the examples:

normalization

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have built a few tables and want to understand why schemas are split the way they are, plus candidates who need to define 1NF to BCNF precisely. Difficulty: Medium Engines: The idea is engine independent. Every query here ran on SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use) and PostgreSQL 16.15 with the same results; the one difference found is called out in section 4.

How to read this doc: Each idea is first explained in plain language, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error shown was produced by actually running it; the Verified section quotes each example output in full.

1. Why This Even Matters — A Story First

A small shop keeps its orders in one spreadsheet. Every row has the order number, the date, the customer name, email

The solution is hidden so you can try first

15 min read · 9 sections

Run the code

Edit the query and run it against the tables for this question.

SQLUpdate anomaly: the flat table now holds two different emails for the same customer

SQLTest a functional dependency: does product_sku decide the name and the price?