Essential Database Design Patterns for Web Applications

A well-designed schema is a quiet superpower. It makes feature work feel light, scaling feel calm, and the on-call rotation feel like a chore instead of an emergency. Here are the patterns that keep aging well — through traffic spikes, changing requirements, and the developer turnover every project eventually faces.

Normalization

Start normalised. Resist the urge to “make it fast” before you’ve felt where it’s actually slow.

Third Normal Form (3NF) catches the obvious shape problems:

-- Denormalised — duplication waiting to drift
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT,
  customer_email TEXT,    -- duplicated from customers
  customer_name TEXT,     -- duplicated from customers
  total_cents BIGINT
);

-- 3NF — single source of truth per fact
CREATE TABLE customers (
  id     BIGSERIAL PRIMARY KEY,
  email  CITEXT UNIQUE NOT NULL,
  name   TEXT  NOT NULL
);

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT REFERENCES customers(id),
  total_cents  BIGINT NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

Denormalise later, on purpose, with a comment that explains why.

Indexing Strategy

An index is a contract: faster reads, slower writes, more disk. Spend them like budget — not like decoration.

• Index every foreign key.
• Index columns used in WHERE, ORDER BY, and JOIN.
• Use composite indexes that match the leading columns of your query.
• Drop unused indexes — pg_stat_user_indexes will tell you which.

-- Composite index supports both queries below
CREATE INDEX orders_customer_created_idx
  ON orders (customer_id, created_at DESC);

-- Uses the index
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

-- Also uses the index (leading column only)
SELECT count(*) FROM orders WHERE customer_id = 42;

Relationships

Model the real-world relationship, not the screen.

Relationship	Pattern	Notes
One-to-one	Foreign key + UNIQUE	Or merge into a single table.
One-to-many	Foreign key on the “many” side	The default and almost always correct.
Many-to-many	Join table with composite primary key	Add timestamps; you’ll want them later.
Polymorphic	Subject-type + subject-id	Lose referential integrity. Avoid.

CREATE TABLE post_tags (
  post_id BIGINT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Soft Deletes

Most products eventually need them. Add a deleted_at TIMESTAMPTZ column rather than removing rows. Pair every “active” query with a partial index:

ALTER TABLE users
  ADD COLUMN deleted_at TIMESTAMPTZ;

CREATE INDEX users_active_idx
  ON users (id)
  WHERE deleted_at IS NULL;

Wrap the filter in a view or a query helper — never trust callers to remember.

Transactions

Use them. The failure modes you can’t reproduce locally are almost always missing transactions.

await db.transaction(async (tx) => {
  await tx.execute(
    "UPDATE accounts SET balance_cents = balance_cents - $1 WHERE id = $2",
    [amount, fromId],
  );
  await tx.execute(
    "UPDATE accounts SET balance_cents = balance_cents + $1 WHERE id = $2",
    [amount, toId],
  );
  await tx.execute(
    "INSERT INTO transfers (from_id, to_id, amount_cents) VALUES ($1, $2, $3)",
    [fromId, toId, amount],
  );
});

Pick the right isolation level: READ COMMITTED is the right default; reach for SERIALIZABLE only when you can articulate the anomaly you’re preventing.

Migrations

Migrations are append-only. Never edit a merged migration; write a new one.

The pattern that survives team growth:

1. Add the new column / table — nullable, with a default.
2. Backfill in batches with throttling.
3. Switch the application to write the new shape.
4. Make the column NOT NULL once 100% of writes use it.
5. Drop the old shape in a follow-up release.

Big-bang migrations in production are how Friday afternoons become Saturday mornings.

Performance Tuning

EXPLAIN ANALYZE is the shortest path to honest answers. Run the query, read the plan, and target the largest cost first.

EXPLAIN ANALYZE
SELECT u.id, u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.customer_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.id, u.email
ORDER BY orders DESC
LIMIT 50;

Common wins, in order of frequency:

1. Missing index on a join column.
2. N+1 queries from the application layer.
3. Wide rows when only a few columns are needed (use a covering index).
4. Sequential scans on large tables — usually a missing predicate.

Closing Thoughts

A schema is a contract that gets harder to change every month. Spend a little extra time on names, types, and constraints when you’re alone — so you don’t have to spend a lot of time on them in front of the team a year later.