How to Design a Database Schema (That You Won't Have to Rewrite)

Quick answer

• Design from your queries backward, not from your entities forward. The question “what will I ask this data” decides the tables.
• Get these four right and the schema survives: primary keys, relationships with foreign keys, column types, and constraints. Everything else is editable later.
• Let the database enforce what it can — NOT NULL, UNIQUE, CHECK, foreign keys. Application-level validation is a suggestion.
• Index for the queries you identified, and only those. Every index taxes writes.
• Add columns in a nullable state, backfill, then enforce — that’s the migration order that doesn’t break a running deploy.
• Verify with realistic row counts before you call the design done; ten rows hide every mistake.

Step 1: Write down the questions the app will ask

Before any table exists, list the questions your application needs answered, in plain language, with the frequency you expect. “Show the last 20 orders for a user, newest first” and “total revenue per month” are schema decisions; “store orders” is not.

The list does two jobs: it tells you which relationships need to exist, and it becomes the index list in Step 7. Queries you can’t name are queries you can’t design for — and those are exactly the ones that get slow later.

What success looks like: a written list of five to fifteen queries, each phrased as a question with a sort order and a filter. If you can’t write it, you don’t know the feature yet.

Step 2: List entities and give each one a primary key

Each noun that survives from Step 1 becomes a table. Give every table a primary key, and make it a surrogate key — a generated value with no business meaning.

CREATE TABLE users (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT users_email_unique UNIQUE (email)
);

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id     bigint      NOT NULL REFERENCES users (id),
  status      text        NOT NULL DEFAULT 'pending',
  total_cents integer     NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Why surrogate instead of using email as the key: natural keys change. When someone updates their email, every row referencing it has to be rewritten, and every external system holding that identifier breaks. Keep email as a UNIQUE constraint — the rule is still enforced, but identity stays stable.

Use a UUID instead of a bigint when the key is exposed in URLs and you don’t want IDs to be guessable or to reveal volume. Otherwise an identity column is smaller, faster to index, and easier to sort.

What success looks like: every table has a single-column primary key with no business meaning, and every real-world uniqueness rule appears as a UNIQUE constraint.

Step 3: Model the relationships and set their cardinality

For each relationship in your Step 1 queries, decide one-to-many or many-to-many, then express it in the structure.

One-to-many is a foreign key on the “many” side — orders.user_id, as above. Many-to-many needs a join table with a composite primary key, which also prevents duplicate pairings:

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

Choose the delete behavior deliberately. ON DELETE CASCADE deletes children when a parent goes; ON DELETE RESTRICT refuses the delete while children exist. Cascading deletes are convenient and can quietly remove data you wanted to keep, so use them where the child genuinely has no meaning without the parent.

What success looks like: you can trace any relationship in your query list to a foreign key or a join table, and you know what happens to children when a parent is deleted.

Step 4: Decide what belongs in a row versus a separate table

The rule: store a fact once, in the table that owns it. If a value appears in two places, they can disagree, and nothing in the database will stop them.

That gives you two decisions:

• Normalize what you update. A user’s plan should live on users, not copied onto every order — otherwise changing it means rewriting history or living with contradictory rows.
• Denormalize what you read whole, for a measured reason. A total_cents on orders duplicates a sum of line items. That’s a deliberate, defensible denormalization because the order total is a fact that must not change when a product’s price does. The order records the price at time of sale; the product records today’s price.

Where a value is genuinely optional and shape-varies per record, a JSON column is a reasonable middle ground — but if you’re querying into it for filtering, it wants to be a real column with a real index.

What success looks like: for every value stored twice, you can explain why the duplicate is a different fact rather than the same fact in two places.

Step 5: Choose column types deliberately

Type choices are hard to change once data exists, so make them once, on purpose:

• Money: never floating point. Use numeric(12,2) or integer cents. Binary floats can’t represent most decimal fractions exactly, so totals drift.
• Timestamps: timestamptz, and store UTC. A naive timestamp has no timezone, and the bug appears the first time you have users in two of them.
• Text: text with a CHECK for length, instead of varchar(n) chosen arbitrarily. A length limit is a business rule; express it as one.
• Enums and status: text with a CHECK constraint, or a lookup table. A database enum is painful to extend; text plus a constraint is easy to add a value to later.
• Nullable means optional. If a column cannot legitimately be empty, mark it NOT NULL and let the database refuse the insert.

What success looks like: no float money columns, no timezone-free timestamps, and every business rule about a value expressed as a constraint rather than a convention.

Step 6: Add the constraints the database can enforce

Application validation runs only when code goes through the application. The database enforces its rules for every writer, including migrations, backfills, ad-hoc scripts, and the second service you add next year.

ALTER TABLE orders
  ADD CONSTRAINT orders_status_valid
    CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'));

ALTER TABLE orders
  ADD CONSTRAINT orders_total_non_negative
    CHECK (total_cents >= 0);

A unique constraint on the right set of columns is what makes an idempotent write safe: UNIQUE (user_id, idempotency_key) means a retried request inserts nothing the second time instead of duplicating the row.

What success looks like: deliberately insert a violating row and watch the database reject it. If the insert succeeds, that rule only exists in your application code.

Step 7: Index for the queries from Step 1

Go back to your query list and index the columns each one filters and sorts on. The common patterns:

• Filter plus sort: a composite index in that order — (user_id, created_at DESC) serves “this user’s orders, newest first.”
• Foreign keys: most engines don’t index them automatically. Without an index on orders.user_id, deleting a user scans the orders table.
• Uniqueness: a UNIQUE constraint is an index, so it also speeds up lookups by that column.
• Partial indexes: an index with WHERE covers a frequently queried subset — active records, unprocessed jobs — and costs less than indexing everything.

Then confirm the plan instead of assuming:

EXPLAIN ANALYZE
SELECT id, total_cents FROM orders
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

Success is an Index Scan rather than a Seq Scan over orders, with the row estimate close to what you expected.

What success looks like: every query in your Step 1 list has an index that serves it, and one EXPLAIN ANALYZE per important query confirming it’s used.

Step 8: Sequence the first migration so deploys survive it

Schema changes run against a live database while old and new application code are both running. That constraint dictates the order, and it’s the step AI assistants skip.

The safe pattern for adding a required column:

1. Add the column as nullable. Deploy. Both code versions work.
2. Backfill existing rows in batches, and have new writes populate it too.
3. Add the NOT NULL constraint once no nulls remain. Deploy again.

For removals, do it in reverse: stop writing to the column, deploy, then drop it in a later migration. A single migration that adds a NOT NULL column with no default will fail on a table with rows, and a migration that drops a column the running code still writes to produces errors the moment it applies.

Every migration should also be reviewed for one question: can this run while the old version of the app is still serving traffic?

What success looks like: you can describe, in order, what happens to in-flight requests at each step — and nothing in the sequence requires downtime.

• What Are Database Migrations (and Why Do They Break Deploys)? — Migration tooling, versioning, and rollback.
• How to Roll Back a Bad Deploy — The deploy-side half of the same problem.
• What Is a Database Transaction (ACID)? — What wraps a multi-step write so it can’t half-apply.

Step 9: Verify against realistic data before calling it done

A schema tested with ten rows passes every check. Load a realistic volume — hundreds of thousands of rows in the big tables, with the same distribution you expect — and re-run your five most important queries with EXPLAIN ANALYZE. This is the step that catches a missing index, a query that can’t use the index you added, and an N+1 pattern in the code that generates it.

• What Is Database Indexing (and Why Is My Query Slow)? — Reading plans and choosing the right index.
• What Is the N+1 Query Problem? — The pattern that looks fine on a seeded database.
• How to Add an Index to a Slow SQL Query — The fix, once you’ve found the query.
• What Is a Connection Pool? — The design decision that shows up under concurrency rather than in a query plan.

Where this bites vibecoders

A design session with an AI assistant usually starts from the feature description, so you get tables that mirror the screens rather than the queries: a users table with a password, a plan, and a last_login, an orders table with the customer’s name and email copied onto every row, no foreign keys because the assistant validated the relationship in application code, and status stored as a free-text string. It runs, it passes the tests, and it’s wrong in ways that only appear later — the copied email goes stale after a profile update, the free-text status accumulates typos that break every filter, and there’s no index on user_id, so the orders page degrades as the table grows. None of these are fixable by prompting for a better schema once data exists; they’re migrations with backfills on live tables. The cheapest intervention is to hand the assistant your query list first and ask for a schema that serves those queries, then check the result against the four durable decisions: keys, relationships, types, constraints.

Checklist

• Write the top ten queries down before designing a single table.
• Give every table a surrogate primary key and keep natural keys as unique constraints.
• Express every relationship as a foreign key, and choose cascade behavior on purpose.
• Store each fact once; denormalize only where you can name the reason.
• Never store money in a floating-point column.
• Use timezone-aware timestamps and store them in UTC.
• Push rules into the database: NOT NULL, UNIQUE, CHECK, foreign keys.
• Index the filter-and-sort columns from your query list, and check the plan.
• Add columns nullable, backfill, then enforce — never in one migration.
• Test with realistic row counts before shipping the schema.

FAQ

How should I choose a primary key?

Use a surrogate key — a generated integer or UUID — rather than a natural one like an email address or a product code. Natural keys change, and when they do you’re cascading updates through every foreign key that references them. Keep a separate unique constraint on the natural key so the rule is still enforced, and let the surrogate key handle identity.

Should I normalize or denormalize my tables?

Normalize by default: store each fact once, in the table that owns it, and join to read it. Denormalize deliberately, in a specific place, for a measured performance reason — a cached aggregate column, or a copy of a name you display on every row. Normalization prevents contradictory data; denormalization is a trade you make when you know which query hurts.

How do I design a schema before I know all my queries?

Design for the queries you can name now and accept that the schema will change — that’s what migrations are for. Getting the entities, keys, relationships, and constraints right is the durable part, because those are expensive to change later. Adding a column, an index, or a table is cheap, so leaving room to extend is more valuable than predicting everything up front.

Do I need a soft delete column?

Only if you need to recover records or preserve history for audit. A deleted_at timestamp means every query in your codebase must filter on it, and the one that forgets surfaces deleted data — a security and correctness problem. For most apps, hard deletes plus a reliable backup are simpler and safer; add soft deletes when a requirement actually names them.

Related topics

• How to Choose a Database for Your App: A Decision Guide
• What Are the Different Types of Databases (and What Is Each One For)?
• The 12 Database Mistakes That Show Up in AI-Generated Apps
• Databases for AI Builders: Why Your AI-Generated App Gets Slow and Loses Data
• What Are Database Migrations (and Why Do They Break Deploys)?
• What Is Database Indexing (and Why Is My Query Slow)?

Sources

• PostgreSQL Documentation — Constraints
• PostgreSQL Documentation — Numeric Types
• PostgreSQL Documentation — Date/Time Types
• Use The Index, Luke — Indexing for sorting and filtering
• PostgreSQL Documentation — Using EXPLAIN