Database normalization — 1NF, 2NF, 3NF

Day 1 taught you indexes — they make reads fast. Day 10 taught you transactions — they make writes safe. Today: normalization — it makes your schema honest. Each fact stored once, in the right place, with no hidden dependencies.

The problem: one table for everything

You’re building an e-commerce app. You need to store orders. The “simple” approach — one big table:

CREATE TABLE bad_orders (
  order_id       INT,
  customer_name  VARCHAR(100),
  customer_email VARCHAR(100),
  customer_city  VARCHAR(50),
  product_name   VARCHAR(100),
  product_price  DECIMAL(10,2),
  quantity       INT
);

Looks fine. Now insert two orders from the same customer:

-- Order 1: Alice buys a keyboard
INSERT INTO bad_orders VALUES
  (1, 'Alice', 'alice@email.com', 'Jakarta',
   'Mechanical Keyboard', 75.00, 1);

-- Order 2: Alice buys a mouse
INSERT INTO bad_orders VALUES
  (2, 'Alice', 'alice@email.com', 'Jakarta',
   'Wireless Mouse', 25.00, 1);

Alice’s name, email, and city are stored twice. This causes four problems:

1. Update anomaly: Alice moves to Bandung. You update row 1. You forget row 2. Now the database says Alice lives in both Jakarta and Bandung. Which is true?
2. Insert anomaly: You want to add a new product (“Monitor”, $300) before anyone has ordered it. But bad_orders requires an order_id and customer_name. You can’t insert the product without a fake order.
3. Delete anomaly: Alice cancels order 1 (the keyboard). You delete that row. Now Alice’s name, email, and city are gone — she only exists in order 2. If she cancels that too, you lose the customer entirely. You can’t delete an order without deleting a customer.
4. Redundancy: Every order repeats customer data and product data. 10,000 orders from 100 customers = 10,000 copies of 100 customers’ names and emails. Wasted space, slower scans, more memory.

These are the anomalies that normalization eliminates. E.F. Codd defined the normal forms in 1970 to solve exactly this. We’ll cover the three that matter: 1NF, 2NF, 3NF.

1NF: First Normal Form — “atomic values, no repeating groups”

A table is in 1NF if every cell contains a single atomic value. No lists, no arrays, no comma-separated values in one cell.

Here’s a table that violates 1NF:

-- VIOLATION: products column has a list
order_id | customer | products
---------|----------|------------------------------
       1 | Alice    | Keyboard, Mouse, Monitor
       2 | Bob      | Webcam

The products column has three values crammed into one cell. You can’t query “who bought a Mouse?” without parsing strings. You can’t index it. You can’t join on it.

Fix: one row per order-product pair.

-- 1NF: each cell is atomic
order_id | customer | product
---------|----------|----------
       1 | Alice    | Keyboard
       1 | Alice    | Mouse
       1 | Alice    | Monitor
       2 | Bob      | Webcam

Now every cell is atomic. You can query, index, and join on product. This is 1NF.

2NF: Second Normal Form — “no partial dependencies”

A table is in 2NF if it’s in 1NF AND every non-key column depends on the entire primary key, not just part of it.

This only matters when you have a composite primary key (two or more columns). Here’s a table that violates 2NF:

-- Composite PK: (order_id, product_name)
-- VIOLATION: product_price depends on product_name only
order_id | product_name    | product_price | quantity
---------|-----------------|---------------|----------
       1 | Keyboard        |         75.00 |        1
       1 | Mouse           |         25.00 |        2
       2 | Keyboard        |         75.00 |        1
       3 | Monitor         |        300.00 |        1

The primary key is (order_id, product_name). But product_price depends only on product_name, not on order_id. This is a partial dependency. It causes the same anomalies:

• If the keyboard price changes, you must update rows 1 AND 2. Miss one → inconsistency.
• You can’t add a new product with a price until someone orders it (insert anomaly).

Fix: split into two tables. product_price moves to a products table where it depends on the full key.

-- 2NF: split — price lives in products table
-- Order items: PK = (order_id, product_name)
order_id | product_name | quantity
---------|--------------|----------
       1 | Keyboard     |        1
       1 | Mouse        |        2
       2 | Keyboard     |        1
       3 | Monitor      |        1

-- Products: PK = product_name
product_name | product_price
-------------|---------------
Keyboard     |         75.00
Mouse        |         25.00
Monitor      |        300.00

Now product_price is stored once per product. Change the keyboard price in one row. quantity stays in order_items because it depends on the entire key — which order AND which product.

3NF: Third Normal Form — “no transitive dependencies”

A table is in 3NF if it’s in 2NF AND no non-key column depends on another non-key column. In other words: every non-key column depends on the key, the whole key, and nothing but the key.

Here’s a table that violates 3NF:

-- PK: customer_id
-- VIOLATION: city depends on zip_code, not directly on customer_id
customer_id | customer_name | zip_code | city
------------|---------------|----------|----------
          1 | Alice         |    10110 | Jakarta
          2 | Bob           |    40125 | Bandung
          3 | Charlie       |    10110 | Jakarta

city depends on zip_code, and zip_code depends on customer_id. So city depends on customer_id transitively — through zip_code. This is a transitive dependency.

The problem: Alice and Charlie both have zip 10110. Both say “Jakarta.” If Jakarta’s city name changes, you must update both rows. Miss one → inconsistency.

Fix: split city into a zip_codes table.

-- 3NF: split — city lives in zip_codes table
-- Customers: PK = customer_id
customer_id | customer_name | zip_code
------------|---------------|----------
          1 | Alice         |    10110
          2 | Bob           |    40125
          3 | Charlie       |    10110

-- Zip codes: PK = zip_code
zip_code | city
---------|----------
   10110 | Jakarta
   40125 | Bandung

Now city is stored once per zip code. “Jakarta” appears once, not twice.

The normalized result

Starting from one bad_orders table, normalization gives us four tables. To query “Alice’s orders with product names,” you JOIN:

SELECT o.order_id, p.product_name, oi.quantity, p.product_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.customer_name = 'Alice';

The JOIN is the “cost” of normalization — you trade query complexity for data integrity. In Day 1 you learned that indexes make these JOINs fast. That’s why normalization and indexing go together: normalize for safety, index for speed.

When to denormalize

Normalization is the default. But sometimes you intentionally break it — for read performance.

Example: an analytics dashboard shows “top 10 customers by total spend.” With normalized tables, this query JOINs orders + order_items + products + customers and SUMs across millions of rows. Slow.

Solution: create a materialized view or a denormalized summary table that pre-computes customer_id | total_spend. Now the dashboard reads one table. No JOINs. Fast.

The tradeoff:

• Normalized: fast writes (update one row), slow reads (JOINs), no redundancy, no anomalies.
• Denormalized: slow writes (update multiple copies), fast reads (no JOINs), redundancy, must manage anomalies manually (triggers, jobs).

This is the read-write tradeoff — the same tradeoff you saw in Day 1 (indexes speed up reads but slow down writes). Normalization is the schema-level version of that tradeoff.

The transfer question

From Day 1 → Day 10 → Day 13 — click to reveal

Normalization (Day 13) splits data into multiple tables to eliminate anomalies — each fact stored once. This creates the need for JOINs, which are slow without indexes (Day 1). Indexes on foreign keys (like customer_id in orders) make JOINs fast. And when concurrent transactions modify these normalized tables, transactions (Day 10) ensure the modifications are atomic — if an order fails halfway, neither the order nor the inventory is corrupted.

The full picture: normalize for integrity → index for read speed → transact for write safety. Each builds on the previous.

Quiz