Normalization and Denormalization

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.

Third normal form (3NF)

3NF requires 2NF plus no transitive dependencies — non-key columns must depend only on the primary key, not on other non-key columns.

-- Violates 3NF: category_name depends on category_id, not product id
CREATE TABLE products_bad (
    id            BIGSERIAL PRIMARY KEY,
    name          TEXT,
    category_id   BIGINT,
    category_name TEXT  -- transitive dependency
);

-- 3NF: category name in categories table
CREATE TABLE categories (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

Without normalization, updating a category name touches thousands of product rows (update anomaly), and deleting the last product in a category can orphan the category label (delete anomaly).

When to denormalize

Read-heavy paths that cannot tolerate multi-table JOINs justify controlled duplication:

-- Denormalized summary table refreshed nightly
CREATE TABLE product_sales_summary (
    product_id    BIGINT PRIMARY KEY,
    units_sold_30d INTEGER NOT NULL,
    revenue_cents_30d BIGINT NOT NULL,
    refreshed_at  TIMESTAMPTZ NOT NULL
);

The ETL job aggregates from normalized order_items; the product listing API reads one row per SKU. Staleness of up to 24 hours is acceptable for "bestseller" badges.

Balancing the trade-off

Start normalized; denormalize only when profiling proves JOIN cost exceeds maintenance burden.