Normalization

Normalization is the process of organizing a schema so that each independent fact is stored in exactly one place. The payoff is that updating a fact — a customer’s email, a product’s price — only ever requires changing one row. The cost is that reading back a “complete” record (an order with its customer and items) now requires joining several tables back together.

Normal forms are a series of increasingly strict rules for what a well-structured table looks like. Each one fixes a specific kind of redundancy left over from the previous form.

The anomalies redundancy causes

Picture one wide orders table where every row also carries the customer’s name, email, and address, plus each product’s name and price.

• Update anomaly. A customer changes their email. It’s copied onto 400 of their order rows. Miss one and the data now contradicts itself — which email is real?
• Insertion anomaly. You want to add a new product to the catalogue, but the only place product data lives is on order rows. You can’t list a product nobody has bought yet without inventing a fake order.
• Deletion anomaly. A customer’s only order is deleted for a refund. Their address disappeared with it — that fact only existed on that row.

Normalization removes these by giving each fact one home.

First normal form (1NF)

Every column must hold a single, atomic value — no repeating groups or lists crammed into one field.

A products column containing "Widget x2, Gadget x1" isn’t queryable by product without parsing a string, can’t be joined, and can’t be constrained. Splitting those into their own rows (one per order + product) fixes it.

Real-world 1NF violations you’ll actually meet: a phone_numbers column holding "555-1234 / 555-9999", a tags column holding "red,sale,clearance", a roles column holding a JSON array of role IDs that other tables need to join against. (A JSON/array column is a deliberate choice sometimes — see the callout at the end — but as a stand-in for a real relationship it’s a 1NF break.)

Second normal form (2NF)

Applies once a table has a composite primary key (like order_id + product_id). Every non-key column must depend on the whole key, not just part of it.

In an order_items(order_id, product_id, quantity, product_name, unit_weight) table, product_name and unit_weight depend only on product_id — not on which order it’s in. So they’re duplicated across every order line for that product, and updating a product name means a sweep across order_items. They belong in a products table.

Third normal form (3NF)

Every non-key column must depend only on the primary key — not on another non-key column.

orders(order_id, customer_id, customer_email, customer_tier). customer_email and customer_tier depend on customer_id, which is itself just a non-key column on orders. That’s a transitive dependency. Pull customer fields into a customers table; orders keeps only customer_id.

-- 3NF: orders only references a customer, it doesn't duplicate their details
SELECT o.order_id, c.customer_name, c.customer_email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

A quick test for 3NF: if I know a row’s value for column X, can I predict column Y without looking at the key? If yes (email predicts tier, zip predicts city, product_id predicts product_name), Y is in the wrong table.

Denormalization: doing it on purpose, and where

Normalization optimizes for correct writes. Some workloads are dominated by reads that would need many joins, and deliberately storing a fact in two places — with a plan to keep the copies in sync — is the right call.

Cached aggregates. A posts.comment_count column instead of SELECT COUNT(*) on every page load. Kept correct with a trigger or an atomic UPDATE posts SET comment_count = comment_count + 1 in the same transaction as the comment insert. Reddit-style sites live on this pattern.

Point-in-time snapshots. order_items.unit_price_at_purchase duplicates today’s products.price. This is not a normalization violation — the price the customer paid is a genuinely different fact from the current price, and it must not change when the catalogue does.

Read models / materialized views. An analytics dashboard reads from a pre-joined, pre-aggregated table refreshed every few minutes, not from the live normalized tables. Postgres MATERIALIZED VIEW, or a separate reporting database.

Star schema for warehouses. OLAP systems deliberately flatten dimensions (a dim_customer table repeating region, segment, and tier on every row) so analysts write two-table joins instead of eight. Different workload, different rules.

Search indexes. A denormalized document per product pushed into Elasticsearch/OpenSearch, rebuilt from the source of truth. The relational tables stay normalized; the search copy is disposable.