Skip to content

Normalization

Reduce redundancy and anomalies with 1NF, 2NF, 3NF, and BCNF normal forms.

What you'll learn

  • What normalization is and the problems it solves
  • First Normal Form (1NF) — atomic values and no repeating groups
  • Second Normal Form (2NF) — no partial dependencies
  • Third Normal Form (3NF) — no transitive dependencies
  • When to denormalize for performance

Concept

Why Normalize?

Normalization is the process of organizing tables and columns to reduce data redundancy and eliminate anomalies — insertion, update, and deletion anomalies. An update anomaly occurs when the same fact is stored in multiple places and you update one but forget the others. Normalization ensures each fact is stored in exactly one place.

First Normal Form (1NF)

A table is in 1NF if every column contains atomic (indivisible) values and there are no repeating groups or arrays. A users table with a phone_numbers column containing "555-1234, 555-5678" violates 1NF because the value is not atomic. The fix is to create a separate user_phones table with one row per phone number.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and every non-key column depends on the entire primary key, not just part of it. This only matters for tables with composite primary keys. Example: an order_items table with key (order_id, product_id) should not store product_name — that depends only on product_id, not the full key. Move product_name to a products table.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column (no transitive dependencies). Example: an employees table with department_id and department_name — department_name depends on department_id, not on the employee key. Move department_name to a departments table.

When to Denormalize

Fully normalized schemas are correct but can require many joins for common queries. In read-heavy systems (analytics, reporting, caching), it is sometimes worth denormalizing — duplicating data to avoid joins. This is a conscious trade-off: faster reads at the cost of more complex writes and the risk of inconsistency. Denormalize only when you have measured a real performance problem.

Example

              -- BEFORE: unnormalized table (violates 1NF, 2NF, 3NF)
-- order_id | customer_name | customer_city | items (CSV)

-- AFTER: normalized into 3NF
CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  city  VARCHAR(100) NOT NULL
);

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

CREATE TABLE products (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);
            

The unnormalized single table is split into four 3NF tables: customers, orders, products, and a junction table order_items.

Try it

Common mistakes

The mistake

Over-normalizing to the point of needing 10 joins for a simple query

The fix

3NF is the practical sweet spot. Higher normal forms (BCNF, 4NF, 5NF) are rarely needed. If queries require too many joins, consider selective denormalization.

The mistake

Storing derived data in a normalized table

The fix

Do not store order_total in the orders table if it can be computed from order_items. Store it only if you denormalize for read performance and keep it in sync.

Practice Normalization

3 exercises

Practice Normalization

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors