Chapter 5 — Designing Your Database

The why

You can rename a TypeScript class in 30 seconds. Renaming a column in a production database with 10 million rows requires: a migration, a code change, dual-writing during rollout, a backfill job, and a second migration to remove the old column. That's days of work.

Code is easy to change. Database schemas — especially with live data — are hard.

This is why you spend a full day thinking about the schema before you write any code.

Start from the business, not the screens

The 5 questions before creating any table

Before you create a new table, answer these questions. If you can't answer them, you're not ready.

1. What is this thing's identity?

Use UUID as the primary key by default. It's globally unique, safe to expose in URLs, and doesn't leak how many records you have.

id UUID PRIMARY KEY DEFAULT gen_random_uuid()

2. What does it own vs. what does it reference?

Owned data lives here or in a child table:

-- Order owns its items
order_items (id, order_id REFERENCES orders(id) ON DELETE CASCADE, ...)

Referenced data lives in another table:

-- Order references a user, but doesn't own the user
orders (id, user_id REFERENCES users(id) ON DELETE RESTRICT, ...)

ON DELETE CASCADE = if the parent is deleted, delete children too (ownership)
ON DELETE RESTRICT = if something still references this, refuse to delete it

3. What must always be true?

Encode rules as database constraints, not just application code:

status VARCHAR(20) NOT NULL DEFAULT 'pending'
  CHECK (status IN ('pending', 'approved', 'rejected', 'completed'))

Now even if a bug tries to set status = 'banana', the database rejects it.

4. How will this be queried most often?

Write your top 3 SELECT queries before creating the table. Then create indexes for them.

5. How much write traffic will this see?

High write traffic → fewer indexes (each index slows down inserts).
High read traffic → more indexes (each index speeds up selects).

The entity relationships for this project

Naming conventions — pick once, never break

Tables: snake_case, plural

users
restaurants
menu_items
order_items

Columns: snake_case

created_at
user_id
is_open
total_amount

Boolean columns: prefix with is_ or has_

is_active
is_open
has_delivery

Foreign keys: <referenced_table_singular>_id

user_id          → references users(id)
restaurant_id    → references restaurants(id)
menu_item_id     → references menu_items(id)

Timestamps: always created_at, updated_at, and optionally deleted_at

Junction tables (many-to-many): alphabetical, both names

restaurant_categories   (not categories_restaurants)

Choosing the right column type

Constraints are free correctness

Here is a real QuickBite orders table with proper constraints:

CREATE TABLE orders (
    id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID         NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    restaurant_id UUID         NOT NULL REFERENCES restaurants(id) ON DELETE RESTRICT,
    status        VARCHAR(20)  NOT NULL DEFAULT 'pending'
                               CHECK (status IN ('pending', 

Notice:

• Every column has NOT NULL or a default — no accidental nulls
• status is constrained at the DB level — bad data can’t sneak in from a SQL console
• total_amount > 0 — zero-amount orders are rejected at the database level

Normalization — don't store the same fact twice

Bad (denormalized):

orders (id, user_id, user_name, user_email, restaurant_name, total_amount, ...)

Now if the user updates their name, you have to update every order row. Miss one — your data is inconsistent.

Good (normalized):

orders (id, user_id, restaurant_id, total_amount, ...)
-- To get user name, JOIN to users table
-- To get restaurant name, JOIN to restaurants table

When is denormalization OK?

Only when you have a measured performance problem AND the denormalized field is derivable. Name it to make the intent obvious: restaurant_name_at_order_time — clearly a snapshot, not a live value.

Soft delete vs hard delete

Hard delete: Row is gone. Simple. Recommended for most tables.

Soft delete: Row has a deleted_at column. When "deleted", set deleted_at = now(). Used when:

• Legal/compliance requires keeping records
• You need to be able to "undo" the deletion

deleted_at TIMESTAMPTZ  -- NULL means active; set to now() to "delete"

With Sequelize, paranoid: true automatically adds WHERE deleted_at IS NULL to all queries.

Audit columns — mandatory on every table

Every table that holds business state must have these columns:

created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
created_by  UUID,    -- FK to users table (NULL if system-created)
updated_by  UUID     -- FK to users table

For important tables (orders, payments), add an audit log table too:

CREATE TABLE order_status_changes (
    id          BIGSERIAL    PRIMARY KEY,
    order_id    UUID         NOT NULL REFERENCES orders(id),
    from_status VARCHAR(20)  NOT NULL,
    to_status   VARCHAR(20)  NOT NULL,
    changed_by  UUID         NOT NULL,
    changed_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
    reason      TEXT
);

When a customer asks “why was my order cancelled?” you need to answer. Without this table, you can’t.

Common mistakes

One thing to remember