Why this matters
- Over-normalised schemas force five-table JOINs on every product page; under-normalised schemas let one address change corrupt hundreds of shipped labels.
- Interviewers ask for 1NF–3NF definitions and when to break them — both sides matter in production.
- VaultCommerce's reporting team and checkout team often disagree on schema shape; normalization vocabulary settles the debate.
- Understanding anomalies (update, insert, delete) explains why duplicate
customer_emailon every order row is dangerous.
First normal form (1NF)
Each column holds atomic values; no repeating groups. Bad design stores multiple tags in one column:
-- Violates 1NF
CREATE TABLE products_bad (
id BIGSERIAL PRIMARY KEY,
name TEXT,
tags TEXT -- 'sale,wireless,bestseller'
);
VaultCommerce uses a product_tags junction table — one row per tag assignment — satisfying 1NF.
Second normal form (2NF)
2NF requires 1NF plus every non-key column depends on the entire primary key. On a composite key (order_id, product_id), product_name must not live in order_items if it depends only on product_id:
-- 2NF: product name lives in products, not order_items
CREATE TABLE order_items (
order_id BIGINT,
product_id BIGINT,
quantity INTEGER,
unit_price_cents INTEGER, -- depends on (order_id, product_id) snapshot
PRIMARY KEY (order_id, product_id)
);
unit_price_cents depends on the line — it is a valid snapshot. product_name belongs in products.
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 3 sections
- Third normal form (3NF)
- When to denormalize
- Balancing the trade-off
Test yourself
Answer these before moving on — recall is what makes it stick.