Database Schema Design

Choosing keys and types, how indexes really work, and why schema mistakes are the most expensive kind.

You can rewrite a bad function in an afternoon. Rewriting a bad schema means migrating live data, updating every query that touches it, and coordinating a deploy that cannot be rolled back once the data has moved.

Schema mistakes are the most expensive mistakes in application development, because the data outlives the code. Your service will be rewritten twice; the users table will survive both.

This lesson covers the decisions that are hard to reverse.

Choose types deliberately

Types are constraints. A well-chosen type makes an entire class of bad data impossible.

CREATE TABLE orders (
  id           BIGSERIAL     PRIMARY KEY,
  user_id      BIGINT        NOT NULL REFERENCES users(id),
  total_cents  INTEGER       NOT NULL CHECK (total_cents >= 0),
  currency     CHAR(3)       NOT NULL,
  status       order_status  NOT NULL DEFAULT 'pending',
  created_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

Four decisions there are worth calling out.

Money is an integer of minor units, never a float. 0.1 + 0.2 is not 0.3 in floating point. Store 2999 cents, not 29.99. Every financial system does this, and the ones that did not have all rewritten it.

Timestamps carry a time zone. TIMESTAMPTZ stores an unambiguous instant. Plain TIMESTAMP records a wall-clock reading with no indication of where, so the same value means different moments in Mumbai and London — and daylight-saving transitions make some values ambiguous or impossible.

Constrained sets become enums or check constraints. If status can only be five values, say so in the schema. Otherwise the day arrives when you find 'Pending', 'pending' and 'PENDNIG' in the same column.

NOT NULL unless null means something. Null is not "empty" — it is "unknown", and it propagates surprisingly through comparisons and aggregates. Require a value unless absence is genuinely meaningful.

Primary keys

Every table needs one. The interesting question is what it should be.

Auto-incrementing integers are compact, sort naturally, and index efficiently. Their drawbacks: they reveal how many rows you have (a competitor reading /orders/1042 learns your volume), they allow enumeration of other people's records, and they collide when merging data from multiple systems.

UUIDs are unguessable and can be generated by clients without asking the database. But random UUIDs (v4) are a genuine performance problem at scale: because inserts land at random points in the index, the database rewrites pages all over the disk instead of appending. On a large table this measurably slows writes.

Time-sortable UUIDs (v7) or ULIDs are the modern compromise — globally unique but ordered by creation time, so inserts stay sequential and the index behaves like an auto-increment.

A common production pattern is both: an internal integer key for joins, and an external opaque id exposed in URLs and APIs.

Indexes: the trade you are making

An index is a sorted structure that lets the database find rows without scanning the table.

Without one, finding a user by email means reading every row — a full table scan. At ten million rows that is seconds. With an index it is a handful of page reads, effectively instant.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

The cost is real and worth stating plainly:

Writes get slower. Every INSERT, UPDATE and DELETE must update every affected index.
Storage grows. An index on a large table can approach the size of the table.

So index what you actually query, not everything.

Composite index column order matters

This is the detail that trips people up. An index on (user_id, created_at) can serve:

queries filtering on user_id alone, and
queries filtering on user_id and created_at.

It cannot efficiently serve a query filtering on created_at alone.

The rule of thumb: think of a phone book sorted by (surname, first name). Finding everyone called "Sharma" is easy. Finding everyone whose first name is "Priya", regardless of surname, means reading the whole book.

Put equality filters first, ranges last.

Normalisation, and when to stop

Normalisation means storing each fact exactly once. If a user's email lives only in users, changing it is one update and cannot produce disagreement.

The counter-force is that reading a fully normalised model requires joins, and joins across large tables cost time.

Denormalisation deliberately duplicates data to make reads cheaper — storing author_name on posts so listing posts needs no join.

The trade is stark:

	Normalised	Denormalised
Writes	Simple, one place	Must update every copy
Reads	Joins needed	Single-row read
Correctness	Cannot disagree	Copies can drift

Start normalised. It is the safer default, correctness is easier to reason about, and databases join efficiently at moderate scale. Denormalise only when you have measured a real read problem, and be honest that you are accepting the job of keeping copies in sync — because the day one update path forgets, your data disagrees with itself and nothing errors.

Soft deletes and their trap

Many systems never truly delete rows:

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

This preserves history and makes accidental deletion recoverable. It also introduces a subtle, permanent tax: every query must now remember WHERE deleted_at IS NULL, and the one that forgets shows deleted records to users.

If you adopt soft deletes, enforce them centrally — in the repository layer or a database view — rather than trusting fifty query sites to remember. And note that unique constraints stop working as expected: a deleted user still occupies their email address unless the constraint is made partial.

Migrations

Diagram, as text

Add nullable column leads to Deploy code that writes it
Deploy code that writes it leads to Backfill in batches
Backfill in batches leads to Any nulls left?
Any nulls left? leads to Backfill in batches (yes)
Any nulls left? leads to Add NOT NULL constraint (no)

Schema changes are code. They belong in version control, run automatically, and are reviewed like anything else.

Two rules save real outages:

Every migration needs a rollback. If a deploy fails at 2am, you need a tested way back.

Deploy schema changes and code changes separately. Adding a NOT NULL column in the same release as the code that populates it guarantees a window where old instances are writing rows without it. Use the expand-contract sequence: add the column as nullable, deploy code that writes it, backfill existing rows, then add the constraint.

Check your understanding

Your orders table has an index on (user_id, created_at). Which query can use it efficiently?

A `users` table uses a soft delete flag. What is the trap most teams hit?

Why should money be stored as an integer number of minor units, or a decimal type, rather than a floating-point number?

Common mistakes

No foreign keys "for performance". The saving is tiny; the cost is orphaned rows referencing users who no longer exist, discovered months later.

Storing structured data as JSON blobs. Convenient at first, then you cannot query or index inside it without pain. Use JSON for genuinely variable, rarely-queried attributes — not to avoid thinking about your model.

Indexing every column. Each index slows every write. Index what your queries filter and sort on.

Storing computed values that can drift. A cached comment_count on posts is fine if something reliably maintains it. Usually nothing does.

Booleans that should be timestamps. is_verified BOOLEAN tells you the state; verified_at TIMESTAMPTZ tells you the state and when — for the same storage.

Practice

Design the schema for an orders table that must support: fetching one order with its lines, listing a customer's recent orders, and a nightly report of yesterday's revenue by product. State the tables, keys and indexes, and justify each index.

Write your own answer first, on paper or in a note. Then compare.

Show a hint

Derive indexes from the three queries, and refuse to add any that no query needs.

Show a strong answer

Tables: orders (one row per order) and order_lines (one row per product on an order). Keep them separate rather than storing lines as a blob — the nightly report aggregates across lines from different orders, which a blob makes impossible without reading everything.

Keys:

orders.id — primary key. A sequential or time-ordered identifier so inserts append rather than scatter through the index.
order_lines.order_id — foreign key to orders.id, with the primary key on (order_id, line_number) so a single order's lines are physically adjacent.

Indexes, one per query and no more:

Fetch one order with its lines: served by the two primary keys. The order_lines key beginning with order_id means the lines are a contiguous range read, not scattered lookups. No extra index needed.
List a customer's recent orders: (customer_id, created_at DESC) on orders. The column order matters — customer first narrows to one customer, then the rows are already in the order the query wants, so there is no sort step. An index on customer_id alone would find the rows and then sort them.
Nightly revenue by product: (created_at) on orders to bound the date range. Deliberately not an index on order_lines.product_id — the report scans a whole day regardless, so an index would be maintained on every insert and used by nothing.

Money columns as integer minor units, with the currency stored explicitly alongside rather than assumed.

One thing to state aloud: order_lines should record the price as it was at the time of the order, not join to the product table for the current price. An order is a historical fact; a price change next month must not rewrite last month's revenue.

You must add a NOT NULL column to a table with 200 million rows in a live system. Describe a migration that does not lock the table or break running code.

Write your own answer first, on paper or in a note. Then compare.

Show a hint

Old code and new code will run at the same time. Every step must be safe for both.

Show a strong answer

The constraint is that the deploy is gradual, so at every moment some instances run old code and some new. A migration is safe only if both can operate against the schema as it stands at that moment.

Four steps, each independently deployable:

Add the column as nullable, with no default that requires a rewrite. Adding a nullable column is a metadata change in most engines and returns immediately. Old code ignores a column it does not know about; new code tolerates nulls. Do not add NOT NULL here — that forces a full table rewrite and a long lock.
Deploy code that writes the new column on every insert and update, while still tolerating nulls on read. Now new rows are correct and old rows are not.
Backfill in batches. Update a few thousand rows at a time with a pause between batches, so no single transaction holds locks for long and replication does not fall behind. This is the slow step and it runs for hours or days without affecting availability.
Verify no nulls remain, then add the constraint. Only once the backfill is provably complete — count the nulls, do not assume — apply NOT NULL. On some engines this still scans; check whether yours can validate an existing constraint without a lock.

What makes it safe: every step is backward compatible with the code deployed before it, so any step can be paused or rolled back independently. The failure mode to avoid is combining steps 1 and 4 into one migration, which is the version that locks a 200-million-row table during business hours.

Key takeaways

Schema mistakes are expensive to reverse because data outlives code. Spend more thought here than on ordinary functions.
Types are constraints: money as integer minor units, timestamps with time zone, enums for fixed sets, NOT NULL by default.
Auto-increment keys leak volume and allow enumeration; random UUIDs hurt insert performance. Time-sortable ids (v7/ULID) are the modern compromise.
An index trades faster reads for slower writes and more storage. Index what you query, not everything.
In a composite index, column order decides which queries it can serve — equality filters first, ranges last.
Start normalised, denormalise only against measured evidence, and accept that you now own keeping the copies in sync.
Treat migrations as reviewed code, always with a rollback, and use expand-contract so old and new code can run simultaneously.

Database Schema Design

Choose types deliberately

Primary keys

Indexes: the trade you are making

Composite index column order matters

Normalisation, and when to stop

Soft deletes and their trap

Migrations

Check your understanding

Your orders table has an index on (user_id, created_at). Which query can use it efficiently?

A `users` table uses a soft delete flag. What is the trap most teams hit?

Why should money be stored as an integer number of minor units, or a decimal type, rather than a floating-point number?

Common mistakes

Practice

Key takeaways

Further reading

Check yourself

Practice this

Discuss

Your orders table has an index on (user_id, created_at). Which query can use it efficiently?

A `users` table uses a soft delete flag. What is the trap most teams hit?

Why should money be stored as an integer number of minor units, or a decimal type, rather than a floating-point number?

Discuss

On this page

Practice this