Normalization: 1NF to 3NF, Denormalize Reads, Snapshot Truths

Problem Context

Normalization is a 1970s idea (Codd's normal forms) that still wins arguments in 2026 — because the alternative is a wide table with eight copies of every customer name, three of which are out of date. The forms (1NF, 2NF, 3NF, BCNF) are a checklist for eliminating redundancy and update anomalies. The catch: fully normalized schemas read poorly. Modern systems normalize the system of record and denormalize selectively for read performance — usually via materialized views, CQRS read models, or a cache layer.

The 2026 question isn't "normalize or not?". It's "where do you put each copy of the truth, and how do you keep them in sync?". Postgres 17 incremental materialized views (still extension-only via pg_ivm), Cosmos change feed, and Debezium CDC have made the "denormalize for reads" pattern mainstream and observable.

Concept Explanation

The normal forms in plain English:

• 1NF — atomic columns; no repeating groups (no comma-separated lists, no phone1, phone2, phone3).
• 2NF — every non-key column depends on the whole key (only matters with composite keys).
• 3NF — every non-key column depends on the key, and nothing else (no transitive dependencies likezip → city stored in the customer row).
• BCNF — stricter 3NF; every determinant is a candidate key. Rare to need explicitly.

flowchart LR
    SOR["System of Record<br/>(3NF Postgres)"] --> CDC["Change feed / CDC"]
    CDC --> MV["Materialized read model<br/>(denormalized)"]
    CDC --> CACHE["Cache (Redis)"]
    CDC --> SEARCH["Search index<br/>(Elastic / Atlas)"]
    APP["App"] --> MV
    APP --> SOR

    style SOR fill:#0078D4,color:#fff,stroke:#005a9e
    style MV fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Recognize 1NF violations

-- BAD (not 1NF): repeating group
CREATE TABLE customers_bad (
    id    bigint PRIMARY KEY,
    name  text,
    phone1 text, phone2 text, phone3 text  -- ← anti-pattern
);

-- GOOD: separate table for the multi-valued attribute
CREATE TABLE customers (id bigint PRIMARY KEY, name text);
CREATE TABLE customer_phones (
    customer_id bigint REFERENCES customers(id),
    kind  text  CHECK (kind IN ('mobile','home','work')),
    phone text NOT NULL,
    PRIMARY KEY (customer_id, kind)
);

Step 2: Eliminate transitive dependencies (3NF)

-- BAD: zip determines city/state, but they live in customers
CREATE TABLE customers_bad (
    id bigint PRIMARY KEY,
    name text,
    zip text, city text, state text   -- city/state depend on zip, not id
);

-- GOOD: factor out the dependency
CREATE TABLE postal_codes (
    zip text PRIMARY KEY, city text NOT NULL, state text NOT NULL
);
CREATE TABLE customers (
    id bigint PRIMARY KEY, name text NOT NULL,
    zip text REFERENCES postal_codes(zip)
);

Step 3: Build a normalized core for OLTP

-- Orders + line items + products: clean 3NF
CREATE TABLE products (
    id bigint PRIMARY KEY, sku text UNIQUE, name text, price_cents bigint
);
CREATE TABLE orders (
    id bigint PRIMARY KEY, customer_id bigint REFERENCES customers(id),
    placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
    order_id   bigint REFERENCES orders(id) ON DELETE CASCADE,
    product_id bigint REFERENCES products(id),
    qty        int    NOT NULL CHECK (qty > 0),
    unit_price_cents bigint NOT NULL,   -- snapshot of price at order time
    PRIMARY KEY (order_id, product_id)
);

Step 4: Denormalize for reads with a materialized view

-- Read model: one row per order with totals + customer name pre-joined
CREATE MATERIALIZED VIEW order_summary AS
SELECT o.id           AS order_id,
       o.placed_at,
       c.id           AS customer_id,
       c.name         AS customer_name,
       SUM(oi.qty * oi.unit_price_cents) AS total_cents,
       COUNT(*)       AS line_count
FROM   orders o
JOIN   customers c   ON c.id = o.customer_id
JOIN   order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.placed_at, c.id, c.name;

CREATE UNIQUE INDEX ON order_summary (order_id);
CREATE INDEX ON order_summary (customer_id, placed_at DESC);

-- Refresh strategy: REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;
-- Or pg_ivm for incremental refresh, or Debezium → Kafka → read model.

Step 5: CQRS read model in C#

// Write side hits the normalized tables; read side queries the projection
public class OrderSummaryReadModel  // backed by order_summary
{
    public long   OrderId { get; set; }
    public DateTime PlacedAt { get; set; }
    public long   CustomerId { get; set; }
    public string CustomerName { get; set; } = "";
    public long   TotalCents { get; set; }
    public int    LineCount { get; set; }
}

// Reads are one row, no joins, no aggregation
var page = await db.OrderSummaries
    .Where(s => s.CustomerId == customerId)
    .OrderByDescending(s => s.PlacedAt)
    .Take(20)
    .ToListAsync();

Step 6: Snapshot fields that must not change retroactively

-- order_items.unit_price_cents above is a snapshot, NOT a denormalization bug.
-- An order placed at $9.99 must still total $9.99 even if products.price_cents
-- changes tomorrow. This is a deliberate, immutable copy of the truth at write time.
-- Document it; future-you will thank you.

Common Pitfalls

1. Premature denormalization."It's for performance" without measurement is how teams end up with three copies of the customer name and zero idea which is right.
2. Confusing snapshots with duplication.Storing the price-at-time-of-order is correct (immutable history). Storing the customer's current address on every order is duplication that will rot.
3. Ignoring update anomalies.If you can update one row and leave the database in a contradictory state, you're under-normalized. Either normalize or build a constraint.
4. One giant view as "the read model". Materialized views that touch 12 tables refresh slowly and break loudly. Keep read models task-specific.
5. Refreshing materialized views without CONCURRENTLY. Plain refresh takes an exclusive lock — every reader stalls. CONCURRENTLY needs a unique index but is non-blocking.
6. CQRS without a sync mechanism.If you split read and write models, you must own the propagation: triggers, change feed, CDC, or scheduled rebuilds. "The app will keep them in sync" never lasts.

Practical Takeaways

• Normalize the system of record to 3NF. It's the cheapest way to avoid update anomalies.
• Denormalize reads, not writes — via materialized views, CQRS projections, or caches.
• Snapshots (price-at-order, terms-at-signup) are deliberate immutable copies, not duplication bugs.
• Use REFRESH MATERIALIZED VIEW CONCURRENTLY; never plain refresh in production.
• Make the propagation pipeline explicit (CDC, change feed, triggers) so staleness is observable.
• Most app schemas need 3NF + a few hand-tuned denormalized read paths. BCNF / 4NF / 5NF are rare in practice.
• If two columns "always agree," one of them shouldn't exist — or one is a snapshot.