Problem Solving and Databases

1. 1
   Data

   Entities and their facts, from the requirements in plain English; what must persist.

   fails by Nouns copied from a tutorial's schema.

2. 2
   Access Patterns

   Per workflow: reads, writes, what happens together, how often, filtered by what.

   fails by Skipped; the model is drawn from the nouns.

3. 3
   Model

   Entities shaped for the patterns: snapshot or reference, optional or required, one table or two.

   fails by Normalisation applied as doctrine; every reference a foreign key.

4. 4
   Index

   One per frequent, filtered read; unique where the pattern demands it.

   fails by None until a page is slow; then one per slow page.

5. 5
   Query

   Written last, against a model that already serves it; plan read when it surprises.

   fails by Written first, and the model bent to fit.

The chain is short on a store and takes an afternoon. Its output is a schema whose every decision has a pattern as its reason, which is what makes the schema changeable when the patterns do.

Each unknown changes the model — a column, a nullability, a constraint. Resolving them before the first migration is the whole payoff of the chain.

1create table product (
2  id         bigint primary key,
3  name       text not null,
4  price      integer not null,          -- current price; history lives on order_item
5  stock      integer not null check (stock >= 0)  -- "never oversell", enforced here
6);
7create index product_name_lower on product (lower(name));   -- name search, every keystroke
8create table "order" (
9  id           bigint primary key,
10  customer_id  bigint null,             -- guest checkout: orders without a customer
11  email        text not null,           -- confirmation and lookup for guests
12  status       text not null,
13  created_at   timestamptz not null
14);
15 
16create table order_item (
17  order_id    bigint references "order",
18  product_id  bigint references product,
19  quantity    integer not null,
20  unit_price  integer not null          -- snapshot: order history must not change with price
21);
22create table payment (
23  id            bigint primary key,
24  order_id      bigint references "order",
25  provider_ref  text not null unique,   -- callback lookup; unique so a repeat cannot match twice
26  status        text not null,
27  paid_at       timestamptz null        -- revenue by payment date, per the founder
28);
29create index payment_paid_at on payment (paid_at);

Notice which lines came from patterns rather than from the entity list: the check constraint, the lowercased name index, the nullable customer, the price snapshot, the unique provider reference, the paid-at index. None is visible from the nouns alone.