Data modeling and normalization_

Data modeling decides where each fact lives. Normalization is the discipline of storing every fact exactly once, so it can't contradict itself. This page walks a flat spreadsheet-style table through the normal forms, then covers when to deliberately break the rules, and how views let you reshape data without duplicating it.

The problem with one big table

Start with an orders table designed the way a spreadsheet would be:

CREATE TABLE orders_flat (
    order_id       bigint,
    customer_name  text,
    customer_email text,
    product_names  text,      -- 'Keyboard, Mouse'
    product_prices text       -- '79.00, 29.50'
);

INSERT INTO orders_flat VALUES
    (1, 'Ada', 'ada@example.com', 'Keyboard, Mouse', '79.00, 29.50'),
    (2, 'Ada', 'ada@example.com', 'Monitor', '349.99'),
    (3, 'Grace', 'grace@example.com', 'Mouse', '29.50');

Every design flaw here causes a concrete failure:

• Ada's email is stored twice. Update one row and not the other, and the database now disagrees with itself. This is an update anomaly.
• Products only exist inside order rows. Delete order 2 and the Monitor, its price, everything, vanishes. A delete anomaly.
• A product has no row of its own, so recording one before anyone orders it means a row with empty order fields. An insert anomaly.
• "Which orders contain a Mouse?" requires string parsing instead of a WHERE clause.

First normal form: one value per cell

First normal form (1NF) requires each column to hold a single atomic value, no comma-separated lists. Split the line items into rows:

CREATE TABLE order_lines_1nf (
    order_id       bigint,
    customer_name  text,
    customer_email text,
    product_name   text,
    product_price  numeric(10, 2)
);

INSERT INTO order_lines_1nf VALUES
    (1, 'Ada', 'ada@example.com', 'Keyboard', 79.00),
    (1, 'Ada', 'ada@example.com', 'Mouse', 29.50),
    (2, 'Ada', 'ada@example.com', 'Monitor', 349.99),
    (3, 'Grace', 'grace@example.com', 'Mouse', 29.50);

Now WHERE product_name = 'Mouse' works. But the duplication got worse: Ada's email appears three times, and the Mouse's price twice.

Second normal form: columns depend on the whole key

Second normal form (2NF) applies to tables whose key spans more than one column. Every non-key column must depend on the whole key, not on part of it. The key here is implicitly (order_id, product_name), and product_price depends only on the product, not on which order it is in. The fix is to move the price into a products table, where the product alone is the key.

Third normal form: no column depends on another non-key column

Third normal form (3NF) says no column may depend on a non-key column. customer_email depends on the customer, not the order, so it belongs in a customers table keyed by customer.

Applying both produces the standard shape, where every fact has exactly one home:

CREATE TABLE customers (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    email text NOT NULL UNIQUE
);

CREATE TABLE products (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10, 2) NOT NULL
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id),
    product_id bigint NOT NULL REFERENCES products (id),
    quantity   integer NOT NULL DEFAULT 1,
    unit_price numeric(10, 2) NOT NULL,   -- price at time of order, see below
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers (name, email) VALUES
    ('Ada', 'ada@example.com'), ('Grace', 'grace@example.com');
INSERT INTO products (name, price) VALUES
    ('Keyboard', 79.00), ('Mouse', 29.50), ('Monitor', 349.99);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 79.00), (1, 2, 1, 29.50), (2, 3, 1, 349.99), (3, 2, 1, 29.50);

Changing Ada's email is now a one-row UPDATE that cannot leave a stale copy anywhere. The anomalies are gone because duplication is gone.

Notice unit_price in order_items looks like duplication but isn't: the price at the time of the order is a different fact from the product's current price, and both deserve a home. Normalization is about one home per fact, not zero copies of anything that looks similar.

Boyce-Codd normal form

Boyce-Codd normal form (BCNF) is a stricter 3NF: whenever one column determines another, the determining column must be a candidate key. The difference from 3NF only shows up in tables with overlapping composite candidate keys, for example a bookings table (room, time_slot, teacher) where each teacher always teaches in one room. That table is in 3NF, yet the teacher-to-room fact still duplicates per booking; BCNF moves it into its own table. Schemas like this are rare, and a schema in 3NF almost always satisfies BCNF too.

The normal forms at a glance

Higher forms (4NF, 5NF) deal with multi-valued and join dependencies and rarely change a practical schema; the forms above cover day-to-day design.

Reading a normalized schema

Normalized data comes back together with joins:

SELECT o.id AS order_id, c.name, sum(oi.quantity * oi.unit_price) AS total
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, c.name
ORDER BY o.id;

 order_id | name  | total
----------+-------+--------
        1 | Ada   | 108.50
        2 | Ada   | 349.99
        3 | Grace |  29.50

If the joins feel verbose, that's what views are for, not denormalization.

Views: stored queries, not stored data

A view names a query so readers get the convenient shape without the data being duplicated:

CREATE VIEW order_summaries AS
SELECT o.id AS order_id, c.name AS customer, o.created_at,
       sum(oi.quantity * oi.unit_price) AS total
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, c.name, o.created_at;

SELECT customer, total FROM order_summaries WHERE order_id = 1;

The view runs its query on every read, so it is always current. A materialized view stores the result physically and serves reads from the stored copy, trading freshness for speed on expensive aggregations:

CREATE MATERIALIZED VIEW customer_totals AS
SELECT c.name, sum(oi.quantity * oi.unit_price) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.name;

SELECT * FROM customer_totals ORDER BY lifetime_value DESC;

-- data changed? refresh on your schedule:
REFRESH MATERIALIZED VIEW customer_totals;

When to denormalize

Denormalization deliberately duplicates a fact to make reads cheaper, accepting that your code must now keep the copies in sync. It's a real tool with a real cost, so it should be a measured response to a demonstrated problem, not a default:

• A hot aggregate, such as showing order totals on every page: store orders.total and update it when items change, rather than summing on every read.
• An access-pattern mismatch, such as a search page filtering on a joined column at scale.
• Snapshot semantics, like unit_price above, which isn't really denormalization: the historical value is its own fact.

Before duplicating data, check whether an index, a view, or a materialized view solves the read problem: indexes and views can never disagree with the source data, and a materialized view only lags until its next refresh, without needing any synchronization code of your own. When you do denormalize, keep the copies consistent inside one transaction, or with triggers, so a crash between the write and the sync can't leave them disagreeing. See Transactions.

JSON columns in a relational model

A jsonb column is controlled denormalization for attributes that vary per row, such as per-event metadata. The rule of thumb: anything you filter on, join on, or aggregate regularly deserves a real column with a real type and real constraints; the long tail can live in jsonb. See Tables and data types.