Start With the Table You Have

A small table that mirrors the spreadsheet the business started with is fine while it has few rows and one writer. Nothing disagrees yet, so there's nothing to fix. Normalising pays from the first time the same fact has to be changed in more than one place.

A customer changes their email, and some notifications keep going to the old address. Somewhere the email is stored in hundreds of rows, and the update reached only some of them. That is the problem normalization was invented to prevent, and it still shows up in new schemas every week.

The normal forms are a checklist for storing each fact in exactly one place, so it cannot disagree with itself. They are not a rule to apply blindly: some data, such as the address an order was shipped to, is a fact about an event and belongs with that event. Below is how to normalise to 3NF, and when denormalising on purpose is the right call.

Signs your schema needs normalising
  • You can't remember which form is which
  • You denormalized for "performance" before measuring
  • Your customer name appears in orders, invoices, and shipments — and they disagree
  • You're terrified of changing a column name because 12 reports break

The Normal Forms in Practice

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

Normalising a Real Table

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)
);
✅

Check it: Search your schema for column names that end in a number, and for text columns that your code splits on a comma. Each is a repeating group waiting to become a table.

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)
);
✅

Check it: Change one customer's email and count the rows you had to update. In third normal form the answer is one.

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

Check it: Change a source row and time how long the read model takes to show it. That delay is your staleness, and it should be a number you chose.

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

Check it: Change a product's price and open an order placed before the change. Its total should be exactly what it was.

Mistakes in Both Directions

  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 can block every reader until it finishes. CONCURRENTLY needs a unique index on plain columns and an already-populated view, and it doesn't lock out readers.
  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.

Try It on Your Own System This Week

Three checks on a schema you already run, about an hour in total:

  1. Find a fact stored in two tables. Decide which is the source, and whether the other is a snapshot or a mistake.
  2. List every denormalised copy. For each, write down how it's updated and how stale it may be. If nobody knows, that's the bug.
  3. Measure the join you're afraid of. With indexes on the keys it's usually fast. Measure before you copy data to avoid it.

How I Decide

  • 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 when readers can't wait. A plain refresh is often faster when most rows change, so it suits views nobody reads during the refresh.
  • 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.

When to Denormalise, and How to Do It Safely

Normalise by default, and denormalise when you have a measured read problem. That rule hasn't changed. What has changed is that there are now better options than copying columns by hand.

Generated columns let the database compute a value from other columns in the same row, so it can't drift. PostgreSQL 18 added virtual generated columns, computed on read with no storage cost, and stored ones are computed on write. Materialized views suit expensive aggregates that can be a little stale, and REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers. For cross-table counters, a trigger or an outbox-driven update keeps the logic in one place.

The risk with any copied value is that it goes stale, so whenever you denormalise, decide three things up front: what's the source of truth, how does the copy update, and how will you detect drift? Run a scheduled query that compares the copy with the source. It's a small job, and it turns silent corruption into an alert.

Snapshots aren't denormalisation

Some data looks denormalised and isn't. An order should store the price and the shipping address at the time of purchase, even though the product and customer tables hold current values. That's a historical fact, and if you join to the current price, last year's invoices change when the price does. Ask whether a value means "what it is now" or "what it was then". The answer tells you whether to reference it or copy it. Audit tables and event logs follow the same logic.

JSON columns

jsonb is useful for sparse or truly variable attributes, and it's easy to overuse. If you find yourself filtering, joining or enforcing rules on a key inside a JSON column, that key wants to be a real column with a constraint. A common pattern for AI features is storing model output as JSON, which is fine as a record. Promote any field you query into its own column, since it gets a type, an index and validation that way.

Constraints are the part of normalisation people skip. A UNIQUE, CHECK or foreign key is an invariant the database enforces for every client, including a script somebody runs at midnight and a coding agent writing a migration. Application-level checks can't promise that, because two concurrent requests can both pass them. Normal forms get the attention, and it's the constraints that keep your data correct.