Normalization | Relational Databases

In this chapter

We'll trace a duplicated phone number back to Act 1's original 'conflicting numbers' problem — meeting 1NF, 2NF, and 3NF, and understanding exactly why GreenMart's real schema was already designed correctly.

16–19 min

The Problem in Real Life

Sarah pulls up an old order to double-check a delivery number and finds something wrong: the phone number on the order doesn't match Priya's number in Customers anymore. Neither number is technically incorrect — they're just from two different moments in time, and nobody ever decided which one was supposed to be true.

She traces it back to a shortcut from when the website first launched: someone copied a customer's phone number straight into every order, instead of just pointing at the customer's own row and looking it up whenever it was actually needed.

There are two answers for the same fact again. I thought we fixed this back in chapter 1.

Sarah

Copying a Fact vs. Pointing at It Once

The same old problem, a new table

A fact duplicated into two places can disagree with itself, whether that's a notebook page or a database column.

A foreign key already solves this — if nothing else duplicates it

CustomerID already points at the one true phone number; copying it elsewhere just creates a second copy that can go stale.

Not all repetition is a mistake

A receipt storing the price actually paid is a deliberate, correct exception — normalization is a discipline, not an absolute rule.

Nothing in the database flags a normalization problem on its own

Two disagreeing copies of the same fact don't throw an error — they just quietly coexist until someone happens to notice.

What Does Normalization Actually Fix?

Normalization is the discipline of organizing tables so every fact lives in exactly one place — never copied, never duplicated, never able to drift out of sync with itself. GreenMart's hypothetical Orders shortcut broke this the moment it started storing a customer's phone number directly, right alongside the CustomerID that already points at the one true copy.

Normalization is usually described as a series of normal forms — 1NF, 2NF, 3NF — each one fixing a specific, named kind of redundancy. Here's each one, alongside the exact query that proves it:

Copying CustomerPhone directly into a hypothetical Orders design means the exact same fact — Priya's phone number — now lives in two places that can independently drift apart.

The Problem — a Duplicated Fact, Now Two Different Answers

CREATE TABLE OrdersBad (
  OrderID INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL,
  CustomerPhone TEXT,
  OrderDate DATE NOT NULL
);

INSERT INTO OrdersBad VALUES (301, 101, '555-0142', '2026-08-29');

-- Priya gets a new phone number
UPDATE Customers SET Phone = '555-0199' WHERE CustomerID = 101;

-- OrdersBad still shows her OLD number -- nobody updated it here too
SELECT o.CustomerPhone AS OrderRecordSays, c.Phone AS CustomersTableSays
FROM OrdersBad o JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID = 301;

OrderRecordSays and CustomersTableSays now disagree, and nothing in the database itself knows or cares — this is exactly the "three conflicting numbers" problem from chapter 1, back again in a new table.

1NF requires every column to hold a single, atomic value — never a list crammed into one field.

First Normal Form (1NF) — No Repeating Groups

-- What 1NF forbids: a single column holding multiple values, like
-- OrderID 301 | Products = 'Apples, Milk' crammed into one field.
-- The real OrderItems table already avoids this -- one row per product:
SELECT * FROM OrderItems WHERE OrderID = 301;

GreenMart's real OrderItems table has satisfied this since Act 3 without anyone naming it: one row per product per order, never a comma-separated list.

2NF applies once a table has a composite key, and requires every other column to depend on the whole key — not just part of it.

Second Normal Form (2NF) — No Partial-Key Dependency

-- What 2NF forbids: storing ProductName directly in OrderItems, since it
-- would depend only on ProductID, not on the whole (OrderID, ProductID)
-- pairing. The real design leaves it out and looks it up instead:
SELECT oi.OrderID, oi.Quantity, p.Name
FROM OrderItems oi
JOIN Products p ON oi.ProductID = p.ProductID
WHERE oi.OrderID = 301;

A ProductName column sitting in OrderItems would depend only on ProductID, half of the (OrderID, ProductID) pairing — the real table sidesteps this entirely by not storing it there at all.

3NF requires every non-key column to depend directly on the primary key — not on another non-key column. CustomerPhone depends on CustomerID, which is already a foreign key; Phone has no business being a column of Orders at all.

Third Normal Form (3NF) — No Transitive Dependency

-- The fix for query 1's problem: don't store CustomerPhone in Orders at
-- all -- look it up through the foreign key whenever it's actually needed.
SELECT o.OrderID, c.Phone AS CurrentPhone
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderID = 301;

This always shows Priya's current phone number, pulled fresh from the one place it actually lives — there's no second copy anywhere left to go stale.

Notice what the fix actually was: not adding anything new, just removing a column that never needed to exist in Orders at all, and relying on the foreign key to look up the current truth whenever it's needed. GreenMart's real Orders table has worked this way since Act 3's schema evolution chapter — this chapter is really about understanding why that original design decision was already correct.

Normalization isn't a rule to follow blindly, though. A receipt that stores the price a customer actually paid, even after GreenMart changes that product's price later, is a deliberate, informed exception — the receipt is describing a historical fact, not looking up a current one. The difference between a real redundancy bug and a deliberate design choice is exactly whether anyone actually decided it on purpose.

Key Takeaway

Normalization means every fact lives in exactly one place — 1NF removes repeating groups, 2NF removes partial-key dependencies, 3NF removes transitive dependencies — but a deliberate, well-reasoned exception is different from an accidental redundancy nobody decided on purpose.

Why This Matters

Every update anomaly this course has fought since chapter 1 — a notebook with conflicting numbers, an order that half-happened, an oversold item — traces back to the same root idea: a fact that exists in more than one place can disagree with itself. Normalization is the formal name for the discipline that prevents that from ever being possible in the schema itself.

GreenMart's schema is sound, and now Sarah actually knows why. Everything this Act has covered — race conditions, transactions, isolation, and now normalization — comes together in the checkpoint next.