Skip to main content
PreparationZone Logo
PrepZone

Normalization and Denormalization

1NF through 3NF for clean schemas, and when to break rules for read performance.

Pass checkpoint below

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_email on every order row is dangerous.
Clustered (PK)
Table rows (sorted by PK)
Row 1id=1
Row 2id=2
Row 3id=3
Non-clustered (email)
email index→ row pointer
Table heapunordered rows
A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

First normal form (1NF)

Each column holds atomic values; no repeating groups. Bad design stores multiple tags in one column:

Java
-- 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:

Java
-- 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.