Normalization & Denormalization

In a Nutshell

Normalization is the process of organizing data so that each fact is stored exactly once — eliminating redundancy and preventing update anomalies. A fully normalized schema (3NF) means no data is duplicated: a customer's name lives in one row of the customers table, not repeated across every order. Denormalization deliberately reintroduces redundancy for read performance — storing precomputed or duplicated data to avoid expensive joins. The tension is fundamental: normalized schemas are correct and compact but slow to read; denormalized schemas are fast to read but harder to keep consistent.

How It Actually Works

Normal Forms

Normal Form	Rule	Violation Example	Fix
1NF	Atomic values, no repeating groups	tags: "java,python,go"	Separate tags table with one tag per row
2NF	No partial dependency (all non-key columns depend on the entire primary key)	Composite key (order_id, product_id) with product_name depending only on product_id	Move product_name to the products table
3NF	No transitive dependency (non-key columns don't depend on other non-key columns)	orders(order_id, customer_id, customer_name) — customer_name depends on customer_id, not order_id	Move customer_name to customers table

Practical rule: Most production schemas aim for 3NF and selectively denormalize from there.

Why Normalize?

1. No update anomalies — Change a customer's name once, and it's changed everywhere
2. Less storage — No duplicate data
3. Data integrity — Constraints enforce consistency
4. Flexible queries — Any query can be expressed with joins

Why Denormalize?

1. Fewer joins — A single table read is faster than joining 5 tables
2. Read-optimized — Data is stored in the shape it will be queried
3. Precomputed aggregates — Store order_count on the user row instead of COUNT(*) on every read
4. Reduced query complexity — Simpler queries, easier caching

Common Denormalization Patterns

Pattern	How	Trade-off
Materialized columns	Store customer_name in orders table	Stale if customer renames; must update all order rows
Precomputed aggregates	Store like_count on the posts table	Must increment/decrement atomically on every like/unlike
Materialized views	Database-managed precomputed query results	Storage cost; refresh strategy (sync vs async)
Denormalized read table	Separate read-optimized table updated by CDC/events	Write amplification; eventual consistency between write and read stores
Embedding (NoSQL)	Store related data inside the document	Document size growth; harder to query embedded data across documents

The Decision Framework

Start: Is the data queried together frequently?
  │
  ├── No → Keep normalized (3NF)
  │
  └── Yes → Is the join expensive at scale?
       │
       ├── No → Keep normalized (join is fine)
       │
       └── Yes → How often does the denormalized data change?
            │
            ├── Rarely → Denormalize (e.g., store product_name on order_items)
            │
            └── Frequently → Use materialized view or cache instead

Seeing It in Action

Scenario: Social media platform — normalizing then selectively denormalizing

Normalized schema (3NF):

CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    display_name VARCHAR(100)
);

CREATE TABLE posts (
    post_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE likes (
    user_id BIGINT REFERENCES users(user_id),
    post_id BIGINT REFERENCES posts(post_id),
    PRIMARY KEY (user_id, post_id)
);

Problem: Rendering a feed requires joining posts + users (for display name) + counting likes (for like count) on every page load:

SELECT p.*, u.display_name, COUNT(l.user_id) as like_count
FROM posts p
JOIN users u ON p.user_id = u.user_id
LEFT JOIN likes l ON p.post_id = l.post_id
GROUP BY p.post_id, u.display_name
ORDER BY p.created_at DESC LIMIT 20;
-- At 100M posts, this is painfully slow

Selectively denormalized:

ALTER TABLE posts ADD COLUMN author_name VARCHAR(100);   -- denormalized from users
ALTER TABLE posts ADD COLUMN like_count BIGINT DEFAULT 0; -- precomputed aggregate

-- Now the feed query is:
SELECT post_id, content, author_name, like_count, created_at
FROM posts
ORDER BY created_at DESC LIMIT 20;
-- Single table scan, no joins — 100× faster

Keeping it consistent:

• author_name: Updated via trigger or application code when user changes display name (rare — acceptable)
• like_count: Incremented atomically: UPDATE posts SET like_count = like_count + 1 WHERE post_id = X

Interview Questions

1. Q: When is denormalization appropriate? Give specific criteria. Hint: When: joins are performance bottlenecks at scale, the denormalized data changes infrequently, the query pattern is known and stable. Don't denormalize: prematurely (measure first), when data changes frequently (stale data risk), or when you haven't tried indexing first. Denormalization is a last resort, not a first optimization.

2. Q: What are update anomalies, and how does normalization prevent them? Hint: Three types: Insert anomaly (can't insert data without unrelated data), Update anomaly (changing a fact requires updating multiple rows — miss one and data is inconsistent), Delete anomaly (deleting one thing accidentally deletes another). Normalization stores each fact once, so updates touch one row.

3. Q: How would you handle a denormalized like_count that gets out of sync? Hint: Prevention: use atomic operations (UPDATE ... SET like_count = like_count + 1). Detection: periodic reconciliation job that compares like_count with SELECT COUNT(*) FROM likes WHERE post_id = X. Recovery: batch update to fix drift. For non-critical data like likes, small drift is acceptable.

4. Q: Your team is debating between a fully normalized schema and a denormalized one. How do you decide? Hint: Start normalized (3NF). Measure query performance under realistic load. If specific queries are slow due to joins, denormalize those specific paths. Don't denormalize everything — only the hot read paths. Use EXPLAIN ANALYZE to confirm that joins (not other factors) are the bottleneck.

5. Q: How do materialized views fit into the normalization/denormalization trade-off? Hint: Materialized views provide denormalized read performance while keeping the source-of-truth normalized. The database manages the precomputed result set. Trade-off: storage cost, and refresh strategy — synchronous refresh (always fresh, slower writes) vs asynchronous refresh (stale data window, faster writes). PostgreSQL supports REFRESH MATERIALIZED VIEW CONCURRENTLY for non-blocking updates.

References

• Designing Data-Intensive Applications by Martin Kleppmann — Chapter 2 on data models
• Database Design for Mere Mortals by Michael Hernandez — accessible normalization guide
• PostgreSQL Materialized Views — official docs

Dive Deeper

• SQL Antipatterns by Bill Karwin — common normalization/denormalization mistakes
• Uber's Schemaless — how Uber denormalized MySQL for scale
• Facebook TAO — extreme denormalization for social graph reads