Normalization

After this lesson you can

• Spot repeated data and the update, insert and delete problems it causes
• Apply the first, second and third normal forms to a table design

Key takeaways

• Repeated data causes problems: an update missed in one copy, an insert that must wait for unrelated data, a delete that takes other facts with it.
• First normal form (1NF) means one value per cell, no repeating columns such as Pet1 and Pet2, and a primary key for every row.
• Second normal form (2NF) means every column depends on the whole key, so ProductName belongs in Products, not OrderDetails.
• Third normal form (3NF) means no column depends on another non-key column, so an owner's phone belongs in Owners, not Pets.
• Third normal form is the usual goal. A deliberate copy, such as an order line's UnitPrice at the time of the order, is a different fact, not a mistake.

Normalization is the formal version of common sense design: a set of rules for splitting data into tables so that each fact is stored in exactly one place.

The trouble with one big table

Say the vet's office kept its visits in a spreadsheet:

Maria's phone number is written three times; so is Biscuit's species. That repetition causes three kinds of problem:

Normalization removes the repetition, and the problems with it.

First normal form (1NF): one value per cell

A table is in first normal form when:

• every column holds one value, never a list (no Pets: Biscuit, Pepper in one cell),
• there are no repeating columns (no Pet1, Pet2, Pet3),
• and every row can be told apart by a primary key.

A cell with a list can't be searched, counted or joined properly. Give each value its own row instead.

Second normal form (2NF): the whole key

A table is in second normal form when it's in 1NF and every column depends on the whole primary key, not just part of it. This matters when the key is made of two columns.

OrderDetails has the key OrderId + ProductId. Quantity depends on both: it's how many of this product on this order. But a ProductName column would depend on ProductId alone, and be repeated on every order that includes the product. So ProductName belongs in Products, not in OrderDetails.

Third normal form (3NF): nothing but the key

A table is in third normal form when it's in 2NF and no column depends on another non-key column.

If Pets held OwnerId and OwnerPhone, the phone would depend on the owner, not on the pet. Two pets of the same owner would repeat the phone. So the phone belongs in Owners, and Pets keeps only the OwnerId.

A common way to remember the three: every column depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF).

The spreadsheet, normalized

Applying the rules to the visits spreadsheet gives the four PetClinic tables from the last lesson:

Maria's phone is now in one row of Owners: change it once, and every visit sees the change. A new vet is one INSERT into Vets. Deleting a visit leaves the pet and its owner alone. And a join brings the spreadsheet's view back whenever you want it.

How far to go

There are higher normal forms, but third normal form is the usual goal for an app's database. Sometimes a designer deliberately keeps a copy of a value, such as an order line's UnitPrice, which records the price at the time of the order even after the product's price changes. That's a decision about meaning, not a mistake: the two prices are different facts.

Check yourself

2 questions to try on the lesson page.

So what

Each fact in one place: that's all normalization asks. Follow the three forms, and updates happen once, inserts never wait for unrelated data, and deletes never take other facts with them.

Key terms

• Normalization
• Update Anomaly
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)

Cheat sheets

• Normalization