Schema design & normalization

Queries can be rewritten in an afternoon; a schema lives for years and every query is shaped by it. A good schema makes correct data easy to store and wrong data hard to store, and makes common questions simple to ask. This lesson shows how to get from a description of a business to tables, why the "one big spreadsheet" design breaks down, what the normal forms actually mean, how to choose data types, and when breaking the rules on purpose is reasonable.

#From requirements to entities

Start by writing down what the system must remember, in plain sentences. For the bookstore:

• An author has a name and a country, and writes books.
• A book has a title, genre, price, publication year and stock level, and is written by one author.
• A customer has a name, a unique email, an optional city and a join date, and may have been referred by another customer.
• A customer places orders; each order has a date and a status.
• An order contains one or more books, each with a quantity and the price paid.

The nouns that have their own identity and attributes become entities, which become tables: authors, books, customers, orders. The verbs become relationships. For each relationship, ask two questions in both directions — "how many?" and "is it required?":

Notice the last line of the requirements: "each with a quantity and the price paid". Those attributes belong to neither the order nor the book; they describe the pairing. Attributes of a relationship are the clearest signal that you need a junction table — and that the junction table is a real entity in its own right (an order line).

A one-to-one relationship (a customer and their loyalty card) is usually just more columns on the same table. Split it into a second table sharing the primary key only when those columns are sparse, accessed separately, or more sensitive.

#Why one big table fails

Imagine skipping all that and storing sales in a single wide table, the way a spreadsheet would:

 order_id | ordered_on |  customer  |  city  |           title           |      author       | country | qty | unit_price
----------+------------+------------+--------+---------------------------+-------------------+---------+-----+------------
        1 | 2025-01-05 | Ada Park   | Leeds  | A Wizard of Earthsea      | Ursula K. Le Guin | US      |   1 |      12.99
        1 | 2025-01-05 | Ada Park   | Leeds  | Guards! Guards!           | Terry Pratchett   | UK      |   2 |       9.99
        2 | 2025-01-12 | Ben Okafor | London | Norwegian Wood            | Haruki Murakami   | Japan   |   1 |      17.00
        3 | 2025-02-02 | Ada Park   | Leeds  | Small Gods                | Terry Pratchett   | UK      |   1 |      10.99
        3 | 2025-02-02 | Ada Park   | Leeds  | The Left Hand of Darkness | Ursula K. Le Guin | US      |   1 |      15.50

It is easy to query, but the same facts are stored many times — "Ada lives in Leeds" appears on every line she has ever bought. That redundancy causes three kinds of anomaly:

• Update anomaly. Ada moves to York. You must update every one of her rows; miss one and the database now claims she lives in two cities at once.
• Insertion anomaly. You sign a contract with N. K. Jemisin before any of her books sell. There is nowhere to record her, because every row must be a sale.
• Deletion anomaly. In this excerpt Ben has just one order; it is refunded and you delete its row. You have also deleted the only record that Ben exists, and that Norwegian Wood is by Haruki Murakami.

All three come from one root cause: a table mixing facts about different things. Normalization separates them systematically.

#The normal forms

Each normal form removes a specific kind of redundancy. For practical schema design, you need the first three.

#First normal form: one value per cell

A table is in 1NF when every column holds a single, atomic value and there are no repeating groups. These violate it:

• books.genres = 'sci-fi, time travel' — a list in a string.
• orders.book1, orders.book2, orders.book3 — a repeating group of columns.

Both make basic questions painful ("all time travel books" becomes a LIKE '%time travel%' search that also matches "time travel romance") and make constraints impossible (you cannot have a foreign key to a substring). The fix is always a separate table with one row per value.

#Second normal form: depend on the whole key

A table is in 2NF when it is in 1NF and no non-key column depends on only part of a composite key. Suppose order_items, whose key is (order_id, book_id), also stored title and ordered_on. title depends only on book_id; ordered_on depends only on order_id. Each would be repeated on every line and could drift out of sync. Move them to the table whose whole key they depend on: books and orders.

#Third normal form: depend on nothing but the key

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. If books stored author_country, that column would depend on author_id (a non-key column in books), not on book_id itself. Changing Le Guin's country would mean updating three book rows. It belongs in authors.

Higher normal forms (BCNF, 4NF, 5NF) deal with rarer situations such as overlapping candidate keys and independent multi-valued facts. If your tables are in 3NF and every table describes exactly one kind of thing, you have avoided the vast majority of anomalies.

#Redundancy that is not redundancy

order_items.unit_price looks like a copy of books.price, but it is not. Order 1 bought Guards! Guards! at 9.99; the price is now 10.99. The order line records the price at the time of purchase, which is a different fact from the current list price. Deleting it in the name of normalization would rewrite history every time a price changes. The same reasoning applies to shipping addresses on orders, exchange rates on payments and tax rates on invoices.

#Choosing data types

Types are your first line of defence: a value of the wrong type cannot be stored at all. Recommended defaults in PostgreSQL:

Two of these deserve a demonstration. Floating-point types store binary approximations, so cents go missing:

SELECT 0.1::float8 + 0.2::float8           AS float_sum,
       0.1::numeric + 0.2::numeric         AS numeric_sum,
       0.1::float8 + 0.2::float8 = 0.3::float8 AS float_equal;

      float_sum      | numeric_sum | float_equal
---------------------+-------------+-------------
 0.30000000000000004 |         0.3 | f
(1 row)

And timestamptz does not actually store a time zone. It stores an absolute instant (internally in UTC) and converts it to the session's time zone for display. Plain timestamp stores a wall-clock reading with no zone, so "09:00" from a London server and "09:00" from a Tokyo server become indistinguishable. Use timestamptz for anything that happened at a moment in time; our ordered_on is a date only because the bookstore reports by day.

#Status values

orders.status holds one of a few values. There are three reasonable designs: a text column with a CHECK (status IN (...)) constraint (simple, easy to change), a lookup table order_statuses referenced by a foreign key (best when statuses carry their own attributes or are managed by users), or a PostgreSQL ENUM type (compact, but removing or reordering values is awkward). The constraints lesson adds the CHECK version.

#Deliberate denormalization

Normalization optimises for correctness of writes. Sometimes reads matter more, and you deliberately store derived or duplicated data:

• Cached aggregates, such as an orders.total column so order lists do not have to sum items every time.
• Reporting tables or materialized views that pre-join and pre-aggregate data for dashboards.
• Copies for history, like unit_price — which, as you saw, is not really denormalization at all.

Every cached value is a second source of truth that can disagree with the first. Denormalize only when you have measured a real performance problem, and decide up front how the copy stays correct: computed in the same transaction as the source change, maintained by a trigger, or periodically rebuilt (REFRESH MATERIALIZED VIEW).

#Naming conventions

Consistency matters more than any particular style. A common PostgreSQL convention: lower-case snake_case names, so you never need double quotes; plural table names (books); primary keys named <singular>_id (book_id), so foreign keys can share the name of the column they reference; and date and time columns named for their meaning (ordered_on, shipped_at).

#Practice

The shop wants three changes, and a colleague has proposed shortcuts:

1. Books can belong to several genres — Kindred is sci-fi and time travel. Proposal: change books.genre into a comma-separated list.
2. Each book has one publisher with a name and country. Proposal: add publisher_name and publisher_country columns to books.
3. Customers can keep a wishlist of books, recording when each was added. Proposal: add a customers.wishlist text column of titles.

For each proposal, name the normal form or anomaly it runs into. Then write the DDL for a normalized design, including a migration of the existing genres, and test it inside BEGIN … ROLLBACK.

BEGIN;

CREATE TABLE publishers (
  publisher_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         text NOT NULL,
  country      text
);

ALTER TABLE books
  ADD COLUMN publisher_id integer REFERENCES publishers (publisher_id);

CREATE TABLE genres (
  genre_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     text NOT NULL UNIQUE
);

CREATE TABLE book_genres (
  book_id  integer NOT NULL REFERENCES books (book_id),
  genre_id integer NOT NULL REFERENCES genres (genre_id),
  PRIMARY KEY (book_id, genre_id)
);

CREATE TABLE wishlist_items (
  customer_id integer NOT NULL REFERENCES customers (customer_id),
  book_id     integer NOT NULL REFERENCES books (book_id),
  added_on    date    NOT NULL DEFAULT current_date,
  PRIMARY KEY (customer_id, book_id)
);

-- migrate existing data
INSERT INTO genres (name)
SELECT DISTINCT genre FROM books;

INSERT INTO book_genres (book_id, genre_id)
SELECT b.book_id, g.genre_id
FROM books AS b
JOIN genres AS g ON g.name = b.genre;

-- Kindred gains a second genre
INSERT INTO genres (name) VALUES ('time travel');
INSERT INTO book_genres (book_id, genre_id)
SELECT 7, genre_id FROM genres WHERE name = 'time travel';

SELECT b.title, string_agg(g.name, ', ' ORDER BY g.name) AS genres
FROM books AS b
JOIN book_genres AS bg USING (book_id)
JOIN genres      AS g  USING (genre_id)
WHERE b.book_id IN (1, 7)
GROUP BY b.book_id
ORDER BY b.book_id;

ROLLBACK;

        title         |       genres
----------------------+---------------------
 A Wizard of Earthsea | fantasy
 Kindred              | sci-fi, time travel
(2 rows)

#Key takeaways

• Derive tables from requirements: entities become tables, relationships become foreign keys, and many-to-many relationships (especially ones with their own attributes) become junction tables.
• Redundant facts cause update, insertion and deletion anomalies; normalization removes them by giving each fact one home.
• 1NF: one value per cell. 2NF: no dependency on part of a composite key. 3NF: no dependency between non-key columns.
• A value that records history, like the price paid, is a separate fact, not redundancy.
• Pick types that reject bad data: numeric for money, timestamptz for instants, date, boolean, identity keys.
• Denormalize only for a measured need, and decide how the copy is kept correct.