Database normalization and denormalization: dependencies, historical facts and read models

TL;DR: If every order stores the customer's current display name, changing that name requires updating every copied value. A partial update leaves the database answering the same question differently depending on which order is read. Normalization addresses that kind of dependency and update anomaly.

Start with the facts each row represents. A customer identifier determines the customer's current profile. An order identifier determines the customer who placed the order. A line within an order determines the purchased product, quantity and agreed price. These facts have different keys and different lifetimes.

Identify the dependency before naming the normal form

Consider an illustrative order-line table with key (order_id, line_number). If customer_id is repeated on every line, it depends on only the order part of that key. If the customer's current display name is also stored there, it depends on the customer identifier. Moving those facts to their owning rows removes opportunities for contradictory copies.

Microsoft's normalization explanation describes the progression through common normal forms. For an interview, connect those forms to the schema's declared dependencies rather than reciting definitions without a key.

Formal normal-form definitions account for all candidate keys and functional dependencies. The table is a practical explanation for this example, not a test that can classify every schema by looking at column names. A UUID primary key does not remove dependencies among the other columns.

A historical price is its own fact

An order's agreed unit price should usually survive a catalog price change. Keeping that price on the order line is intentional historical modeling. The current catalog price and the price accepted for a transaction answer different questions; they are not two authoritative copies of the same mutable value.

Here is a local SQLite exercise. Prices are illustrative integer minor units. Foreign-key enforcement is enabled before any transaction, as required by SQLite's foreign-key guidance:

import sqlite3

with sqlite3.connect(":memory:") as db:
    db.execute("PRAGMA foreign_keys = ON")
    db.executescript("""
      CREATE TABLE customer (
        id INTEGER PRIMARY KEY, display_name TEXT NOT NULL);
      CREATE TABLE product (
        id INTEGER PRIMARY KEY, current_price INTEGER NOT NULL);
      CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER NOT NULL REFERENCES customer(id));
      CREATE TABLE order_line (
        order_id INTEGER NOT NULL REFERENCES orders(id),
        line_number INTEGER NOT NULL,
        product_id INTEGER NOT NULL REFERENCES product(id),
        agreed_price INTEGER NOT NULL CHECK (agreed_price >= 0),
        PRIMARY KEY (order_id, line_number));
      INSERT INTO customer VALUES (1, 'Asha');
      INSERT INTO product VALUES (7, 230);
      INSERT INTO orders VALUES (10, 1), (11, 1);
      INSERT INTO order_line VALUES (10, 1, 7, 230);
      UPDATE customer SET display_name = 'Asha Rao' WHERE id = 1;
      UPDATE product SET current_price = 250 WHERE id = 7;
    """)
    names = db.execute("""
      SELECT c.display_name FROM orders o
      JOIN customer c ON c.id = o.customer_id ORDER BY o.id
    """).fetchall()
    assert names == [('Asha Rao',), ('Asha Rao',)]
    assert db.execute("SELECT agreed_price FROM order_line").fetchone() == (230,)
    try:
        db.execute("INSERT INTO orders VALUES (12, 999)")
    except sqlite3.IntegrityError:
        pass
    else:
        raise AssertionError("orphan order was accepted")

Both orders now join to the updated current name. The purchased price remains 230 even though the catalog says 250. If an invoice must retain the legal name at purchase time, model that historical fact explicitly too, with the applicable retention and correction policy.

Denormalize for a measured read requirement

A support dashboard may need recent orders, current customer names and totals in one inexpensive lookup. A derived read model can supply that shape. Define which source owns each field and how changes reach the projection. Otherwise an optimization silently creates another independently editable database.

Choose a freshness requirement. An asynchronous projection introduces lag and must handle duplicate or out-of-order updates; a synchronous transaction increases write coordination. A repair job needs enough source data or an event history to reconstruct the intended result. Deleting personal data also needs to reach its derived copies.

Constraints still matter. PostgreSQL's constraint documentation explains primary keys, foreign keys and checks, including their scope. Normalizing a diagram does not make an unconstrained implementation reject invalid references.

Measure the expensive read before duplicating fields. Suitable indexes or a query change may solve it without introducing projection maintenance. If denormalization is justified, track its lag and test rebuilding it from the authoritative records.

Self-check: the customer's name changes, but an old invoice must retain the original billing identity. Decide whether a field represents the current profile or the historical transaction. Updating both indiscriminately would destroy the very fact the invoice is supposed to preserve.