Normalization - System Design Guide

Overview

Normalization is organizing a relational schema so each fact is stored in exactly one place. It removes redundancy and prevents update anomalies, where changing a customer's address in one row but not another leaves the data inconsistent. The normal forms (1NF, 2NF, 3NF, BCNF) are progressively stricter rules about how columns depend on keys.

Denormalization is the deliberate opposite: duplicating data to make reads faster, for example storing a post's comment count or copying the author's name onto each post. Production systems usually normalize by default and denormalize specific hot paths, with a plan to keep copies in sync.

A contact list

If a friend's phone number is written in ten different notebooks, changing it means updating all ten and you will probably miss one. Normalization keeps the number in one address book and makes everything else refer to it.

When to use it

Designing a new relational schema.
Data integrity matters more than raw read speed.
Fixing inconsistent data caused by duplication.
Deciding what to precompute for read-heavy paths.

Where it shows up in interviews

Schema design

Recognize it when: model the data for a new system.

Design a school management database
Design an e-commerce schema

Read-optimized denormalization

Recognize it when: reads vastly outnumber writes.

Design a news feed
Design product listing pages

Where it is used in real software

Counter caches

Rails' counter_cache and similar patterns store comments_count on posts to avoid COUNT(*) on every page view.

Data warehouses

Star schemas intentionally denormalize dimensions for simpler, faster analytical queries.

NoSQL modeling

DynamoDB and Cassandra designs denormalize heavily, storing data once per access pattern.

Key terms

1NF: Atomic values; no repeating groups or comma-separated lists in a column.
2NF: 1NF and no column depends on only part of a composite key.
3NF: 2NF and no column depends on another non-key column (no transitive dependencies).
Update anomaly: Inconsistency when duplicated data is updated in only some places.
Denormalization: Controlled duplication for read performance.

How it works, step by step

1
1NF
Split multi-valued columns (phone1, phone2 or 'a,b,c') into rows in a separate table.
2
2NF
Move columns that depend on part of a composite key into their own table.
3
3NF
Move columns that depend on other non-key columns (zip code to city) into their own table.
4
Measure read paths
Find queries that are slow because of joins or aggregations.
5
Denormalize selectively
Add computed or copied columns with triggers, events, or jobs to keep them in sync.

Normalizing an orders spreadsheet

Original table: order_id, customer_name, customer_email, product_name, product_price, qty

Problem	Anomaly	Normalized fix
Customer email repeated on every order	Changing email misses rows	customers(id, name, email)
Product price repeated	Price change edits history	products(id, name, price) + order_items(price_at_purchase)
Deleting the last order loses the customer	Deletion anomaly	Customers live in their own table
Cannot add a product with no orders	Insertion anomaly	Products live in their own table

NOWProblem: Customer email repeated on every order | Anomaly: Changing email misses rows | Normalized fix: customers(id, name, email)

Four tables (customers, products, orders, order_items) remove all four anomalies. Note that order_items keeps price_at_purchase on purpose: it is a historical fact, not a duplicate.

Implementation

1-- Normalized (3NF)2CREATE TABLE customers (id BIGINT PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT NOT NULL);3CREATE TABLE products  (id BIGINT PRIMARY KEY, name TEXT NOT NULL, price_cents INT NOT NULL);4CREATE TABLE orders    (id BIGINT PRIMARY KEY, customer_id BIGINT REFERENCES customers(id), created_at TIMESTAMPTZ);5CREATE TABLE order_items (6  order_id BIGINT REFERENCES orders(id),7  product_id BIGINT REFERENCES products(id),8  qty INT NOT NULL,9  price_cents_at_purchase INT NOT NULL,10  PRIMARY KEY (order_id, product_id)11);12 13-- Deliberate denormalization: keep a counter on posts, updated in the same transaction14ALTER TABLE posts ADD COLUMN comments_count INT NOT NULL DEFAULT 0;15 16BEGIN;17INSERT INTO comments (post_id, author_id, body) VALUES (7, 42, 'Nice!');18UPDATE posts SET comments_count = comments_count + 1 WHERE id = 7;19COMMIT;

Complexity and performance

Normalized readsMore joins

Fast with indexes, slower at extreme scale.

Denormalized writesMore places to update

Consistency work.

Trade-offs

Integrity vs read speed

Normalized data is consistent by construction; denormalized data is faster to read but can drift.

OLTP vs OLAP

Transactional systems favor normalization; analytical systems favor denormalized wide tables.

Variants and related techniques

Materialized views

Database-maintained denormalized query results, refreshed on demand or schedule.

CQRS read models

Separate denormalized read stores updated from write events.

Common mistakes

Over-normalizing into many tiny tables.
Fix: Stop at 3NF for most apps; model for clarity and performance.
Denormalizing without a sync plan.
Fix: Decide how copies stay correct: same transaction, triggers, or events.

Interview questions

What is the trade-off between normalization and denormalization?

Normalization minimizes duplication and prevents anomalies but requires joins; denormalization speeds up reads by duplicating data at the cost of extra writes and consistency management.

When would you denormalize?

When a measured read path is too slow due to joins or aggregation, reads greatly outnumber writes, and you can keep the copy correct via transactions, events, or periodic recomputation.

Practice problems

Problem	Difficulty	What it trains
Normalize a flat spreadsheet to 3NF	Easy	Dependencies.
Choose denormalized fields for a social feed	Medium	Read optimization.