Database Normalization Explained with Examples

Many business databases start life as a spreadsheet: one wide sheet where each row holds a customer, an order and the products on it. It works until someone updates a customer's phone number in one row but not the other forty. Database normalization is the process of organising data into tables so that each fact is stored once, in the right place. This article walks through it with a single running example, from a messy sheet to a clean design, and then explains when to bend the rules.

The starting point: one big table

Here is a simplified order sheet from a small stationery wholesaler:

This sheet has three classic problems, known as anomalies:

• Update anomaly. If Asha Traders moves to Mumbai, you must change every one of their rows. Miss one and the data contradicts itself.
• Insert anomaly. You cannot record a new product, or a new customer, until someone orders it.
• Delete anomaly. Delete order 1002 and you lose the only record that Ravi Stores exists.

Normalization removes these by applying a series of rules called normal forms. In practice, most business systems aim for the third normal form.

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

A table is in 1NF when every column holds a single, atomic value and there are no repeating groups. Our products and prices columns break this rule: they hold lists packed into text. You cannot easily ask "how many pens did we sell?" without parsing strings.

The fix is one row per order line, with quantity in its own column:

Each row is now uniquely identified by the combination of order_no and product. That combination is the table's composite key. But we have made duplication worse: order date and customer now repeat on every line.

Second normal form (2NF): no partial dependencies

A table is in 2NF when it is in 1NF and every non-key column depends on the whole key, not just part of it. Here, order_date and customer depend only on order_no. unit_price depends only on product. Only qty genuinely depends on both.

So we split the table by what each fact depends on:

• orders: order_no, order_date, customer, customer_city
• products: product, unit_price
• order_items: order_no, product, qty

Third normal form (3NF): no transitive dependencies

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. In orders, customer_city depends on customer, not on the order. That is a transitive dependency: order → customer → city. Move customer details into their own table.

The final design, written as SQL:

CREATE TABLE customers (
    id    INT PRIMARY KEY,
    name  VARCHAR(120) NOT NULL,
    city  VARCHAR(80)
);

CREATE TABLE products (
    id          INT PRIMARY KEY,
    name        VARCHAR(120) NOT NULL UNIQUE,
    list_price  DECIMAL(10,2) NOT NULL
);

CREATE TABLE orders (
    id           INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(id),
    order_date   DATE NOT NULL
);

CREATE TABLE order_items (
    order_id    INT NOT NULL REFERENCES orders(id),
    product_id  INT NOT NULL REFERENCES products(id),
    qty         INT NOT NULL,
    unit_price  DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Notice unit_price appears in order_items as well as list_price in products. That is deliberate, not a mistake. The price charged on 2 September is a historical fact about that order. If the list price rises next month, old invoices must not change. Normalization is about storing each fact once, and "the price we charged on this order" is a different fact from "today's price".

All three anomalies are gone. A customer's city lives in one row. New products can be added before anyone buys them. Deleting an order leaves the customer intact.

Database normalization beyond 3NF

Higher forms exist. Boyce–Codd normal form (BCNF) tightens 3NF for tables with overlapping candidate keys, and fourth and fifth normal forms deal with more unusual multi-valued dependencies. They matter in some designs, but a schema carefully taken to 3NF handles the vast majority of business applications well.

When to denormalize

Denormalization means deliberately reintroducing some duplication, usually for read performance. Reasonable cases include:

• Reporting tables that pre-join and pre-summarise data so dashboards do not run heavy joins on every page load.
• Cached totals, such as storing order_total on orders instead of summing lines every time.
• Historical snapshots, like copying the shipping address onto an order so later address changes do not rewrite history.

The rule is to normalize first and denormalize on purpose, with a clear owner for keeping the copies in sync (a trigger, a scheduled job or application code). Denormalizing by accident, because the design was never thought through, is how the original spreadsheet problems creep back. If you are restructuring a legacy system, our database management team works on exactly this kind of redesign, and our software consulting service can review a schema before development begins.

Key takeaways

• Database normalization stores each fact once to prevent update, insert and delete anomalies.
• 1NF: one value per cell. 2NF: depend on the whole key. 3NF: depend on nothing but the key.
• Historical values such as the price charged are separate facts and belong with the transaction.
• Denormalize deliberately for performance, with a clear plan for keeping copies consistent.