Skip to content
Velloreth
Programming 6 min read

Database Normalization Explained: 1NF to 3NF

Normalization removes redundancy and update anomalies. Learn the normal forms, the trade-offs, and when to denormalize deliberately.

By

Why normalize

Storing the same fact in several places creates three problems.

An update anomaly. The fact changes, and one copy is missed, so the data is inconsistent.

An insert anomaly. A fact cannot be recorded without recording an unrelated fact.

A delete anomaly. Deleting one fact removes another that is still needed.

Normalization stores each fact once, which removes all three.

The unnormalized table

orders
id | customer_name | customer_email    | items
1  | Alice         | alice@example.com | widget, gadget
2  | Alice         | alice@example.com | thing

The problems are visible:

  • The customer’s email is repeated, so a change must update every row.
  • The items column holds a list, so a query cannot filter on a single item.
  • A customer with no order cannot be recorded, because the table is about orders.

First normal form

Every column holds a single atomic value, and there are no repeating groups.

The items column violates it. The fix is a separate table.

orders
id | customer_id
1  | 1
2  | 1

order_items
order_id | item
1        | widget
1        | gadget
2        | thing

Now a query can filter on a single item, and the number of items is not fixed.

A comma-separated list in a column is the classic 1NF violation. It seems convenient and it makes every query harder.

Second normal form

The table is in 1NF, and every non-key column depends on the whole primary key.

This only applies to a composite key.

order_items
order_id | product_id | product_name | quantity
1        | 10         | Widget       | 2
1        | 11         | Gadget       | 1

The primary key is (order_id, product_id). The product_name depends only on product_id, not on the whole key. That is a partial dependency.

The fix is to move the product data to its own table.

products
id | name
10 | Widget
11 | Gadget

order_items
order_id | product_id | quantity
1        | 10         | 2
1        | 11         | 1

Now the product name is stored once, and a rename updates one row.

Third normal form

The table is in 2NF, and no non-key column depends on another non-key column.

orders
id | customer_id | customer_city | customer_country
1  | 1           | Kathmandu     | Nepal

The customer_city depends on customer_id, which is not the key. That is a transitive dependency.

The fix is to move the customer data to its own table.

customers
id | name  | city      | country
1  | Alice | Kathmandu | Nepal

orders
id | customer_id
1  | 1

Now the city is stored once per customer, and a customer who moves is one update.

Beyond 3NF

BCNF (Boyce-Codd) is a stricter 3NF that handles a case where a non-key column determines part of a candidate key.

4NF removes a multi-valued dependency.

5NF removes a join dependency.

In practice, 3NF is the target for most operational databases, and the higher forms are rarely needed. The value of knowing them is in recognising the dependency that causes the anomaly.

The trade-off

Normalization is not free.

More tables means more joins. A query that reads an order with its customer and products joins three tables.

A join costs time. For a read-heavy workload, the join can dominate the query.

The schema is more complex. More tables, more foreign keys, more to understand.

The benefit is correctness. The data cannot become inconsistent, because each fact is stored once.

Denormalization

Denormalization is a deliberate trade of redundancy for read speed.

A cached aggregate. Store the order total on the order, rather than summing the items on every read.

ALTER TABLE orders ADD COLUMN total_cents INTEGER;

A duplicated column. Store the customer name on the order, so a report does not join.

A materialised view. Precompute a complex query and refresh it periodically.

A search index. A separate structure optimised for search, kept in sync with the source.

The risk: the copies can diverge. A denormalized column must be updated whenever the source changes, and a missed update is a silent inconsistency.

The rule: denormalize for a measured performance problem, not in anticipation of one. And when you do, make the update path explicit and test it.

Normalization and NoSQL

A document store is often not normalized, and the reason is the access pattern rather than a preference. A document holds the data a query needs, which avoids a join, and the cost is that a fact stored in several documents must be updated in all of them. This is the update anomaly, accepted deliberately in exchange for a single read that returns everything. The same trade appears in a relational database when a team denormalizes a column for speed: the redundancy is the price of avoiding a join on a hot path. What matters is that the trade is a decision rather than an accident, and that the update path is explicit and tested. A denormalized column that is updated in one code path and not another is a silent inconsistency that surfaces as a wrong number in a report months later.

A document holds the data a query needs, which avoids a join.

{
  "id": 1,
  "customer": { "name": "Alice", "city": "Kathmandu" },
  "items": [
    { "product": "Widget", "quantity": 2 }
  ]
}

The advantage: one read returns everything, with no join.

The disadvantage: the customer name is duplicated across every order, so a rename must update every document. This is the update anomaly, accepted deliberately.

The trade is the same, just made in the other direction: the document model optimises for the read and accepts the redundancy.

Practical guidance

  • Normalize to 3NF for an operational database.
  • Avoid a comma-separated list in a column.
  • Move a partial dependency to its own table.
  • Move a transitive dependency to its own table.
  • Denormalize deliberately, for a measured problem.
  • Make the update path explicit when you denormalize.
  • Remember the trade: normalization is correctness, denormalization is speed.
  • Test the consistency of a denormalized column.

For the related guides, see sql-joins-explained and database-indexing-explained. For the broader context, sql-vs-nosql-databases and database-migrations-guide.

Quotable summary

Normalization removes redundancy, which prevents update, insert, and delete anomalies, because each fact is stored once. First normal form requires atomic values and no repeating groups, so a comma-separated list in a column violates it. Second normal form removes a partial dependency on part of a composite key, and third normal form removes a transitive dependency on a non-key column. Denormalization is a deliberate trade of redundancy for read speed, and it should be a decision rather than an accident, because the copies can diverge and a missed update is a silent inconsistency. Normalize to 3NF for an operational database, and denormalize for a measured performance problem rather than in anticipation of one.

Frequently asked questions

What is an update anomaly?
The same fact is stored in several rows, so an update to one leaves the others stale. Normalization stores the fact once.
What is first normal form?
Every column holds a single atomic value, and there are no repeating groups. A column holding a comma-separated list violates it.
What is second normal form?
The table is in 1NF, and every non-key column depends on the whole primary key, not part of it. It only applies to a composite key.
What is third normal form?
The table is in 2NF, and no non-key column depends on another non-key column. A transitive dependency is removed by moving the column to its own table.
Should I always normalize to 3NF?
No. 3NF is a good default for an operational database. A read-heavy analytical query may benefit from denormalization, and a document store may not be normalized at all.
What is denormalization?
Deliberately storing redundant data to avoid a join or a computation. It speeds up a read and adds the risk of the copies diverging.