Normalization splits data across multiple related tables to eliminate redundant storage of the same fact, following a series of normal forms of increasing strictness: First Normal Form (1NF) requires atomic column values, Second Normal Form (2NF) removes partial dependencies on part of a composite key, and Third Normal Form (3NF) removes dependencies between non-key columns. Boyce-Codd Normal Form (BCNF), developed by Raymond Boyce and Edgar Codd in 1974, tightens 3NF further for certain edge cases. Most production schemas target 3NF as a practical, well-understood balance.
An unnormalized table, and the problem it causes
Storing the customer's name and address directly in every order row means updating a customer's address requires finding and editing every one of their orders — miss one, and the data is now inconsistent.
-- BEFORE: customer data repeated in every order row
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_name VARCHAR(100),
customer_address VARCHAR(255), -- repeated in every order this customer places
total NUMERIC(10, 2)
);Normalizing: splitting into related tables
A customer's address now lives in exactly one place — update it once, and every order automatically reflects the change through the foreign key relationship.
-- AFTER: customer data lives in exactly one place
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(100),
address VARCHAR(255)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total NUMERIC(10, 2)
);When to deliberately denormalize
Normalization isn't free — more tables means more JOINs to reconstruct a full picture, which costs query performance. Read-heavy systems sometimes deliberately denormalize (duplicating some data back for faster reads), accepting the redundancy-management burden in exchange for speed. This is a genuine engineering tradeoff, not a sign that normalization was done wrong — the right amount of normalization depends on the actual read/write pattern of the application.