Database design

01Entities and relations

Start from the business, not from tables. List the nouns (customer, product, order, payment) and the verbs that connect them (a customer places orders, an order contains items). Each important noun becomes a table, an entity. Then decide the cardinality of each relation. One-to-many is the most common: one customer has many orders, so orders gets a customer_id foreign key. Many-to-many, like products and categories, needs a join table (product_categories) with both foreign keys and a composite primary key. One-to-one is rare and usually means optional extra data, such as a customer profile. Foreign keys make the database refuse orphan rows; choose ON DELETE behaviour deliberately: RESTRICT for business records, CASCADE only for true children like order items of a draft cart.

• Nouns become tables, verbs become relations.
• 1:N: foreign key on the 'many' side.
• N:M: a join table with a composite key.
• Name consistently: plural tables, singular_id foreign keys, snake_case.

02Normalisation vs denormalisation

Normalisation means storing each fact once. In practice, third normal form (3NF) is the target: every column depends on the key, the whole key and nothing but the key. If a customer's email is copied into every order, changing it means updating many rows, and some will be missed. Denormalisation is copying data on purpose, for speed or history. Two cases are not only allowed but required. Snapshots: an order item must store the product name and unit price at the time of purchase, because the product will change later. Read models: a cached order_count on customers or a search index. Normalise by default, denormalise deliberately, and document who keeps the copy in sync, whether a trigger, a job or the application.

03Keys: natural vs surrogate, UUID vs bigint

A natural key is a real-world value, like an email or SKU; a surrogate key is a meaningless generated id. Natural values change (people change emails, SKUs get renamed), so use a surrogate primary key and protect the natural value with a UNIQUE constraint. For the surrogate, bigint auto-increment is compact (8 bytes), fast and keeps B-tree inserts in order, but ids are guessable and must be generated by the database. UUIDs (16 bytes) can be generated anywhere, which suits distributed systems, offline clients and public URLs. Random UUIDv4 scatters inserts across the index and hurts write performance at scale; time-ordered UUIDv7 fixes that. PostgreSQL 18 has a built-in uuidv7() function; elsewhere, generate v7 in the application.

• bigint: internal tables, joins, high write volume.
• UUIDv7: public ids, offline or multi-system creation.
• Never expose sequential ids where enumeration is a risk.
• Keep natural keys as UNIQUE columns, not primary keys.

04Index strategy

An index is a sorted copy of some columns that lets the database find rows without scanning the whole table, like the index at the back of a book. Every primary key and unique constraint has one. Add others for the queries you really run: foreign keys used in joins, columns in frequent WHERE clauses and ORDER BY. In a composite index, column order matters. The leftmost-prefix rule says an index on (customer_id, created_at) helps queries filtering by customer_id, or by customer_id and a date range, but not by created_at alone. Put equality columns first and range or sort columns last. Indexes are not free: each one slows writes and uses memory, so remove unused ones. Always confirm with EXPLAIN.

1-- "latest orders of a customer" in one index scan
2CREATE INDEX orders_customer_created_idx
3  ON orders (customer_id, created_at DESC);
4 
5EXPLAIN
6SELECT id, total_minor, status
7FROM orders
8WHERE customer_id = 42
9ORDER BY created_at DESC
10LIMIT 20;

05Money and time

Never store money in a floating-point column: 0.1 + 0.2 is not 0.3 in binary floats, and rounding errors add up across thousands of orders. Use integer minor units (cents) in a bigint, or DECIMAL/NUMERIC with a fixed scale, and always store the currency code (ISO 4217, like EUR or USD) next to the amount. Store prices, discounts, tax and totals on the order itself so history never changes. For time, store instants in UTC. PostgreSQL's timestamptz stores UTC and converts on display; in MySQL, DATETIME has no time zone, so store UTC by convention, and avoid TIMESTAMP for far-future dates because it ends in 2038. For future local events, such as a booking at 10:00 in a city, store the local time plus an IANA time zone name like Asia/Yerevan, because time zone rules can change.

• Amounts: bigint minor units or NUMERIC(19,4), never float.
• Always store currency next to amount.
• Instants in UTC; future local events as local time + IANA zone.

06Soft deletes and safe migrations

A soft delete marks a row as deleted (deleted_at timestamp) instead of removing it, so you can restore it and keep history. The cost: every query must filter it out, and unique constraints must ignore deleted rows. In PostgreSQL a partial unique index solves that; in MySQL, use a generated column. Use soft deletes for user-facing content, not for everything; for audit needs, an append-only history table is often cleaner, and privacy laws may require real deletion. Migrations change a live schema, so make them backwards-compatible with the running code using expand and contract: add the new nullable column, deploy code that writes both, backfill in batches, switch reads, then drop the old column in a later release. Build big indexes without locking writes (CREATE INDEX CONCURRENTLY in PostgreSQL).

1-- PostgreSQL: email unique only among non-deleted customers
2CREATE UNIQUE INDEX customers_email_live_uq
3  ON customers (lower(email))
4  WHERE deleted_at IS NULL;
5 
6-- expand step: safe on a live table
7ALTER TABLE orders ADD COLUMN currency char(3);
8-- backfill in batches, then in a later release:
9-- ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;

07Example: an e-commerce core schema

Here is a compact core that shows the ideas together. Products hold shared information; variants hold what is actually sold (size, colour, SKU, price). Orders belong to a customer, store their currency and totals, and have a status with a CHECK constraint. Order items reference the variant but also snapshot the name and unit price, so an order printed in five years still shows what the customer paid. Payments are a separate table because one order can have several attempts, refunds or partial captures. Around this core you would add addresses (snapshotted onto the order too), inventory movements, promotions applied, and a status history table for auditing.

1CREATE TABLE products (id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name text NOT NULL);
2CREATE TABLE variants (
3  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
4  product_id bigint NOT NULL REFERENCES products(id),
5  sku text NOT NULL UNIQUE,
6  price_minor bigint NOT NULL CHECK (price_minor >= 0));
7CREATE TABLE orders (
8  id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
9  customer_id bigint NOT NULL REFERENCES customers(id),
10  status text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled','refunded')),
11  currency char(3) NOT NULL,
12  total_minor bigint NOT NULL,
13  created_at timestamptz NOT NULL DEFAULT now());
14CREATE TABLE order_items (
15  order_id bigint NOT NULL REFERENCES orders(id),
16  variant_id bigint NOT NULL REFERENCES variants(id),
17  name_snapshot text NOT NULL,
18  unit_price_minor bigint NOT NULL,
19  qty int NOT NULL CHECK (qty > 0),
20  PRIMARY KEY (order_id, variant_id));

08Modelling trees: adjacency list, materialised path and closure table

Categories, comment threads, org charts and folder structures are trees, and SQL has several ways to store them. An adjacency list gives each row a parent_id: simple, cheap to move a node, and with recursive CTEs (WITH RECURSIVE, supported in PostgreSQL and MySQL 8) reading a subtree is fine for moderate depths. A materialised path stores the full path as a string, such as /1/7/42/; finding all descendants is a fast prefix search, but moving a branch rewrites every path below it. PostgreSQL's ltree extension makes this pattern first-class. A closure table stores every ancestor-descendant pair with its depth in a separate table; reads of any subtree or ancestor chain become one indexed join, at the cost of more rows and more work on insert and move. Nested sets are fast to read but painful to update and rarely worth it today. Choose by your dominant operation: frequent moves favour adjacency lists, heavy subtree reads favour paths or closure tables.

1CREATE TABLE category_paths (
2  ancestor_id   bigint NOT NULL REFERENCES categories(id),
3  descendant_id bigint NOT NULL REFERENCES categories(id),
4  depth         int    NOT NULL,
5  PRIMARY KEY (ancestor_id, descendant_id)
6);
7 
8-- all products in category 7 and every subcategory, one join
9SELECT p.*
10FROM category_paths cp
11JOIN products p ON p.category_id = cp.descendant_id
12WHERE cp.ancestor_id = 7;

09Integrity under concurrency: constraints, upserts and optimistic locking

Application checks such as 'if the email does not exist, insert it' are race conditions: two requests can both pass the check and both insert. Only the database can enforce rules atomically, so put them there. A UNIQUE constraint plus an upsert (INSERT ... ON CONFLICT in PostgreSQL, ON DUPLICATE KEY UPDATE in MySQL) turns duplicates into a defined outcome. For external retries, such as a payment webhook or a double-clicked checkout, store an idempotency key with a unique constraint and return the stored result on a repeat. PostgreSQL's exclusion constraints can forbid overlapping ranges, which is the correct way to prevent double bookings. For records edited by humans, optimistic locking avoids holding locks while someone types: keep a version column, update WHERE version = the value you read, and treat zero affected rows as a conflict to show the user. Pessimistic locks (SELECT ... FOR UPDATE) fit short, hot paths like stock decrements.

1-- optimistic lock: succeeds only if nobody changed the row since we read version 7
2UPDATE products
3SET price_cents = 1990, version = version + 1
4WHERE id = 42 AND version = 7;
5-- 0 rows affected => someone else saved first; reload and ask the user
6 
7-- PostgreSQL: no two bookings of a room may overlap
8CREATE EXTENSION IF NOT EXISTS btree_gist;
9ALTER TABLE bookings ADD CONSTRAINT no_overlap
10  EXCLUDE USING gist (room_id WITH =, during WITH &&);

10Growth: partitioning, retention and archiving

Most tables never need partitioning. Append-heavy tables that grow to hundreds of millions of rows, such as events, logs, audit trails or old orders, eventually do. Partitioning splits one logical table into physical pieces by a key, usually a time range per month. Queries that filter on that key only touch matching partitions (partition pruning), indexes stay small enough to fit in memory, and retention becomes instant: detaching or dropping an old partition replaces a DELETE of millions of rows that would bloat the table and flood replication. The rules to know: the primary key and every unique constraint must include the partition key, in both PostgreSQL and MySQL; queries that do not filter by the key scan all partitions; and too many partitions slow planning, so hundreds are fine, tens of thousands are not. Create future partitions ahead of time with a scheduled job or an extension such as pg_partman. Archive detached partitions to cheaper storage if you must keep them.

1CREATE TABLE events (
2  id         bigint GENERATED ALWAYS AS IDENTITY,
3  created_at timestamptz NOT NULL,
4  type       text NOT NULL,
5  payload    jsonb,
6  PRIMARY KEY (id, created_at)
7) PARTITION BY RANGE (created_at);
8 
9CREATE TABLE events_2026_11 PARTITION OF events
10  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
11 
12-- retention: instant, no row-by-row delete
13ALTER TABLE events DETACH PARTITION events_2025_11;
14DROP TABLE events_2025_11;

11Patterns that fight the database: polymorphic links, EAV and cross-tenant references

Some popular patterns quietly give up integrity. Polymorphic associations, such as comments with commentable_type and commentable_id, cannot have a foreign key, so orphans and typos go unnoticed. Alternatives: one nullable foreign key per parent type with a CHECK that exactly one is set, or a separate join table per parent. Entity-attribute-value tables (entity_id, attribute, value) look flexible but turn every read into many joins and store every value as text; a JSONB column for the variable part, with real columns for what you filter on, is almost always better. In multi-tenant schemas, a foreign key on customer_id alone does not stop an order from pointing at another tenant's customer. Make the tenant part of the key: a unique constraint on (tenant_id, id) in the parent and a composite foreign key (tenant_id, customer_id) in the child. Then cross-tenant references are impossible by construction, not by code review.

1ALTER TABLE customers ADD CONSTRAINT uq_customers_tenant_id UNIQUE (tenant_id, id);
2 
3ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_same_tenant
4  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id);
5 
6-- exclusive arc instead of a polymorphic pair
7ALTER TABLE comments ADD CONSTRAINT one_parent CHECK (
8  num_nonnulls(post_id, product_id) = 1
9);