Database normalization for people who aren't engineers

Normalization is taught with terms like "first normal form" and "transitive functional dependency", which is why most people who would benefit from it never learn it.

Strip the vocabulary away and there is one rule:

Every fact should live in exactly one place.

That is it. Everything else is a technique for achieving it.

Why one place matters

If a fact lives in two places, those places can disagree. Not might — will. Someone updates one and not the other, and now your data contains a contradiction with no way to tell which side is right.

A spreadsheet with the client's address on every order looks convenient. Then the client moves. You update the recent orders. The old ones still show the old address. Six months later, nobody can tell which orders had the address updated and which are simply historical.

That is the entire failure mode, and it is responsible for most "our data is a mess" complaints.

The three moves

You do not need the formal normal forms. You need three habits.

1. One row per thing

Each row should describe exactly one thing, and each column should hold exactly one value.

Broken:

You cannot filter by contact, email one of them, or record that Jane left. The Contacts cell holds three facts crammed into text.

Fixed: a Contacts table, one row per person, each linked to a client.

The tell is a cell containing commas, slashes, or line breaks. Also the numbered-column disguise — Contact 1, Contact 2, Contact 3 — which is the same problem spread sideways. Whenever you see numbered columns, a table is trying to be born.

2. Facts belong to their own subject

Every column should describe the thing the row is about — nothing else.

Broken: an Orders table with Client name, Client address, Client phone.

Those are facts about the client, sitting in a row about an order. They will be repeated on every order and will drift.

Fixed: Orders holds a link to the client. Client details live on the client record and are read through the link as lookups.

The test: "Is this a fact about the thing this row represents, or about something it points at?" If it is about something it points at, move it.

3. Don't store what you can calculate

Broken: an Orders table with a manually typed Total column.

It is right the day it is typed and wrong the moment a line item changes. Now you have two answers to "what is this order worth?" and no way to know which is current.

Fixed: a rollup that sums the line items. It cannot be stale, because it is not stored.

The exception that matters

Rule 3 has one important exception, and getting it wrong causes real financial errors.

Historical values must be frozen.

When an order is placed at £40 and the product later costs £45, the order must still say £40. A lookup would show £45 — current truth, wrong answer.

So: copy the price onto the line item at the moment of sale. That looks like duplication, and it is — deliberately. The line item's price is not "the product's price". It is "the price this thing sold for on this date", which is a genuinely different fact that deserves its own home.

The general principle: if a value is a snapshot of a moment, store it. If it is current truth, link to it.

This applies to prices, exchange rates, tax rates, addresses on issued invoices, and job titles on signed contracts.

How far to go

You can over-normalize. Splitting every repeated string into a lookup table produces a model that is technically pure and miserable to use — six clicks to see one record.

A practical stopping point:

Make it a table when the thing has attributes you care about, or when you need to link other things to it. A client has a region, an owner and a payment term → table. A supplier has a lead time → table.

Leave it as a select field when it is just a label. Status, priority, category, T-shirt size. These have no attributes of their own, and a single-select already prevents typos, which was the main risk.

Leave it as plain text when it is genuinely free-form. Notes, descriptions, addresses you will never analyse.

The question is not "is this text repeated?" It is "does this thing have properties, or is it just a word?"

Recognising an un-normalized model

Symptoms, in rough order of severity:

• The same name appears in several tables, typed rather than chosen.
• Numbered columns: Item 1, Item 2, Item 3.
• Cells containing comma-separated lists.
• A column whose meaning depends on another column ("Value — in the currency in column F").
• Totals maintained by hand.
• Updating one real-world fact requires editing several places.
• Two reports disagree and both are technically reading real data.

Any one of these is worth fixing. Three or more means the model, not the data, is the problem.

Normalizing an existing mess

You do not need a rewrite. Work outside-in:

1. List every distinct real-world noun mentioned anywhere in your sheet. Clients, products, people, suppliers, sites.
2. Give each noun a table with one row per real thing. Deduplicate as you go — this is where you discover you have four spellings of one client.
3. Replace typed names with links. This is the step that stops future drift.
4. Move misplaced facts. Client address off the order, onto the client.
5. Split multi-value cells into linked rows.
6. Replace hand-maintained totals with rollups.
7. Freeze the historical values on purpose — prices as sold, rates as applied.

Do it for one workflow at a time. Attempting the whole business at once is the reliable way to abandon the project.

Why this is worth the effort

A normalized model is not tidier for its own sake. It has three properties yours probably lacks:

• It cannot contradict itself. One fact, one place, no disagreement possible.
• It answers questions you did not plan for. Because the relationships are real, new questions are queries rather than projects.
• It survives change. Renaming a client, adding a second contact, or splitting a region is a small edit rather than a migration.

The formal theory goes considerably deeper, and for a fifteen-person team it does not need to. "Every fact in exactly one place, except snapshots, which are frozen on purpose" will get you most of the way.

Where to go next

• One-to-many vs many-to-many — getting relationship shape right.
• What is a junction table — the structure for the hard case.
• Why every table needs a stable ID — the identity problem underneath all of this.