Normalization and Denormalization Trade-offs

Normalization is the process of organizing tables so that each piece of information is stored in exactly one place, eliminating redundancy and the update anomalies that come with it. The standard normal forms — 1NF (atomic columns, no repeating groups), 2NF (no partial dependency on part of a composite key), and 3NF (no non-key column depending on another non-key column) — give a structured way to reach a design where data has a single source of truth.

Normalization is the right default, but it is not free: a fully normalized schema often requires more joins to answer a single business question, and at high read volume those joins can become the bottleneck. Denormalization — deliberately reintroducing redundancy to speed up reads — is a legitimate design tool, but only when applied consciously to a specific, measured performance problem, with a clear plan for how the duplicated data stays consistent.

Why it matters

Normalised tables never contradict themselves, but reading from them can need many joins. Storing a calculated value, such as an order total, makes reads fast and adds the duty of keeping it correct. Knowing when that trade is worth making, and how to keep the copy honest, is real-world design.

Why Normalize First

A normalized schema means updating a customer's email address touches exactly one row, and every part of the system that reads it sees the same value. Without normalization, the same fact (a product's current price, a customer's current address) can be copied into many tables, and those copies inevitably drift out of sync as the data changes over time — an update anomaly. Designing normalized first, then denormalizing selectively, keeps the default behavior of the schema correct and only trades correctness for speed where you have explicitly decided it is worth it.

When Normalization Hurts Read Performance

Consider an orders list page that needs to show, per order, the customer name, the number of items, and the order total. In a fully normalized schema this requires joining orders to customers and aggregating order_items for every single row shown — fine at low volume, but expensive when the page is loaded thousands of times per minute or the tables grow into the tens of millions of rows. Recomputing an aggregate (like a total) on every read is wasted work if the underlying line items rarely change once an order is placed.

Deliberate Denormalization: a Stored Total

A common, well-understood denormalization is storing a computed value — like order_total — directly on the orders table instead of recalculating SUM(quantity * unit_price) from order_items on every read. This trades a small amount of duplicated, derivable data for avoiding a join-and-aggregate on a hot read path. The trade-off only works if you also decide, explicitly, how the stored total stays correct: recomputed in the same transaction that inserts or updates order_items, or maintained through a trigger, so it never silently drifts from the true sum of the line items.

• Normalize by default for anything that is written more than it is read, or where consistency matters more than raw read speed (financial records, inventory counts).
• Consider denormalizing only after profiling shows a specific, frequently-run query is expensive because of joins or aggregation.
• Always pair denormalization with a consistency plan — a transaction, trigger, or scheduled job that keeps the duplicated value accurate.

Example

CREATE TABLE orders (
    id            BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL REFERENCES customers(id),
    placed_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- Denormalized, derivable field: kept in sync by the app/trigger below.
    order_total   DECIMAL(12, 2) NOT NULL DEFAULT 0
);

CREATE TABLE order_items (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Consistency plan: recompute order_total whenever order_items changes.
CREATE OR REPLACE FUNCTION recalc_order_total() RETURNS TRIGGER AS $$
BEGIN
    UPDATE orders
    SET order_total = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
    )
    WHERE id = COALESCE(NEW.order_id, OLD.order_id);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_recalc_order_total
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION recalc_order_total();

The orders list page can now read order_total directly with zero joins
or aggregation. order_items remains the normalized source of truth for
line-level detail (used on the order detail page). The trigger guarantees
order_total is recalculated automatically any time a line item is added,
changed, or removed, so the denormalized value never drifts.

Exercise

The orders table keeps a denormalised total so that listing orders needs no join. After the items are inserted, bring the stored total up to date by calculating it from order_items, then read it back.

Expected output: total then 174

CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL DEFAULT 0);
CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  quantity INTEGER NOT NULL,
  unit_price INTEGER NOT NULL
);

INSERT INTO orders (id) VALUES (1);
INSERT INTO order_items (id, order_id, quantity, unit_price) VALUES (1, 1, 2, 12), (2, 1, 1, 150);

-- TODO: set orders.total to the sum of quantity * unit_price for that order

SELECT total FROM orders WHERE id = 1;

CREATE TABLE orders (id INTEGER PRIMARY KEY, total INTEGER NOT NULL DEFAULT 0);
CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  quantity INTEGER NOT NULL,
  unit_price INTEGER NOT NULL
);

INSERT INTO orders (id) VALUES (1);
INSERT INTO order_items (id, order_id, quantity, unit_price) VALUES (1, 1, 2, 12), (2, 1, 1, 150);

UPDATE orders
SET total = (
  SELECT SUM(quantity * unit_price)
  FROM order_items
  WHERE order_items.order_id = orders.id
)
WHERE id = 1;

SELECT total FROM orders WHERE id = 1;

Quick quiz

Interview questions