Database7 min read

How to Design a Database Schema That Holds Up

Normalisation, keys, indexes and migrations: a repeatable process for designing a schema you can still change two years from now.

UL

Ullass Engineering Team

Editorial Team — Software Development & Digital Products

Start From the Questions, Not the Tables

The common way to design a schema is to sketch entities — users, orders, products — and start writing CREATE TABLE. That works until the first awkward question arrives, and then you retrofit the answer into the wrong place. The better sequence is to write down the five or six questions the application must answer, in plain language, with the data each one needs. The tables fall out of that list, and every column you add should be traceable to a question.

For an order-management system, the questions are typically: show a customer their orders newest first; show one order with its line items; find orders awaiting fulfilment for a warehouse; calculate a customer's lifetime value; and produce a monthly revenue series. Every column in the schema below exists because one of those needs it.

This ordering also tells you what to index, because the index is an answer to a question rather than a decoration. See PostgreSQL's index chapter for the mechanism, but the discipline is deciding which questions are frequent first.

One convention decision before any DDL: snake_case for everything, singular table names, and the same type for the same concept everywhere. country_code as char(2) in one table and country as text in another will produce a bug the type system cannot catch and the compiler will not warn about.

A Worked Schema: Users, Orders and Line Items

sql
create extension if not exists pgcrypto; create type order_status as enum ( 'draft', 'pending', 'paid', 'shipped', 'cancelled', 'refunded' ); create table users ( id uuid primary key default gen_random_uuid(), email text not null, email_verified_at timestamptz, full_name text not null check (char_length(full_name) between 1 and 200), country_code char(2) not null, default_currency char(3) not null default 'GBP', created_at timestamptz not null default now(), updated_at timestamptz not null default now(), deleted_at timestamptz ); -- Case-insensitive uniqueness without relying on an extension create unique index users_email_lower_uniq on users (lower(email)); create table orders ( id uuid primary key default gen_random_uuid(), order_number text not null unique, user_id uuid not null references users (id) on delete restrict, status order_status not null default 'draft', currency char(3) not null, subtotal_cents bigint not null default 0 check (subtotal_cents >= 0), tax_cents bigint not null default 0 check (tax_cents >= 0), shipping_cents bigint not null default 0 check (shipping_cents >= 0), total_cents bigint not null default 0 check (total_cents >= 0), idempotency_key text, placed_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(), -- The database refuses to store a total that does not add up constraint orders_total_is_sum check ( total_cents = subtotal_cents + tax_cents + shipping_cents ) ); create unique index orders_user_idempotency_uniq on orders (user_id, idempotency_key) where idempotency_key is not null; create table line_items ( id uuid primary key default gen_random_uuid(), order_id uuid not null references orders (id) on delete cascade, product_id uuid not null references products (id) on delete restrict, description text not null, quantity integer not null check (quantity between 1 and 999), unit_price_cents bigint not null check (unit_price_cents >= 0), tax_rate numeric(5, 4) not null default 0.2000 check (tax_rate between 0 and 1) );

Three decisions in that DDL are worth defending.

Money is bigint minor units, not float and not money. double precision cannot represent 19.99, and a ledger built on it will not reconcile at month end. Postgres's numeric is exact and is the better choice when you need arbitrary precision — the numeric type documentation explains the storage cost — but for currencies, integer cents plus an ISO 4217 column is what payment providers expect.

description and unit_price_cents are snapshotted on the line item. They duplicate product data, which looks like a normalisation violation and is not. Change the price of a product tomorrow and every historical invoice must still show what was actually charged. This is deliberate denormalisation with a documented reason.

The total check constraint is the point of having a database. Application code will eventually have a bug in a nightly import. A check constraint turns that into a failed insert instead of a silently wrong ledger. Postgres's constraint documentation covers check, unique, not null and foreign keys in one place, and the ALTER TABLE reference is where the timing semantics for adding them live — including NOT VALID, which matters a great deal below.

Choosing keys: uuid versus bigint

bigserial gives you an 8-byte key, a fast sequence and perfect index locality. uuid gives you 16 bytes, no coordination between nodes, and — critically — an identifier an attacker cannot enumerate. For anything reachable over HTTP, that last property alone usually justifies the wider key.

The cost is index writes. A random uuid v4 as the primary key fragments the B-tree on every insert, because new keys land at random positions instead of the right edge. Version 7 fixes exactly that by embedding a millisecond timestamp in the high bits, giving sequential locality with uniqueness. If you use uuid, use v7.

Normalisation, With the Actual DDL

Normalisation is not a goal in itself. It is a tool for making one fact live in one place, so that changing it does not require hunting for copies.

First normal form is about atomicity. Storing three phone numbers in a text column called phones forces every consumer to split on a comma, and the separator becomes an accidental part of your data model:

sql
create table user_phone_numbers ( user_id uuid not null references users (id) on delete cascade, number text not null, verified_at timestamptz, primary key (user_id, number) );

Third normal form is about transitive dependency. If orders carried shipping_country, shipping_country_name and shipping_zone_id, then the country name depends on the zone, which depends on the country — a chain of dependencies entirely inside one table, requiring an update in three places when a country is renamed:

sql
create table shipping_zones ( id uuid primary key default gen_random_uuid(), country_code char(2) not null, zone_code text not null, name text not null, unique (country_code, zone_code) ); alter table orders add column shipping_zone_id uuid references shipping_zones (id); create index orders_shipping_zone_idx on orders (shipping_zone_id);

Second normal form — no partial dependencies on part of a composite key — matters less in practice because we rarely use composite keys outside join tables, where it is automatic.

Denormalising on purpose

Above third normal form, the trade is writes for read speed. A customer list showing order counts should not count(*) across 40 million rows per page render. Cache it explicitly, and know who owns the refresh:

sql
create table user_order_stats ( user_id uuid primary key references users (id) on delete cascade, order_count integer not null default 0, lifetime_cents bigint not null default 0, last_order_at timestamptz, refreshed_at timestamptz not null default now() ); create or replace function refresh_user_order_stats() returns trigger language plpgsql as $$ begin insert into user_order_stats (user_id, order_count, lifetime_cents, last_order_at) select u.id, count(o.id), coalesce(sum(o.total_cents) filter (where o.status = 'paid'), 0), max(o.placed_at) from users u left join orders o on o.user_id = u.id and o.status = 'paid' where u.id = coalesce(new.user_id, old.user_id) group by u.id on conflict (user_id) do update set order_count = excluded.order_count, lifetime_cents = excluded.lifetime_cents, last_order_at = excluded.last_order_at, refreshed_at = now(); return null; end; $$; create trigger orders_stats_refresh after insert or update of status, total_cents or delete on orders for each row execute function refresh_user_order_stats();

The design rule underneath it: every denormalised value needs a defined refresh path. A cached column with no trigger, no scheduled job and no manual backfill is just a bug that has not fired yet.

Indexing From Real Query Shapes

Work backwards from the questions. Each one becomes an index, and nothing else does:

QuestionIndex
A customer's orders, newest firstorders (user_id, created_at desc)
One order with its line itemsline_items (order_id)
Orders awaiting fulfilmentpartial index on open statuses
Sign-in by emailusers (lower(email)) unique
Warehouse queueorders (warehouse_id, placed_at)
sql
create index orders_user_created_idx on orders (user_id, created_at desc); create index line_items_order_idx on line_items (order_id); create index orders_open_idx on orders (placed_at) where status in ('pending', 'paid'); create index orders_user_covering_idx on orders (user_id, created_at desc) include (total_cents, status);

The last one is an INCLUDE-list index, which stores the extra columns in the leaf pages so the planner can answer from the index alone — an index-only scan that avoids millions of heap fetches for a list view.

One fact that catches everybody out: PostgreSQL does not create an index on a foreign key column automatically. InnoDB does. Move a MySQL schema to Postgres and every join on orders.user_id becomes a sequential scan of the referencing table unless you add the index yourself. The foreign key documentation is explicit that the index is your responsibility.

Order composite index columns equality-first, then range, then sort. Postgres can only scan an index in one direction, so (status, created_at desc) serving a WHERE status = 'paid' ORDER BY created_at DESC is close to optimal, and reversing the columns for that query is a change that costs minutes and saves a sort over the whole matching set.

Every index is a tax on writes: each INSERT, UPDATE and DELETE has to maintain it, and on a high-churn table a speculative index added for one report can measurably slow the write path for months before anyone removes it. Treat the set of indexes as something you prune, not something you accumulate. SELECT index_def, idx_scan FROM pg_stat_user_indexes ordered by idx_scan ascending tells you which ones have earned nothing.

Migrations You Can Roll Forward

A migration runs while the old application version is still serving traffic, so it must be compatible with both. The pattern is expand, migrate, contract — never rename in one step, because the moment the migration lands, half your fleet is reading email_verified and half is reading email_verified_at:

sql
-- 1. Expand: add the new column and let it default for every existing row alter table orders add column channel text not null default 'web'; -- 2. Migrate: deploy code that sets channel explicitly, backfill in batches update orders set channel = 'pos' where channel = 'web' and order_number like 'POS-%'; -- 3. Contract: only once no deployed code relies on the implicit default alter table orders alter column channel drop default;

For a pure rename, run it as two separate releases with the compatibility column in between:

sql
alter table users add column email_verified_at timestamptz; update users set email_verified_at = created_at where email_verified; -- release N+1: all code reads email_verified_at alter table users drop column email_verified;

Adding a column with a non-volatile default has not rewritten the table since Postgres 11, so the first line above is instant on a large table. Adding a foreign key is the one that still locks, and the safe version uses two steps — the NOT VALID path lets the constraint apply to new rows immediately while the existing rows are verified later:

sql
alter table orders add column warehouse_id uuid, add constraint orders_warehouse_fkey foreign key (warehouse_id) references warehouses (id) not valid; -- Backfill in batches so you never hold a long lock update orders set warehouse_id = '...' where warehouse_id is null and id in (select id from orders where warehouse_id is null limit 5000); alter table orders validate constraint orders_warehouse_fkey; alter table orders alter column warehouse_id set not null;

Run that backfill in a loop until it returns zero rows, from a job rather than a migration. And note CREATE INDEX CONCURRENTLY builds without blocking writes, but it cannot run inside a transaction block — so your migration tool must not wrap every file in one. Everything involving DDL should be reviewed for locking behaviour before it reaches production; Postgres's transaction documentation explains which locks each statement takes and why.

What We Get Wrong Most Often

  • Nullable by default. Make columns not null unless absence is genuinely meaningful. Every nullable column is a branch nobody wrote.
  • No timestamps. created_at and updated_at on every table. Retrofitting them on a 40M-row table is a project; adding them on day one is a line.
  • Timestamps as text. Sorting '2025-1-9' against '2025-10-1' produces nonsense. Use timestamptz and store UTC; the type handles offsets correctly on the way back out.
  • Over-eager ON DELETE CASCADE. Cascading deletes an order with its line items is obvious. Cascading from a user through every order to every refund is a data-loss event. Decide per relationship.
  • Auto-increment integers in URLs. They are enumerable, and they leak your volume.
  • Enums you cannot evolve. Postgres enums are easy to query and awkward to remove a value from. If values will change often, use a lookup table with a foreign key — the enum type documentation covers both the ADD VALUE restriction and the workaround.
  • Soft deletes applied inconsistently. deleted_at on some tables and a hard DELETE on others means every query has to know which convention its table follows, and the first forgotten WHERE resurrects a customer.

A Checklist Before You Ship

Every table has a primary key, created_at and updated_at. Every foreign key has an index. Every value the business cares about has a constraint or a check. Money is integer minor units with a currency column. Every index answers a question you can name. Every migration is backwards compatible with the code currently deployed. And every denormalised value has a documented refresh path.

If this is part of a larger build, our web development service covers how we scope schema work, SaaS development is where multi-tenant schemas get interesting, and PostgreSQL vs MySQL for web applications is worth reading first if the engine is still open. The Posnova build is a real example of a schema shaped this way in production — or send us your questions.

How Ullass can help

This guide covers the theory. If you would rather have it implemented and operated for you, these are the services that cover it.

Related Articles

Ullass — Software Development Company

We build web apps, SaaS platforms, and digital products

Ullass designs, engineers, and scales software products for ambitious businesses. Also try our free online tools at tools.ullass.com. Questions? hello@ullass.com