Normalization, Gently

One fact, one place

Normalization is the practice of structuring tables so each fact is stored exactly once. It is the principle behind everything in this unit, and it comes with a simple test: count the rows you would have to edit if one fact changed. The answer should be one.

Here is a library table that fails the test:

Ana's email is stored twice, once per loan. Update it on one row and forget the other, and the table now contradicts itself: two different addresses for one person, with nothing to indicate which is current. This is called an update anomaly.

The trouble grows with the data. A member with forty loans has forty copies of their email, and every one of them is an opportunity for the copies to disagree.

The next block commits that exact crime so the damage is visible.

The update anomaly in action

The UPDATE changes Ana's email on her Dune row only, and the final SELECT shows her with two conflicting addresses. The table now contradicts itself.

CREATE TABLE loans (
  member TEXT,
  email TEXT,
  book TEXT
);

INSERT INTO loans VALUES ('Ana', 'ana@old.com', 'Dune');
INSERT INTO loans VALUES ('Ana', 'ana@old.com', 'Foundation');
INSERT INTO loans VALUES ('Ben', 'ben@x.com', 'Dune');

-- Ana changes her email, but the update only catches one row:
UPDATE loans SET email = 'ana@new.com'
WHERE member = 'Ana' AND book = 'Dune';

SELECT member, email, book FROM loans WHERE member = 'Ana' ORDER BY book;

Output

Ana|ana@new.com|Dune
Ana|ana@old.com|Foundation

Nothing errored. The database has no idea that these two email values were supposed to agree, because as far as the schema is concerned they are unrelated cells in unrelated rows.

Worse, there is now no way to tell which address is current. Both look equally plausible, and a mail merge over this table sends half of Ana's notices into the void. The next section removes the possibility rather than trying to remember to update both rows.

The normalized fix

Split by thing: members are one thing, loans are another.

• members(id, name, email): each person once.
• loans(member_id, book): each loan points at a member (one-to-many, lesson 5-1).

Now Ana's email lives in exactly one row. Updating it once fixes it everywhere, because every loan points at the same member row instead of carrying its own copy. The cost is that reading requires a JOIN, and that is the trade: normalized data is a little more work to read and much safer to write.

You do not need the formal jargon yet (first normal form, second, third). For a working engineer the instinct is: repeating the same value down a column that describes some other entity is a smell. Split the table.

One UPDATE, every loan corrected

With the email stored once, a single statement fixes it everywhere, and the join proves that both loans see the new address.

CREATE TABLE members (
  id INTEGER PRIMARY KEY,
  name TEXT,
  email TEXT
);

CREATE TABLE loans (
  member_id INTEGER,
  book TEXT
);

INSERT INTO members VALUES (1, 'Ana', 'ana@old.com');
INSERT INTO members VALUES (2, 'Ben', 'ben@x.com');

INSERT INTO loans VALUES (1, 'Dune');
INSERT INTO loans VALUES (1, 'Foundation');
INSERT INTO loans VALUES (2, 'Dune');

UPDATE members SET email = 'ana@new.com' WHERE id = 1;

SELECT members.name, members.email, loans.book
FROM loans
JOIN members ON members.id = loans.member_id
WHERE members.name = 'Ana'
ORDER BY loans.book;

Output

Ana|ana@new.com|Dune
Ana|ana@new.com|Foundation

The email lives only in members, so UPDATE members SET email = ... WHERE id = 1 is the whole change. Both loan rows report the new address because they join to the same single member row, and there is no second copy left to fall out of step.

The WHERE clause still matters, as lesson 7-2 warned. Dropping it would give Ben the same address. The difference is that the normalized version cannot produce a contradiction, only a mistake you can see and fix in one place.

Product prices repeated on every order

An orders table that stores product_name and product_price on every row hits the same anomaly as Ana's email. Update all the rows when a price rises and old orders change retroactively, update some and the rows disagree with each other.

The normalized fix is a products table holding each product once, with orders pointing at it through a product_id foreign key. The price then exists in exactly one place.

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  price INTEGER CHECK (price > 0)
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_id INTEGER REFERENCES products(id)
);

Real shops often copy the price paid onto the order row on purpose, as a historical snapshot of what the order cost at the time. That is a genuine second fact, the price of this sale, rather than a duplicate of the current catalog price. Normalization is the default, and breaking it is fine when you can name the reason.