# Normalization

Normalization splits data into tables so each fact is stored once. Spot the problems repeated data causes, and apply the first three normal forms.

Journeyman · Database · SQL · Updated November 3, 2026

## 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:

| VisitDate | OwnerName | OwnerPhone | PetName | Species | VetName | Reason | Fee |
|---|---|---|---|---|---|---|---|
| 2026-02-03 | Maria Lopez | (503) 555-0142 | Biscuit | Dog | Dr. Amy Chen | Annual checkup | 65.00 |
| 2026-02-03 | Maria Lopez | (503) 555-0142 | Pepper | Cat | Dr. Raj Patel | Vaccinations | 80.00 |
| 2026-03-11 | Maria Lopez | (503) 555-0142 | Biscuit | Dog | Dr. Raj Patel | Sore paw | 95.50 |

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

| Problem | What goes wrong |
|---|---|
| **Update** | Maria changes her number. Miss one row, and the database now has two numbers for her, and nobody knows which is right. |
| **Insert** | A new vet joins. They can't be added until they've seen a pet, because every row is a visit. |
| **Delete** | Kiwi the parrot's only visit is deleted, and everything known about Kiwi and its owner goes with it. |

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:

| Table | Holds | Key |
|---|---|---|
| `Owners` | Each owner's name, phone and email, once | `OwnerId` |
| `Pets` | Each pet's name, species and birth date, once, and its `OwnerId` | `PetId` |
| `Vets` | Each vet's name, once | `VetId` |
| `Visits` | The date, reason and fee of each visit, with its `PetId` and `VetId` | `VisitId` |

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](https://www.ask-codey.com/glossary/normalization)
- [Update Anomaly](https://www.ask-codey.com/glossary/update-anomaly)
- [First Normal Form (1NF)](https://www.ask-codey.com/glossary/first-normal-form-1nf)
- [Second Normal Form (2NF)](https://www.ask-codey.com/glossary/second-normal-form-2nf)
- [Third Normal Form (3NF)](https://www.ask-codey.com/glossary/third-normal-form-3nf)

## Cheat sheets

- [Normalization](https://www.ask-codey.com/cheat-sheets/foundations-of-database-design-normalization-cheat-sheet)

Source: https://www.ask-codey.com/lessons/foundations-of-database-design-normalization