Database normalisation

What is database normalisation?

Database normalisation is the practice of organising data into related tables so each fact is stored once. Customers live in a customer table. Products live in a product table. Orders refer to those tables through identifiers.

The problem it solves is repetition. Imagine an order table where every order line stores the full customer address. If the customer moves, hundreds of rows need to change. Miss a few rows and the database now tells two stories about the same customer.

In a normalised design, the address lives once in the customer table. Order lines store the customer id. Change the address once and every future lookup uses the same value.

The idea comes from the relational model introduced by Edgar F. Codd at IBM. The rules are called normal forms, and they are still part of everyday database design.

The first three normal forms in plain English

First normal form: one value per field. Do not store several phone numbers in one cell, and do not create columns such as Product1, Product2, and Product3. Repeating groups belong in a separate table with one row per value.

Second normal form: every field depends on the whole key. If an order-line table has a combined key of order id and product id, the product name belongs to the product table, not on every order line.

Third normal form: fields should not depend on other non-key fields. If a student table stores both the tutor and the tutor's office number, the office number depends on the tutor, not on the student. Move tutor details to their own table.

There are stricter forms, including Boyce-Codd normal form, but the first three cover most practical business systems.

Why OLTP systems are normalised

Operational systems handle many small writes: new orders, payment status updates, stock movements, address changes, support tickets, and invoices. This workload is called OLTP, or online transaction processing.

For that work, normalisation is a good fit. A change touches one place. The database can enforce relationships. A transaction can reject an order for a customer id that does not exist. Storage is usually smaller too, although consistency is the real win.

That is why CRM, ERP, webshop, payroll, and booking systems are usually built around normalised relational databases.

Normalisation and denormalisation

Reporting has different needs. An analytical query often reads millions of rows, groups them by month, product, customer segment, or region, and writes nothing back. Too many joins make those queries slower and harder for analysts to understand.

That is why analytics layers often denormalise data on purpose. A star schema repeats descriptive values in dimension tables so reports can read quickly and users can navigate the model. Dimensional modelling accepts some redundancy because the goal is analysis, not transaction entry.

The two designs can both be right. Keep the operational source normalised for safe writes. Build a denormalised warehouse, mart, lakehouse gold layer, or Power BI semantic model for reporting.

Example: customers and orders

A spreadsheet might start with one row per order line:

order id, order date, customer name, customer address, product name, product category, quantity, price

That works until customer names, addresses, product categories, or prices change. A normalised version splits the data into customers, products, orders, and order lines. The order line keeps the quantity and price for that sale, and points to the order and product.

For reporting, you might then build a star schema again: one sales fact table with customer, product, date, and region dimensions around it. The analytics model is intentionally wider and easier to read.

When to normalise

Normalise when people or applications create and update records all day. Customer master data, product catalogues, stock, orders, invoices, subscriptions, HR records, and accounting entries all need consistency.

Denormalise when the main job is reading and summarising. Management reporting, dashboards, historical analysis, and self-service BI usually belong in a star schema or another analytical model.

A useful rule of thumb: normalise where data is written, denormalise where data is read.

What to watch out for with normalisation

Over-normalisation creates friction. Splitting every tiny lookup into its own table can make simple queries unreadable and slow. Be strict where facts change often, and pragmatic where values are stable.

Reporting on a highly normalised source gets painful. A report that needs twenty joins for one chart is hard to maintain and can load the operational system. Use an analytical copy when reporting becomes important.

Names still need governance. Normalisation removes duplicate storage, but it does not decide what a customer, active product, or cancelled order means. Definitions still need ownership.