# Schema design & normalization > Turn requirements into entities and relationships, normalize away update anomalies, and choose sensible PostgreSQL data types. SQL & Databases roadmap · Design & performance · 18 min read · https://levelcode.org/roadmaps/sql/schema-design 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?": | Relationship | Cardinality | Required? | Implementation | | --- | --- | --- | --- | | author writes book | one author : many books | a book must have an author; an author may have none | `books.author_id NOT NULL REFERENCES authors` | | customer places order | one : many | an order must have a customer | `orders.customer_id NOT NULL REFERENCES customers` | | order contains book | many : many | an order needs at least one item | junction table `order_items` | | customer refers customer | one : many (self) | optional | nullable `customers.referred_by` | 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: ```text 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`. > [!TIP] > The classic summary: every non-key column must depend on **the key, the whole key, and nothing but the key**. For each column, ask "what is this a fact about?" If the answer is not "the thing this table's primary key identifies", the column is in the wrong table. 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: | Data | Use | Avoid | | --- | --- | --- | | Surrogate keys | `integer` or `bigint GENERATED ALWAYS AS IDENTITY`; `uuid` when IDs are created outside the database | `serial` in new code (identity columns are the standard replacement) | | Money and exact quantities | `numeric(p, s)`, e.g. `numeric(10,2)`, or integer cents | `real`, `double precision`, `money` | | Text | `text`; add a length rule with a `CHECK` only when there is a real business limit | `char(n)`, which pads with spaces | | Points in time | `timestamptz` | `timestamp` without time zone for events | | Calendar dates | `date` | text such as `'05/01/2025'` | | Yes/no | `boolean` | `'Y'`/`'N'` strings or 0/1 integers | | Semi-structured extras | `jsonb` | `json` (unless you need to keep exact formatting), or `jsonb` for core columns you filter and join on | Two of these deserve a demonstration. Floating-point types store binary approximations, so cents go missing: ```sql 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; ``` ```text 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`). > [!WARNING] > Two designs look flexible but cause lasting pain. **Entity–attribute–value** tables (`entity_id, attribute_name, value text`) throw away types and constraints and make every query a pile of self-joins. **Polymorphic foreign keys** (`commentable_type, commentable_id` pointing at different tables) cannot be enforced by a real foreign key. Prefer proper columns, separate junction tables per relationship, or a `jsonb` column for genuinely free-form extras. ## 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 `_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`. **Solution** 1. A list in one column violates **1NF**: you cannot index, constrain or join individual genres. Use a `genres` table and a `book_genres` junction table. 2. `publisher_country` depends on the publisher, not the book — a transitive dependency that violates **3NF**. Renaming a publisher would be an update anomaly across all its books. Use a `publishers` table. 3. A list of titles violates 1NF, stores titles (which are not unique keys) instead of IDs, and has nowhere to put "date added". The wishlist is a many-to-many relationship with its own attribute, so it needs a junction table. ```sql 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; ``` ```text title | genres ----------------------+--------------------- A Wizard of Earthsea | fantasy Kindred | sci-fi, time travel (2 rows) ``` `publisher_id` is nullable for now because existing books have no publisher yet; once every book has one, it can be made `NOT NULL`. In a real migration you would drop `books.genre` only after all application code reads from `book_genres`. The composite primary keys on the junction tables stop the same genre or wishlist entry being added twice. Because DDL in PostgreSQL is transactional, the `ROLLBACK` undoes even the `CREATE TABLE` statements. ## 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. ## Quiz 1. order_items has primary key (order_id, book_id). Someone adds a title column copied from books. Which normal form does this violate? A. First normal form B. Second normal form C. Third normal form D. None — it is a valid optimisation **Answer: B.** title depends on book_id alone, which is only part of the composite key. A dependency on part of the key is exactly what 2NF forbids; title belongs in books. 2. Which type is the best default for storing book prices in PostgreSQL? A. double precision B. real C. text D. numeric(10,2) **Answer: D.** numeric stores exact decimal values, so 0.10 + 0.20 is exactly 0.30. Floating-point types store binary approximations and accumulate rounding errors, which is unacceptable for money. 3. Sales are stored in one wide table with the customer's city repeated on every row. Ada moves, and only some of her rows get updated. What is this called? A. An update anomaly B. An insertion anomaly C. A deletion anomaly D. A referential integrity violation **Answer: A.** Storing the same fact in many rows means an update must change all of them; missing some leaves the data contradicting itself. Moving city to a customers table stores it once. 4. Why does order_items store unit_price even though books has a price column? A. It is a denormalized cache that should be removed B. It speeds up joins between orders and books C. It records the price actually paid at purchase time, which is a different fact from the current list price D. Foreign keys require it **Answer: C.** List prices change; what a customer paid does not. Order 1 bought Guards! Guards! at 9.99 though it now costs 10.99. Removing unit_price would silently rewrite historical orders.