Modelling for workloads

Why it matters

Application code changes every week. The database usually outlives several versions of it, plus scripts, imports and the occasional engineer typing into a console. Constraints and good types are the only rules that every writer has to obey. A DBA who models carefully gets fewer "how did this row get here?" incidents and faster queries.

Start from the workload

Before picking types, ask how the data will be used:

• OLTP (online transaction processing): many small reads and writes, such as placing an order or updating a profile. Favour normalised tables, narrow rows, strict constraints and indexes on the lookup paths.
• OLAP (analytics): fewer, large read queries that scan and aggregate millions of rows. Favour wide, denormalised fact and dimension tables, often in a separate warehouse, loaded in batches.
• Write-heavy logs and events: append-only tables, often partitioned by time so old data can be dropped cheaply.

The same "orders" data can be modelled three ways depending on which of these you serve. Write down the top five queries and the expected row counts before you design.

Keys

• Primary key: every table needs one. It must be unique, not null and stable.
• Surrogate keys (id INTEGER / BIGINT identity, or UUID) never change meaning. Natural keys (an ISO country code, a SKU) are fine when they are truly unique and stable. Emails are a poor primary key because people change them; keep them as a UNIQUE column instead.
• Use BIGINT for high-volume tables in PostgreSQL or MySQL: a 32-bit integer runs out at about 2.1 billion, and changing the type of a primary key on a large live table is painful.
• Foreign keys make relationships real. Decide what happens on delete: ON DELETE RESTRICT (the safe default), CASCADE (child rows go too) or SET NULL.

Types

Pick the narrowest type that is always correct:

SQLite is different. It uses type affinity: a column declared INTEGER will happily store 'abc' unless the table is declared STRICT (SQLite 3.37+). Foreign keys are only enforced after PRAGMA foreign_keys = ON;. That is fine for a teaching sandbox, but it is exactly why server databases are stricter by default.

Constraints

Constraints are rules the database checks on every write:

CREATE TABLE members (
  member_id          INTEGER PRIMARY KEY,
  email              TEXT NOT NULL UNIQUE,
  full_name          TEXT NOT NULL,
  plan               TEXT NOT NULL CHECK (plan IN ('basic', 'pro')),
  monthly_fee_cents  INTEGER NOT NULL CHECK (monthly_fee_cents >= 0),
  joined_on          TEXT NOT NULL
);

• NOT NULL says the value is required. Make it the default habit and allow nulls only when "unknown" is a real state.
• UNIQUE stops duplicates (and creates an index).
• CHECK encodes simple business rules: allowed values, ranges, end_date >= start_date.
• DEFAULT gives a sensible value when none is supplied, such as DEFAULT CURRENT_TIMESTAMP.

Worked example: catching bad rows

With the table above, these inserts behave very differently:

INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('ana@example.com', 'Ana Silva', 'pro', 1500, '2026-01-10');   -- ok
INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('ana@example.com', 'Ana S.', 'basic', 500, '2026-02-01');     -- fails: UNIQUE
INSERT INTO members (email, full_name, plan, monthly_fee_cents, joined_on)
VALUES ('eve@example.com', 'Eve Ruiz', 'gold', 900, '2026-02-03');    -- fails: CHECK

In SQLite, INSERT OR IGNORE skips rows that break NOT NULL, UNIQUE, CHECK or primary key constraints instead of stopping, which makes it easy to see which rows your design rejects. (PostgreSQL has INSERT ... ON CONFLICT DO NOTHING for unique conflicts only.)

Common mistakes

• Floats for money. 0.1 + 0.2 is not exactly 0.3 in binary floating point.
• Everything nullable. Nulls leak into reports as blank totals and surprise NOT IN results.
• Rules only in the app. The next import script will not know them.
• Wide "god" tables with dozens of rarely used columns; split optional groups into their own table.
• No plan for growth. Estimate rows per year; it changes key types and partitioning choices.

Quick recap

• Design for the workload: OLTP, analytics or append-only logs.
• Stable primary keys, BIGINT for big tables, foreign keys with a deliberate delete rule.
• Narrow, correct types: exact numerics for money, time zones for timestamps.
• NOT NULL, UNIQUE and CHECK make the database enforce the rules.

Try it

In the Do tab, write a CREATE TABLE whose constraints reject four bad rows from a given batch of inserts.