Database schema design best practices, measured across 100 schemas and 1,232 tables

Most schema design advice is a list of principles with no numbers attached. This post has the numbers. We wrote and reviewed the 100 schemas in the drawDB schema gallery against one set of rules, and then audited all of them with a script: 1,232 tables, 11,064 columns and 2,061 foreign keys across PostgreSQL, MySQL, SQLite, SQL Server and Oracle.

The short version, in the order these decisions get expensive to change:

That last row is the useful part of an audit like this. Two of our own rules didn't hold, and the sections below show both failures and the fix for each.

A caveat before the numbers: these are reference schemas written by one team, so they measure how well a style guide survives 100 different domains. They don't tell you what the industry does. Every schema is public domain, so you can open any of them in the editor and check our counts yourself.

What primary key should you use?

A bigint identity column called id. Of the 1,095 tables with a single-column primary key, 1,092 use an integer: bigint in PostgreSQL, MySQL and SQL Server, INTEGER in SQLite (where it aliases the rowid) and NUMBER(19) in Oracle. The other three are deliberate natural keys: two invite codes, where the code itself is what users share, and a key-value settings table keyed by setting name.

This is what drawDB's PostgreSQL exporter generates for one of them:

CREATE TABLE IF NOT EXISTS "assignments" (
	"id" BIGINT GENERATED BY DEFAULT AS IDENTITY,
	"experiment_id" BIGINT NOT NULL,
	"variant_id" BIGINT NOT NULL,
	"subject_key" VARCHAR(120) NOT NULL,
	"bucket" INTEGER NOT NULL,
	"assigned_at" TIMESTAMP NOT NULL DEFAULT now(),
	PRIMARY KEY("id")
);

bigint rather than integer, because a 32-bit key runs out at 2.1 billion rows and widening a primary key means rewriting every table that references it. Identity rather than serial, because an identity column is standard SQL and its sequence belongs to the column.

We used UUIDs almost nowhere: five columns in the whole gallery. Reach for one when IDs are generated outside the database, merged across shards, or shown publicly where a sequential number would reveal how many customers you have. If you do, use UUIDv7. It's time-ordered, so new rows land at the end of the index rather than at random pages in it, and PostgreSQL 18 generates it natively.

Junction tables are the exception to the id rule. The 137 tables with composite keys are almost all many-to-many links like listing_amenities (listing_id, amenity_id), where the pair already identifies the row. Adding a surrogate id would give you a second unique key to keep in sync and nothing else.

How should you name tables and columns?

Pick one convention and let a script check it. Our numbers:

• snake_case everywhere: all 1,232 tables. Unquoted identifiers fold to lower case in PostgreSQL, so CamelCase names force quoting in every query.
• Plural table names: 1,192 tables (96.8%). The 40 exceptions are history and log tables (password_history, flag_audit_log), daily rollups (click_daily) and nouns where a plural reads wrong (staff, media).
• Foreign keys ending in _id: 2,051 of 2,061 (99.5%). When a table references the same parent twice, the name carries the role, like winner_variant_id or parent_comment_id. The ten exceptions are actor columns such as approved_by and created_by, which read better as a verb.
• id as the primary key name: 1,062 tables (86%). The rest are the composite and natural keys above.

Singular versus plural matters much less than consistency. A schema that mixes user and orders costs every engineer a lookup on every query they write.

Should every table have created_at and updated_at?

No, and this is where our gallery departs furthest from common advice. Only 414 tables (33.6%) have created_at, and only 57 (4.6%) have updated_at. 327 tables (26.5%) have no timestamp column at all.

The reason is that a generic timestamp usually hides a more useful one. The most common timestamp names after created_at in the gallery are started_at, added_at, occurred_at, joined_at, expires_at and submitted_at. An order has placed_at, paid_at and shipped_at, and each one answers a question that a single updated_at can't: when what changed?

Our rules:

1. Name timestamps after the event they record.
2. Skip updated_at on append-only tables (event logs, ledger entries, history rows), since their rows never change.
3. Add updated_at where rows really are edited in place and you need cache invalidation or sync. Even then, keep a history table if you need to know what the previous value was.
4. Lookup and junction tables usually need no timestamp at all.

timestamp or timestamptz in PostgreSQL?

timestamptz, and our audit caught us being inconsistent here. Of the 73 PostgreSQL schemas, 41 use timestamptz throughout, 31 use plain timestamp throughout and one mixes both. The assignments table above is one of the 31.

Plain timestamp stores a wall-clock reading with no zone attached, so the same value means different instants depending on which server or session wrote it. timestamptz stores an absolute instant and converts on display. The PostgreSQL wiki's "Don't Do This" page puts it bluntly: don't use timestamp to store timestamps. The fix, if your existing values were written in UTC:

ALTER TABLE "assignments"
  ALTER COLUMN "assigned_at" TYPE timestamptz
  USING "assigned_at" AT TIME ZONE 'UTC';

This rewrites the table, so run it in a maintenance window on anything large.

How should you store money in a database?

As an integer count of the currency's minor unit, next to a currency code. Every currency amount in the gallery follows this rule: an integer column such as amount_cents, next to an ISO 4217 code in char(3) (188 of the 192 currency columns), and never float, real or PostgreSQL's money type.

CREATE TABLE "invoice_lines" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "unit_price_cents" BIGINT NOT NULL CHECK ("unit_price_cents" >= 0),
  "quantity" NUMERIC(12,2) NOT NULL DEFAULT 1,
  "currency" CHAR(3) NOT NULL
);

Integers make sums exact and identical in every language your services are written in. Floating point can't represent 0.10 exactly, and that rounding error eventually shows up in a reconciliation. Two refinements from the gallery:

• Not every number near money is money. Exchange rates are decimal(18,8), because a rate is a conversion factor rather than an amount. Fractional quantities, like a consultant billing 1.5 days, are decimal(12,2).
• Crypto needs wider integers. The crypto exchange schema uses numeric(78,0), which is still a whole number of base units, because a 256-bit token balance doesn't fit in bigint.

Fintech

Freelance invoicing and billing database schema

How a freelance and small-business invoicing app models its data in MySQL: clients and projects, tracked time, invoices with per-line tax, partial payments, credit notes, recurring templates, reminder schedules and multi-currency with the rate snapshotted at issue.

Should status columns use ENUM types?

We don't. All 360 columns named status, state, kind, type or role are varchar (or text), and 357 of them list their allowed values in the column note. Native ENUMs are awkward to change: in PostgreSQL you can add or rename a value but not remove one without rebuilding the type, and in MySQL changing an ENUM's value list is an ALTER TABLE. A status set always changes eventually.

What a plain varchar loses is enforcement, so add a CHECK constraint in production:

CREATE TABLE "experiments" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "status" VARCHAR(20) NOT NULL DEFAULT 'draft'
    CHECK ("status" IN ('draft', 'running', 'paused', 'stopped', 'completed'))
);

Changing the list later means dropping and re-adding one constraint, with no type rebuild.

Which ON DELETE rule should a foreign key use?

Decide per relationship based on who owns the child row. Across 2,061 foreign keys:

• CASCADE, 1,066 (51.7%): the child is part of its parent. Order lines go when the order goes; variants go with their experiment.
• SET NULL, 583 (28.3%): the link is optional context. A deleted user's comments stay, with author_id set to null.
• RESTRICT, 405 (19.7%): the child is a record that must outlive the referenced row: payments, ledger entries, audit history. Deleting a customer who has invoices should fail loudly, not quietly delete the invoices.
• NO ACTION, 7.

If you can't say which of the three a relationship is, you probably haven't finished modeling it.

Do foreign keys need indexes?

Yes, and this is the rule we broke most. 773 of 2,061 foreign key columns (37.5%) are not the leading column of any index. 557 of those are in PostgreSQL schemas, and 137 of those also have ON DELETE CASCADE.

PostgreSQL does not create an index on the referencing side of a foreign key. Neither do SQLite or SQL Server. MySQL's InnoDB creates one automatically. Without an index, deleting or re-keying a parent row makes the database find its children with a sequential scan of the child table. With CASCADE, that scan runs once for every parent row you delete, and it holds locks the whole time.

Our A/B testing schema has a typical case. assignments.variant_id cascades from variants, and the only index that mentions it is (experiment_id, variant_id). That index sorts by experiment first, so it can't serve a lookup by variant alone. (PostgreSQL 18 can sometimes skip-scan a leading column, but don't design around that.) The fix is one line:

CREATE INDEX "idx_assignments_variant" ON "assignments" ("variant_id");

To find every foreign key column in a PostgreSQL database that no index leads with:

SELECT c.conrelid::regclass AS child_table,
       a.attname AS fk_column,
       c.confrelid::regclass AS parent_table
FROM pg_constraint c
JOIN pg_attribute a
  ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]
  )
ORDER BY 1, 2;

This checks the first column of each key, which covers single-column foreign keys. Cybertec's version handles multi-column keys too. A small lookup table you never delete from, like a currency list, is the one case where skipping the index is fine.

SaaS

A/B testing & experimentation platform schema

An experimentation platform end to end: experiments and weighted variants, deterministic hash bucketing, exposure events, metric definitions, per-variant aggregates with sufficient statistics, significance results and staged rollout.

When should you use JSON columns?

Rarely and deliberately. 102 columns in 95 tables (7.7%) are json or jsonb, and each holds data whose shape varies by row and that you never join on: analytics event properties, user-defined custom fields, snapshotted shipping addresses, automation rules, an experiment variant's client payload. If you find yourself writing WHERE payload->>'customer_id' = …, that field should be a column.

How common are soft deletes?

Less common than the advice suggests: 32 tables (2.6%), in 24 of the 100 schemas, have deleted_at, archived_at or is_deleted. They appear where users expect an undo, a trash folder or a "this message was deleted" placeholder: messages, posts, files, notes and cards. Elsewhere, a hard delete plus an audit table is simpler.

If you do soft-delete, make uniqueness ignore deleted rows, or a deleted account will block anyone from signing up again with that email:

CREATE TABLE "accounts" (
  "id" BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "email" VARCHAR(320) NOT NULL,
  "deleted_at" TIMESTAMPTZ
);

CREATE UNIQUE INDEX "uq_accounts_email_live"
  ON "accounts" ("email") WHERE "deleted_at" IS NULL;

Fintech

Stripe-style payments platform database schema

How a payment processor models its data: merchant accounts, tokenised payment methods, the payment intent state machine, charges and partial refunds, chargebacks with evidence deadlines, payouts, webhook deliveries and idempotency keys.

Check your own schema

Every rule above can be checked mechanically, which is the real lesson of this audit. We wrote these schemas carefully and still left 773 foreign keys unindexed and 31 schemas on the wrong timestamp type. Reviewers miss the same things that authors miss.

To run the same checks on your own design, open any gallery schema or import your existing DDL into drawDB. Change the design visually, then export SQL for your database. Every schema referenced here is public domain, so copy whatever helps.

Frequently asked questions