Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Dialects and Concepts

SQL normalization

Database normalization explained: first, second and third normal form and BCNF, functional dependencies, update anomalies, examples in SQL and when to denormalize.

Normalization is the process of organizing tables so that each fact is stored once, in the right place. A badly designed table repeats data, and repeated data goes out of step: change a customer's city in one row and forget another, and the database now disagrees with itself. Normal forms are rules that rule out these problems.

The rules rest on functional dependencies: column A determines column B if every value of A always comes with the same value of B. A product id determines the product name; a city does not determine a customer. Each normal form removes one kind of unwanted dependency.

Update anomalies

A single table that mixes several kinds of facts causes three problems:

  • Update anomaly: the same fact appears in many rows, so changing it means changing all of them.
  • Insert anomaly: you cannot record one fact without another (a new product cannot be listed until someone orders it).
  • Delete anomaly: deleting one fact loses another (deleting the last order of a product loses the product).

Try it

SQL A table that repeats facts, and its anomaly ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL The same data in third normal form ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The normal forms

First normal form (1NF). Every column holds a single, atomic value, with no repeating groups. A column phones containing '555-0101, 555-0199' or columns phone1, phone2, phone3 breaks 1NF. Move the phones to their own table with one row per phone. Each table also needs a primary key.

Second normal form (2NF). In 1NF, and every non-key column depends on the whole primary key, not just part of it. This matters only for composite keys. In order_lines(order_id, product_id, quantity, product_name), product_name depends only on product_id, so it belongs in a products table.

Third normal form (3NF). In 2NF, and no non-key column depends on another non-key column. If customers stores zip_code and city, and the zip code determines the city, then city depends on zip_code, not directly on the customer. Move the pair to a zip_codes table. A memorable summary: every column depends on the key, the whole key and nothing but the key.

Boyce-Codd normal form (BCNF). A stricter 3NF: for every dependency X -> Y, X must be a candidate key. The difference appears only when a table has several overlapping candidate keys. Most 3NF designs are already in BCNF.

Fourth and fifth normal forms deal with independent multi-valued facts and join dependencies. They are rare in everyday design.

Form Rule in one line Fix
1NF One atomic value per cell, a primary key Split lists into rows or a child table
2NF No partial dependence on a composite key Move the partly dependent columns to their own table
3NF No dependence between non-key columns Extract the dependency into a lookup table
BCNF Every determinant is a candidate key Decompose on the determinant

Dialect differences

Normalization is a design method, not a feature, so it works the same in every database. The tools that enforce it differ in small ways:

  • Foreign keys are enforced by default in PostgreSQL, MySQL (InnoDB), SQL Server and Oracle. SQLite needs PRAGMA foreign_keys = ON.
  • Composite primary keys are supported everywhere. In SQLite, a composite key does not create an alias for the row number.
  • Lookup tables and unique constraints use the same syntax in all five systems; only auto-increment and type names change.
SQL
-- PostgreSQL
CREATE TABLE zip_codes (zip TEXT PRIMARY KEY, city TEXT NOT NULL);
CREATE TABLE customers (
  id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  zip TEXT NOT NULL REFERENCES zip_codes (zip)
);

Usage notes

  • Aim for 3NF for transactional (OLTP) databases. It is the standard point of rest: simple updates and few anomalies.
  • Denormalize on purpose. Reporting tables, data warehouses and read-heavy caches often repeat data to avoid joins. Do it after measuring, keep the normalized source of truth, and refresh the copies.
  • Do not store derived values (an order total that can be summed from lines) unless you accept the risk of them going stale, or maintain them with a trigger or generated column.
  • Lookup tables and constraints (UNIQUE, FOREIGN KEY, CHECK) are how the database enforces what normalization designs.
  • Over-normalizing into dozens of tiny tables makes queries long. Split only when the repeated fact can really change independently.
  • Natural vs surrogate keys. A zip code can be a key; a person's name cannot. Use a generated id when no stable natural key exists.