--- layout: article title: Data modeling and normalization description: Design PostgreSQL schemas with normalization, decide when to denormalize, and use views to shape data for readers. --- 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: ```sql 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: ```sql 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: ```sql 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 | Form | Rule | Duplication it removes | | --- | --- | --- | | 1NF | One value per cell, no repeating groups | Lists packed into a single cell | | 2NF | Every column depends on the whole key | Facts about part of a composite key, copied into every row | | 3NF | No column depends on a non-key column | Facts about another column, copied into every row | | BCNF | Every determining column is a candidate key | Dependencies between overlapping composite keys that 3NF misses | 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: ```sql 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; ``` ```text 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: ```sql 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: ```sql 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](/docs/products/databases/postgresql/concepts/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](/docs/products/databases/postgresql/concepts/tables#json).