Database Normalisation Explained: 1NF, 2NF, 3NF with UK Examples

Most messy databases were tidy once. A small shop in Leeds starts with a single spreadsheet: orders, customers and products on one sheet. It works for six months. Then someone needs to know which postcodes buy the most tea, and someone else edits a customer's address and quietly creates a second version of them. Normalisation is how you stop that happening — not through cleverness, but by applying three rules in order.

Why a flat orders table breaks

Picture a table called orders:

• order_id, order_date
• customer_id, customer_name, customer_email, customer_address, postcode
• items — a text field such as "Kettle x1, Toaster x2"
• total_gbp

The customer's name and address repeat on every order they place. That wastes space, but the real cost is behavioural. Change an address and you must remember every row it appears in. Record a new product and you cannot, until somebody orders it. Delete a customer's final order and you lose the customer entirely.

Every non-key column should depend on the key, the whole key, and nothing but the key.

That sentence is the whole of 1NF, 2NF and 3NF in one line. The rest is working out what it means for your tables.

First normal form: one value per cell

1NF asks for three things: each column holds a single atomic value, there are no repeating groups of columns, and each row is uniquely identifiable by a primary key.

The items column fails immediately. "Kettle x1, Toaster x2" cannot be counted, filtered or joined. Move it into its own table, order_items, with one row per product per order. The same applies to columns called item_1, item_2 and item_3 — repeating columns are just a repeating group wearing a disguise, and they force you to redesign the table the moment someone orders four things.

The address column needs attention too. A single cell holding "14 Otley Road, Leeds LS16 5JX" is not atomic.

A note on UK addresses

Addresses are genuinely awkward. A property can have a building name rather than a number, a flat number, a sub-premises, and a postcode covering a handful of doors. Splitting into line_1, line_2, town and postcode is usually enough for delivery. Don't try to model every quirk of UK addressing — 1NF asks that each field holds one value, not that you rebuild the Postcode Address File.

Second normal form: depend on the whole key

2NF only bites when a table has a composite primary key. order_items is keyed on (order_id, product_id). If you also store the product name and category there, those columns depend on product_id alone — half the key. That is a partial dependency, and it means the product name is repeated on every order line and must be corrected everywhere when it changes.

Move them into a products table keyed on product_id. What remains in order_items is only what genuinely describes the pairing: quantity, and the unit price charged. If your tables all have single-column keys, they are in 2NF the moment they are in 1NF — nothing to do.

Third normal form: nothing but the key

3NF removes transitive dependencies: cases where a non-key column depends on another non-key column rather than on the key. If orders holds customer_id alongside customer_name and customer_email, those details follow from the customer, not from the order. Someone changes their surname and you have to update every historic order. Pull them out into customers and reference them by customer_id.

The same pattern shows up in products: storing supplier_name beside supplier_id means a supplier rebrand has to be applied product by product. Suppliers get their own table.

Derived values deserve a mention. A total_gbp column holding the sum of the line totals repeats a calculation you can always perform. If you keep it for speed, treat it as a deliberate exception and write down exactly when it is refreshed — a trigger, or a job that runs after dispatch. Undocumented calculated columns drift, and nobody trusts them again.

What the finished schema looks like

• customers — customer_id (PK), full_name, email, marketing_opt_in
• addresses — address_id (PK), customer_id (FK), line_1, line_2, town, postcode
• orders — order_id (PK), customer_id (FK), delivery_address_id (FK), order_date, status
• order_items — order_id (FK), product_id (FK), quantity, unit_price_gbp
• products — product_id (PK), product_name, category_id (FK), supplier_id (FK)
• suppliers — supplier_id (PK), supplier_name, contact_email

Notice that unit_price_gbp stays on order_items on purpose. It is the price at the time of the order, which is not the same thing as the current price in products. The same logic applies to VAT: rates change, and an invoice reprinted next year should show the rate that applied when it was issued, not today's.

When to stop normalising

3NF is a sensible default for anything that takes payments or records events. Reporting is a different job. An analyst building a monthly sales dashboard will happily join six tables once and write the result to a wide summary table, because a seven-join query on every page load is a poor use of everyone's afternoon.

That is denormalisation by design, not a mistake. A star schema, a materialised view or a nightly aggregate are all fine. The rule worth keeping is simple: normalise the source of truth, denormalise the copies you read from, and make sure every copy can be rebuilt from scratch. The day a summary table starts accepting edits is the day you have two truths.

The opposite failure is just as common. Splitting a status field into its own lookup table, then that lookup into a category table, adds joins and onboarding time for no gain. If a column has four values that never change, a text field with a constraint is honest and readable.

A practical checklist before you build

1. Give every table a primary key. If you cannot, you are probably looking at a repeating group.
2. Check no cell holds a list. Split it into rows, never into numbered columns.
3. For composite keys, ask whether each column describes the whole key or just part of it.
4. Look for columns that follow from another non-key column, and move them to a table where they are the key.
5. Either drop derived values or document how they are kept in sync.
6. Write one insert, one update and one delete by hand. If any of them needs a second statement you did not expect, something still needs normalising.
7. Remember that keeping a customer's name and address in exactly one place also makes an erasure request far easier to handle — worth a quick word with whoever owns data protection in your business.

Take one real table from a system you work with and run it through those seven steps on paper. It is an afternoon's work. Untangling duplicate customer records in eighteen months is not.

Photo: Zulfugar Karimov / Pexels