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
Press Run (or Ctrl + Enter) to see the result here.
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.
-- 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.