Database Normalization Explained: 1NF, 2NF and 3NF with Simple Examples

Normalization is a method for organising tables in a relational database so that each fact is stored in exactly one place. It was introduced by Edgar F. Codd, the inventor of the relational model, and it remains the foundation of good database design.

Why it matters: anomalies

Imagine a small shop tracking orders in one wide table:

This design causes three classic problems:

• Update anomaly — when Mona changes her phone number, every one of her orders must be updated. Miss one and the data contradicts itself.
• Insert anomaly — you cannot record a new city's shipping fee until someone from that city places an order.
• Delete anomaly — delete Karim's only order and you lose the Alexandria shipping fee too.

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. The products column holding "Mouse, Keyboard" breaks this — you cannot reliably search, count or price individual products. Fix it by giving each product in an order its own row:

Second Normal Form (2NF): depend on the whole key

A table is in 2NF when it is in 1NF and every non-key column depends on the entire primary key — relevant when the key has more than one column. In an order_items table keyed by (order_id, product_id), the product's name depends only on product_id, not on the order. So product details move to their own table:

• products (id, name, current_price)
• order_items (order_id, product_id, quantity, unit_price)

Note that unit_price stays in order_items: it records the price at the time of the order, which is a different fact from today's price.

Third Normal Form (3NF): no facts about other facts

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column (no transitive dependencies). In our orders table, customer_phone depends on the customer, not the order, and city_shipping_fee depends on the city. Each moves to its own table:

CREATE TABLE cities (
  id INT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  shipping_fee DECIMAL(10,2) NOT NULL
);
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(30),
  city_id INT REFERENCES cities(id)
);
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id),
  created_at TIMESTAMP NOT NULL,
  shipping_fee DECIMAL(10,2) NOT NULL  -- fee charged on this order
);

Now Mona's phone lives in one row, each city's fee in one row, and nothing is lost when an order is deleted. A common summary of 3NF is that every non-key column must depend on "the key, the whole key, and nothing but the key".

Beyond 3NF

Boyce–Codd Normal Form (BCNF) is a slightly stricter version of 3NF that handles some edge cases with overlapping candidate keys. Higher forms (4NF, 5NF) deal with more unusual multi-valued dependencies. For most business applications, a careful 3NF design is the practical target.

When to denormalize

Normalization optimises for correctness. Sometimes you deliberately duplicate data for speed or simplicity:

• Historical snapshots — copying the price or address into an order, because it must not change when the product or customer changes later.
• Reporting and analytics — data warehouses often use denormalized "star schemas" designed for fast aggregation.
• Read-heavy hot paths — storing a pre-computed count or total that would otherwise require expensive joins.

Denormalize deliberately, document why, and have a clear mechanism (transactions, triggers or background jobs) to keep copies consistent.

Key takeaways

• Normalization stores each fact once, preventing update, insert and delete anomalies.
• 1NF: atomic values. 2NF: depend on the whole key. 3NF: no dependencies between non-key columns.
• Aim for 3NF in transactional systems; denormalize only for clear, documented reasons.

How Wintecsys can help

We design clean, well-documented data models for new applications, and help businesses restructure spreadsheets and messy legacy databases into reliable systems.

See our software services or discuss your data.