Database Design and Normalization

In the SQL for Beginners series you have been handed a working schema — customers, products, orders and order_items — since chapter 9. Chapter 22 of this 24-chapter course turns the tables around and teaches you to build one: it takes a table that a spreadsheet would love and walks it through the three classic checks — first, second and third normal form — until every fact is stored exactly once.

A table that has gone wrong

Here is the export a marketing colleague keeps in one file: one row per customer, everything they know about them in the same place. It is convenient, it sorts nicely, and it is quietly broken in three separate ways:

CREATE TABLE customer_report (
    customer_id    INTEGER PRIMARY KEY,
    customer       TEXT NOT NULL,
    city           TEXT NOT NULL,
    country        TEXT NOT NULL,
    country_region TEXT NOT NULL,
    interests      TEXT NOT NULL
);

INSERT INTO customer_report VALUES
 (1, 'Aarav Sharma',  'Mumbai',  'India',  'Asia',   'Fiction, Tech'),
 (3, 'Lena Fischer',  'Berlin',  'Germany','Europe', 'Tech'),
 (6, 'Amara Ochieng', 'Nairobi', 'Kenya',  'Africa', 'Kids, Fiction');

Read it back and the shape of the problem is visible in the last column:

sqlite3 shop.db "SELECT * FROM customer_report;"

Expected output:

1|Aarav Sharma|Mumbai|India|Asia|Fiction, Tech
3|Lena Fischer|Berlin|Germany|Europe|Tech
6|Amara Ochieng|Nairobi|Kenya|Africa|Kids, Fiction

Two interests live in one cell, so SQL cannot filter by a single interest, join it to a product category or index it meaningfully — the column holds a list pretending to be a value.

First normal form: one value per cell

1NF demands atomic values: every cell holds one value, there are no repeating groups of columns, and the table has a key that identifies each row. Fixing the list is mechanical — give each interest its own row, and let (customer_id, interest) be the pair that makes a row unique:

CREATE TABLE report_1nf (
    customer_id    INTEGER NOT NULL,
    customer       TEXT NOT NULL,
    city           TEXT NOT NULL,
    country        TEXT NOT NULL,
    country_region TEXT NOT NULL,
    interest       TEXT NOT NULL,
    PRIMARY KEY (customer_id, interest)
);

INSERT INTO report_1nf VALUES
 (1,'Aarav Sharma','Mumbai','India','Asia','Fiction'),
 (1,'Aarav Sharma','Mumbai','India','Asia','Tech'),
 (3,'Lena Fischer','Berlin','Germany','Europe','Tech'),
 (6,'Amara Ochieng','Nairobi','Kenya','Africa','Fiction'),
 (6,'Amara Ochieng','Nairobi','Kenya','Africa','Kids');

Expected output: (SELECT customer_id, customer, city, interest FROM report_1nf ORDER BY customer_id, interest;)

1|Aarav Sharma|Mumbai|Fiction
1|Aarav Sharma|Mumbai|Tech
3|Lena Fischer|Berlin|Tech
6|Amara Ochieng|Nairobi|Fiction
6|Amara Ochieng|Nairobi|Kids

Three rows became five, and every cell now holds exactly one thing — WHERE interest = 'Tech' works, a join works, an index works. But look at what got repeated: Aarav’s city and country appear on both of his rows, and will appear again on every interest he ever adds.

Second normal form: no partial dependencies

2NF starts from 1NF and adds one rule: every column that is not part of the key must depend on the whole key. Here the key is (customer_id, interest) — two columns — and customer, city, country and country_region depend only on the first half. That is a partial dependency, and it means update the same fact in five places or miss one. The fix removes those columns entirely: everything about a customer already lives in the customers table you have been querying since the start of the series, so all this table owns is the link:

CREATE TABLE customer_interests (
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    interest    TEXT NOT NULL,
    PRIMARY KEY (customer_id, interest)
);

INSERT INTO customer_interests VALUES
 (1,'Fiction'), (1,'Tech'), (3,'Tech'),
 (6,'Fiction'), (6,'Kids');

Expected output: (SELECT COUNT(*) FROM customer_interests;) 5 — five links, zero copies of anyone’s city.

Third normal form: no transitive dependencies

3NF adds the last trap: no non-key column may depend on another non-key column. In the original report, country_region follows country — wherever a customer lives decides their region, with no interest of the key involved. That is a transitive dependency (customer_id → country → region), and it means storing the same region on every customer from one country. Pull the pair out into its own table:

CREATE TABLE countries (
    country TEXT PRIMARY KEY,
    region  TEXT NOT NULL
);

INSERT INTO countries VALUES
 ('India','Asia'), ('Germany','Europe'), ('Canada','North America'),
 ('Singapore','Asia'), ('Kenya','Africa');

Now the report can be rebuilt on demand with plain joins, each fact coming from exactly one place — change a region once and every report follows:

SELECT c.id,
       c.first_name || ' ' || c.last_name AS customer,
       c.city,
       co.region,
       i.interest
FROM customer_interests i
JOIN customers c  ON c.id = i.customer_id
JOIN countries co ON co.country = c.country
ORDER BY c.id, i.interest;

Expected output:

1|Aarav Sharma|Mumbai|Asia|Fiction
1|Aarav Sharma|Mumbai|Asia|Tech
3|Lena Fischer|Berlin|Europe|Tech
6|Amara Ochieng|Nairobi|Africa|Fiction
6|Amara Ochieng|Nairobi|Africa|Kids

You can inspect the final shape the same way you inspected an index in chapter 19:

sqlite3 shop.db ".schema customer_interests"

Expected output:

CREATE TABLE customer_interests (
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    interest    TEXT NOT NULL,
    PRIMARY KEY (customer_id, interest)
);

Each of the three steps attacked a different repetition, and each rule has a one-line summary worth memorising:

When to break the rules

Normalization is not a score you win by reaching 3NF everywhere. The costs are real: reporting queries need more joins, and every join is work that an index from chapter 19 has to help with. So the deliberate exceptions are worth naming. Derived totals (revenue per order, items per order) are recomputed on the fly until performance says otherwise — and if you store them, refresh them inside a transaction from chapter 21 so they cannot drift. Read-optimised snapshots for dashboards are allowed to repeat facts, because they are rebuilt from normalized tables on a schedule. What is never allowed is a hand-maintained duplicate: the original customer_report failed not because it was denormalized but because nothing but a human kept it true.

Common mistakes

• Leaving a list in one cell — “Fiction, Tech” cannot be grouped, joined or indexed. Split it into rows before you write the first query against it.
• Skipping the key — a table with no primary key invites duplicates, and without a unique identity no join can be trusted. Declare the key with the table.
• Keeping partial dependencies — storing a customer’s city on every interest row means one update in five places and a mismatch waiting to happen. Move it to the customer table.
• Storing what you can look up — region, category names and prices copied down every row go stale the first time someone edits one of them. Keep the lookup pair in its own table.
• Normalizing by reflex — splitting a three-row reference list into its own table buys nothing and costs a join. Normalize the write path; denormalize the read path only when a measurement asks you to.

Practice checklist

1. Recreate customer_report and point at every cell that violates first normal form.
2. Split the interests list by hand into report_1nf and confirm five rows come back.
3. Find the partial dependency in report_1nf and move those columns back to customers.
4. Create countries, rebuild the report with three joins, and change one region to prove every row follows.
5. Take one of your own tables and ask of each column: does it depend on the key, the whole key, and nothing but the key?

What's next?

Your schema now stores each fact once — the last step is putting it in front of a user. Next you leave the prompt behind: SQL in Your Application covers parameterized queries, SQL injection, ORMs and migrations.