Normalization and Schema Design

Table of Contents

1. The One Big Table Trap
2. The Three Anomalies
3. The Idea Behind Normalization
4. First Normal Form: One Value per Cell
5. Second Normal Form: Depend on the Whole Key
6. Third Normal Form: Nothing but the Key
7. Reviewing shopdb
8. Why unit_price Is a Deliberate Copy
9. Many-to-Many and Naming Conventions
10. Choosing Data Types Well
11. Soft Delete, and When to Denormalize
12. A Design Checklist
13. Migrations: Numbered SQL Files
14. Try It Yourself

1. The One Big Table Trap

Everyone who comes from spreadsheets designs their first table the same way: put everything in one place. An order has a customer, the customer has a city, the order has products, the products have prices. So why not one table with a column for each?

Here is that design, as a scratch table (we will not touch shopdb in this section). The data is the first three orders of our shop:

CREATE TABLE orders_flat (
  order_id       integer,
  customer_name  text,
  customer_email text,
  customer_city  text,
  product_name   text,
  product_price  numeric(10,2),
  quantity       integer
);

INSERT INTO orders_flat VALUES
  (1, 'Sokha Chan', 'sokha@example.com', 'Phnom Penh', 'Wireless Mouse', 15.00, 1),
  (1, 'Sokha Chan', 'sokha@example.com', 'Phnom Penh', 'USB-C Cable',     5.50, 2),
  (2, 'Sokha Chan', 'sokha@example.com', 'Phnom Penh', 'Mechanical Keyboard', 79.00, 1),
  (3, 'Dara Kim',   'dara@example.com',  'Siem Reap',  'SQL Cookbook',   32.00, 1),
  (3, 'Dara Kim',   'dara@example.com',  'Siem Reap',  'Clean Code',     28.50, 1);

 order_id | customer_name | customer_email    | customer_city | product_name        | product_price | quantity
----------+---------------+-------------------+---------------+---------------------+---------------+---------
        1 | Sokha Chan    | sokha@example.com | Phnom Penh    | Wireless Mouse      |         15.00 |        1
        1 | Sokha Chan    | sokha@example.com | Phnom Penh    | USB-C Cable         |          5.50 |        2
        2 | Sokha Chan    | sokha@example.com | Phnom Penh    | Mechanical Keyboard |         79.00 |        1
        3 | Dara Kim      | dara@example.com  | Siem Reap     | SQL Cookbook        |         32.00 |        1
        3 | Dara Kim      | dara@example.com  | Siem Reap     | Clean Code          |         28.50 |        1

It works for a demo. Notice how much repeats: Sokha's name, email, and city appear three times. Each product's price is written on every row that mentions it. That repetition is called redundancy, and it is where the trouble starts.

2. The Three Anomalies

Redundancy causes three kinds of bugs, called anomalies. Each one is a way the table can lie.

Update anomaly. Sokha moves to Siem Reap. You must change the city on every row that mentions Sokha:

UPDATE orders_flat
SET customer_city = 'Siem Reap'
WHERE order_id = 1;

You updated order 1 and forgot order 2. Now the same customer lives in two cities, and no rule in the database says which one is true.

Insert anomaly. A new customer, Vuthy, signs up but has not ordered yet. Where do you store him? Every row needs an order_id and a product. You cannot record a customer with no order, unless you fill the row with NULLs and fake data. The same goes for a new product nobody has bought.

Delete anomaly. Dara cancels and you delete order 3 to clean up:

DELETE FROM orders_flat WHERE order_id = 3;

Dara's email and city vanish too, along with the fact that "SQL Cookbook" costs 32.00. You wanted to delete an order and accidentally deleted a customer and two products.

Clean up when you are done:

DROP TABLE orders_flat;

3. The Idea Behind Normalization

Normalization is the process of splitting a table so that each fact is stored in exactly one place. If a customer's city is stored once, it cannot disagree with itself. If customers live in their own table, you can add one with no orders and delete an order without losing one.

You already did this when you built shopdb. The flat table above became customers, products, orders, and order_items, connected by keys. The theory behind it is a set of rules called normal forms, numbered 1NF, 2NF, 3NF, and beyond. Each form builds on the last. For everyday work, 3NF is the goal, and there is a catchy summary of it that we will meet in section 6.

Do not memorize the formal definitions. Learn to ask one question about every column: "Is this a fact about the thing this row represents?"

4. First Normal Form: One Value per Cell

1NF says: every cell holds a single value, and there are no repeating groups. In other words, no lists crammed into a column.

A tempting violation is storing the items of an order in one cell:

 order_id | customer | products
----------+----------+--------------------------------
        1 | Sokha    | Wireless Mouse, USB-C Cable
        3 | Dara     | SQL Cookbook, Clean Code

Now try to answer "how many orders contain a USB-C Cable?" You are parsing text with LIKE '%USB-C Cable%'. Try "what is the price of the second product?" There is no way to ask. Try adding a product: you edit a string. The database cannot enforce a foreign key on a piece of a string.

The other violation is numbered columns: product1, product2, product3. What happens with the fourth product? You add a column and rewrite every query.

The fix is the same in both cases: one row per item. That is exactly what order_items is. An order with three products has three rows there, as many as it needs, and every one can be joined, counted, and constrained.

Quick test for 1NF: if you ever want to write split(',') on a column, the design is wrong.

5. Second Normal Form: Depend on the Whole Key

2NF only matters when a table has a composite primary key (two or more columns). It says: every non-key column must depend on the whole key, not just a part of it.

Look at orders_flat again. What identifies a row? The pair (order_id, product_name). Now ask about each other column:

• quantity: depends on both. It is how many of this product are in this order. Good.
• customer_name: depends only on order_id. Knowing the order tells you the customer, no matter the product. That is a partial dependency.
• product_price: depends only on product_name. The order is irrelevant. Another partial dependency.

Partial dependencies are the source of the repetition from section 1. The fix is to move each fact to a table keyed by what it really depends on:

Our order_items has the composite key (order_id, product_id) and its only other columns are quantity and unit_price, which belong to that specific pair. It passes 2NF. (We will look more closely at unit_price in a moment.)

6. Third Normal Form: Nothing but the Key

3NF says: non-key columns must depend on the key, and not on other non-key columns. This is a transitive dependency and it is easy to miss.

A tempting design for orders adds the customer's city for faster reports:

 orders
 id | customer_id | customer_city | status
----+-------------+---------------+--------
  1 |           1 | Phnom Penh    | paid

customer_city depends on customer_id, which is a non-key column of orders. The chain is orders.id -> customer_id -> city. The city is a fact about the customer, not the order, so it belongs in customers where it is stored once. Break 3NF and you get the update anomaly again: change the customer's city and the old orders disagree.

A well-known one-line summary of the first three forms: every non-key column must provide a fact about the key, the whole key, and nothing but the key.

Here is the table to remember:

7. Reviewing shopdb

Let us audit our schema from chapter 6 table by table. Connect to the database and look at each with \d:

shopdb=# \d products
                               Table "public.products"
   Column    |     Type      | Collation | Nullable |           Default
-------------+---------------+-----------+----------+------------------------------
 id          | integer       |           | not null | generated always as identity
 name        | text          |           | not null |
 price       | numeric(10,2) |           | not null |
 stock       | integer       |           | not null | 0
 category_id | integer       |           |          |

(Typical output; yours may differ slightly in spacing.)

That subtle column is unit_price. It looks like a 3NF violation, since the price is already in products. Let us look at it.

8. Why unit_price Is a Deliberate Copy

order_items.unit_price repeats information from products.price. By the rules above, that is redundancy. Why did we do it?

Because the two prices are different facts:

• products.price answers "what does this cost now?"
• order_items.unit_price answers "what did the customer pay when they bought it?"

Imagine you drop the column and always join to products.price. In March you raise the Wireless Mouse from 15.00 to 18.00. Every past order for the mouse now appears to have been sold at 18.00. January's revenue report changes retroactively, invoices you already sent no longer match, and your accountant is unhappy.

You can see the difference in one query:

-- Update the live price. Past orders keep what was actually paid.
UPDATE products SET price = 18.00 WHERE id = 1;

SELECT p.name,
       p.price      AS price_now,
       oi.unit_price AS price_paid
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.product_id = 1;

      name      | price_now | price_paid
----------------+-----------+------------
 Wireless Mouse |     18.00 |      15.00
 Wireless Mouse |     18.00 |      15.00
(2 rows)

(The two rows are order 1 and order 4.) Put the price back so later chapters match:

UPDATE products SET price = 15.00 WHERE id = 1;

This is a snapshot: a value copied at one moment because it should not change when the original does. The same idea applies to the shipping address on an order, the tax rate, or the product name on an invoice. Rule of thumb: if the copy must stay the same when the source changes, it is history, not redundancy. If the copy must follow the source, it is a bug waiting to happen.

9. Many-to-Many and Naming Conventions

You met many-to-many in chapter 6: an order has many products, and a product appears in many orders. A relational database cannot store this directly in either table, so you add a third table in the middle (a junction table, also called a join or link table). order_items is one. Its composite primary key (order_id, product_id) also stops the same product from appearing twice in the same order.

If the junction needs no extra columns, it can be just two foreign keys. Here is a scratch example for tagging products:

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

CREATE TABLE product_tags (
  product_id integer REFERENCES products(id) ON DELETE CASCADE,
  tag_id     integer REFERENCES tags(id)     ON DELETE CASCADE,
  PRIMARY KEY (product_id, tag_id)
);

Names are part of the design too. These conventions keep a schema predictable, and shopdb already follows them:

PostgreSQL folds unquoted names to lowercase, so CustomerName becomes customername. Mixed case forces you to quote forever. Stick to snake_case and never think about it.

Pick a convention and keep it. Consistency matters more than which one you pick.

10. Choosing Data Types Well

A column's type is a constraint too. Revisit the choices from chapter 3 with design in mind:

Two more habits. Mark columns NOT NULL unless "unknown" is a real, meaningful state (like a missing city). And write the CHECK constraints. A constraint in the database protects every program that touches it, not only the one you wrote today.

11. Soft Delete, and When to Denormalize

Soft delete. Sometimes you do not want rows to vanish. A common pattern adds a deleted_at timestamptz column. "Deleting" sets it to now(), and every query adds WHERE deleted_at IS NULL:

ALTER TABLE products ADD COLUMN deleted_at timestamptz;

UPDATE products SET deleted_at = now() WHERE id = 6;     -- soft delete
SELECT * FROM products WHERE deleted_at IS NULL;         -- active only

It keeps history and makes mistakes reversible. The costs are real, too: every query must remember the filter (a view helps), unique constraints get complicated (two "deleted" users can share an email), and personal data you promised to erase is still there. Use it when you need history, not by default. (Our orders already avoid deletion differently: a cancelled status.)

If you tried that example, undo it with:

ALTER TABLE products DROP COLUMN deleted_at;

When to denormalize. Normalization trades storage and write simplicity for correctness. Occasionally you deliberately break a rule, usually for speed or for history:

• Snapshots like unit_price, covered above.
• Cached totals, such as an orders.total column so a list page does not sum thousands of rows. You must keep it in sync, ideally in the same transaction (chapter 12).
• Reporting tables copied nightly from the normal ones for analytics.

The order matters: normalize first, measure, then denormalize a specific spot with a reason you can write down. Never start flat "for performance" before you have a performance problem. Chapter 11 shows how indexes solve most of them without breaking the design.

12. A Design Checklist

Before you commit to a new table, walk through this list:

1. What one thing is a row? Say it in a sentence: "a row is one customer". If you need "and", you have two tables.
2. Does it have a primary key? Usually a surrogate id.
3. Is every column a fact about that one thing? If not, move it.
4. Does any column repeat a list? a, b, c in a cell: make a child table.
5. Which relationships exist? For each, decide one-to-many or many-to-many and where the foreign key lives (always on the "many" side).
6. What happens on delete? Pick RESTRICT, CASCADE, or SET NULL on purpose, not by default.
7. What must never be NULL, negative, or duplicated? Write the NOT NULL, CHECK, and UNIQUE.
8. Right types? numeric for money, timestamptz for time.
9. Which copies are snapshots? Document them, as with unit_price.
10. Which columns will queries filter or join on? You will index them in the next chapter, including foreign keys.

13. Migrations: Numbered SQL Files

Your first design is never your last. Requirements change, and the database already holds real data. A migration is a small SQL file that moves the schema from one version to the next. You have already written one: in chapter 6 you altered products to use category_id.

The simple, tool-free habit is to keep migrations as numbered files that run in order:

shopdb/
  schema.sql              # the current full schema, for fresh setups
  seed.sql
  migrations/
    001_add_categories.sql
    002_add_orders_and_items.sql
    003_add_deleted_at_to_products.sql

A migration contains only the change, and ideally is safe to read:

-- migrations/003_add_deleted_at_to_products.sql
BEGIN;
ALTER TABLE products ADD COLUMN deleted_at timestamptz;
COMMIT;

Rules that keep this sane:

• Never edit a migration that has already run on a shared database. Add a new, higher-numbered one instead.
• One purpose per file, so a failure is easy to understand.
• Apply them in order, and record which have run (frameworks keep a table for this; by hand, a note in the README works for a learning project).

docker exec -i shopdb-pg psql -U postgres -d shopdb \
  < migrations/003_add_deleted_at_to_products.sql

Real projects use a migration tool (Flyway, Prisma Migrate, TypeORM, and others) that tracks this for you. The concept is identical to the numbered files you just made.

14. Try It Yourself

1. Spot the anomaly. A table employees(id, name, department_name, department_phone) stores the department phone on every employee. Name one update, one insert, and one delete anomaly. Which normal form does it break?
2. Fix a list column. A table has students(id, name, courses) where courses holds 'Math, SQL, Art'. Design the replacement tables with keys. What kind of relationship is it?
3. Normalize it. Take this flat table and design the tables (columns and keys) it should become: invoices(invoice_no, date, client_name, client_address, item_description, item_price, qty). Which columns would you keep as snapshots and why?
4. Run the price experiment. Do the UPDATE products SET price experiment from section 8 on your own database. Then write a query that shows, for every order item, the difference between the price now and the price paid.
5. Add a tag system. Create tags and product_tags from section 9 as a migration file 005_add_tags.sql. Insert two tags and attach them to three products, then list each product with its tags using joins.
6. Audit your own table. Pick a table from a project you have built (or from the shop). Walk the checklist in section 12. Write down one thing you would change.

Where to go next. Your design is clean and correct. The next question is speed: what happens when orders has a million rows instead of five? Chapter 11, Indexes and Query Performance, shows how PostgreSQL finds rows, how to read EXPLAIN, and how one CREATE INDEX can turn a slow query into an instant one.