Normalization and schema design

A well-normalised schema prevents contradictory data; a badly designed one causes bugs no query can fix.

Normalization is the discipline of storing each fact in exactly one place. Duplicated facts drift apart: if a customer's city is stored on every order, changing it means updating many rows, and missing one leaves the data contradicting itself (an update anomaly).

The normal forms are a checklist for spotting that duplication:

First normal form (1NF): each column holds a single value. No comma-separated lists like product_ids = '1,3,4'; they cannot be joined, indexed or constrained. Use a row per value in a child table.
Second normal form (2NF): in a table with a composite key, every non-key column depends on the whole key. In order_items (order_id, product_id, ...), a product_name column depends only on product_id, so it belongs in products.
Third normal form (3NF): non-key columns depend on the key and nothing else. If customers stored both city and country, and country is determined by city, that fact belongs in a cities table.

The short version: every non-key column should describe "the key, the whole key, and nothing but the key".

Many-to-many relationships, such as products and tags, need a junction table product_tags (product_id, tag_id) with a composite primary key and two foreign keys.

Not every repeated value is a violation. order_items.unit_price looks like a copy of products.price, but it records a historical fact: the price when the order was placed. If prices change, past orders must not. Ask whether the value should change when the source changes.

Denormalization, storing derived or duplicated data on purpose, is a legitimate optimisation for read-heavy paths, such as a cached orders.total or a reporting table. Do it deliberately, keep one source of truth, and keep the copy in sync with transactions, triggers or materialized views.

Example

-- Not normalised: lists in a column, product facts repeated per order
-- orders_bad(id, customer_name, customer_city, product_ids text, product_names text)
--   101 | Ana | Yerevan | '1,2' | 'Keyboard,Mouse'

-- Normalised: each fact in one place, linked by keys
CREATE TABLE tags (
    id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE product_tags (                    -- many-to-many junction
    product_id bigint REFERENCES products (id) ON DELETE CASCADE,
    tag_id     bigint REFERENCES tags (id)     ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);
CREATE INDEX ON product_tags (tag_id);         -- for tag -> products lookups

-- Products carrying a tag: a join, not string parsing
SELECT p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t          ON t.id = pt.tag_id
WHERE t.name = 'wireless';

Remember

Store each fact once; duplicated facts drift apart
1NF: no lists in columns; 2NF/3NF: columns depend on the whole key and nothing else
Many-to-many relationships need a junction table
Historical snapshots (like unit_price) are not duplication; denormalize only deliberately

Watch out for

Storing comma-separated ids in a text column instead of a child table.

Check yourself

order_items (order_id, product_id, quantity, product_name) has key (order_id, product_id). Which normal form does product_name violate?
Why is order_items.unit_price not a harmful duplicate of products.price?

Official docs: www.postgresql.org/docs/current/ddl-constraints.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks