Overview
Normalization is the process of structuring a relational schema so each fact is stored exactly once. It works through a series of normal forms (1NF, 2NF, 3NF, and beyond), each one closing off a specific kind of redundancy. The payoff is data integrity: updates touch one row instead of many, and it is structurally impossible for two copies of the same fact to disagree. The cost is more tables and more joins to reassemble a full picture, which is why real systems normalize for correctness first, then denormalize specific hot paths on purpose.
Quick Reference
| Form | Requires | Fixes |
|---|---|---|
| 1NF | Atomic columns, no repeating groups | Values-in-a-column-name and comma-packed lists |
| 2NF | 1NF, plus no partial key dependency | Non-key columns depending on only part of a composite key |
| 3NF | 2NF, plus no transitive dependency | Non-key columns depending on other non-key columns |
| BCNF | 3NF, plus every determinant is a candidate key | Rare edge cases 3NF still allows |
Syntax
sql
-- Unnormalized: repeating groups packed into columns
CREATE TABLE orders_bad (
id INT PRIMARY KEY,
customer_name TEXT,
customer_email TEXT,
item_1 TEXT, item_1_qty INT,
item_2 TEXT, item_2_qty INT
);
-- Normalized: one fact per table, related by foreign keys
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(id)
);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL
);Examples
sql
-- Reassembling a full order view requires joining back across the split tables
SELECT o.id, c.name, c.email, p.name AS product, oi.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.id = 42;sql
-- A deliberate, tracked denormalization: cache the order total to avoid
-- summing line items on every read of a hot "recent orders" list.
ALTER TABLE orders ADD COLUMN total_amount NUMERIC(10, 2);
-- Kept in sync via a trigger or application-layer write, not left to drift
UPDATE orders o
SET total_amount = (
SELECT SUM(oi.quantity * p.price)
FROM order_items oi JOIN products p ON p.id = oi.product_id
WHERE oi.order_id = o.id
)
WHERE o.id = 42;Visual Diagram
Common Mistakes
- Treating 3NF as a universal target rather than a starting point, over-normalizing a read-heavy analytics table can force expensive joins on every query.
- Storing a derived value (like
cityalongsidezip_code) without recognizing it as a transitive dependency waiting to go stale. - Denormalizing without a consistency plan, a duplicated value with no trigger or application logic to keep it in sync will eventually drift.
- Comma-packing multiple values into a single column (
tags: "a,b,c") instead of a proper join table; this breaks 1NF and makes filtering/indexing painful.
Performance
- Every additional normalized table is an additional join at read time, for OLTP workloads with selective indexed joins this is usually cheap, but it adds up on wide analytical queries.
- Denormalized read models (materialized views, cached aggregates, or a dedicated reporting schema) are a common, legitimate way to keep a normalized source of truth while still serving fast reads.
- Foreign keys enforce referential integrity but also add index maintenance cost on the referencing table, worth it almost always, but not free.
Best Practices
- Normalize to 3NF by default for transactional (write-heavy, correctness-sensitive) schemas.
- Denormalize deliberately and narrowly, one cached column or one materialized view, not a wholesale flattening of the schema.
- Document any intentional redundancy and how it is kept consistent, so the next engineer does not "fix" it into a bug.
- Reach for a separate read-optimized model (a reporting table, a search index, a cache) before denormalizing your system of record.