Normalization

Reducing redundancy through normal forms — and when to intentionally denormalize

Why

Unnormalized schemas store the same data in multiple places. When that data changes, every copy must be updated — miss one and you have inconsistency. Normalization eliminates redundancy by ensuring each fact is stored once. But normalized schemas require more joins to read, which adds latency. The art is knowing when to normalize and when to deliberately denormalize for performance.

Intuition

Each normal form addresses a specific type of redundancy. 1NF: each cell holds one atomic value (no lists in a column). 2NF: non-key columns depend on the whole primary key, not just part of it. 3NF: non-key columns depend only on the primary key, not on each other. BCNF: a stricter version of 3NF.

Think of it progressively: each level fixes a more subtle form of redundancy. In practice, reaching 3NF is the goal for most transactional systems.

Explanation

Normal forms 1NF → 3NF

-- UNNORMALIZED: redundant, multi-valued cells order_id | customer_name | customer_city | items ---------|---------------|---------------|------------------ 1 | Alice | NYC | "book, pen, ruler" 2 | Alice | NYC | "notebook" -- 1NF: atomic values, no repeating groups (one item per row) order_id | customer_name | customer_city | item ---------|---------------|---------------|-------- 1 | Alice | NYC | book 1 | Alice | NYC | pen -- 2NF: remove partial dependencies (customer data depends only on customer) -- Split: orders(order_id, customer_id) + customers(customer_id, name, city) -- 3NF: remove transitive dependencies -- If city → zip_code, store city→zip in a separate cities table -- Non-key columns must depend ONLY on the primary key, nothing else

When to denormalize

Normalize when

Data is updated frequently. Consistency is critical — one fact, one place. OLTP systems (banking, e-commerce) where writes are common and read patterns vary. Storage cost matters.

Denormalize when

Read performance is critical and joins are expensive. Data is rarely updated. Analytics / OLAP workloads (data warehouses, dashboards). Pre-computed aggregates or duplicate columns traded for faster reads.

-- Common denormalization patterns -- 1. Duplicate a column to avoid a join orders.customer_email -- copied from customers table, updated on change -- 2. Pre-aggregate into a summary table daily_revenue(date, product_id, total_revenue) -- updated by a job -- 3. JSONB column for flexible attributes products.attributes JSONB -- avoids EAV table hell

← Previous Next →