Modelling a real domain from start to finish

Rules are easier to read than to apply. So this lesson is one domain modelled from nothing to a working schema, with every decision stated and the wrong turns left in — because the wrong turns are where the learning is.

The brief. A tiffin service in Kothrud. They cook lunch and dinner, deliver on a subscription, and want to stop running the business from a WhatsApp group and a notebook.

Read it as a client would give it to you:

"We have about 200 customers. Most take lunch only, some take both. They pay monthly in advance. We have three plans — veg, non-veg and jain — and the price changed in April. People pause when they travel. We deliver on four routes and each delivery boy has a route. We need to know each morning how many of each plan to cook, and per route. Sometimes a customer complains a delivery never arrived and we need to check. At month end we work out who owes what."

Step 1: find the nouns

Underline them. Customer, plan, subscription, pause, route, delivery boy, delivery, payment, complaint.

Then ask of each: is this a thing, or a fact about a thing?

Customer — a thing.
Plan — a thing (veg, non-veg, jain).
Subscription — a thing. This customer, on this plan, from this date.
Pause — a thing, because it has its own dates.
Route — a thing.
Delivery boy — a person; call them staff, since the role may change.
Delivery — a thing. One meal, to one customer, on one day.
Payment — a thing.
Complaint — a thing, but deferrable. Note it and move on.

Deferring is a real skill. A first schema that models everything is a schema you will get wrong, because you do not yet know which parts matter.

Step 2: find the verbs, because they are the constraints

A customer subscribes to a plan → subscriptions
A subscription is paused for a period → pauses
A customer is on a route → is this a fact about the customer, or the subscription?
A delivery is made to a customer by staff on a date
A customer pays

That third one is the first real decision, and I will come back to it.

Step 3: a first attempt, with the mistakes in

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  phone text NOT NULL,
  address text NOT NULL,
  route text NOT NULL,              -- 1. free text
  plan text NOT NULL,               -- 2. free text, and wrong place
  price int NOT NULL,               -- 3. wrong place, and no unit
  paused boolean DEFAULT false,     -- 4. loses the dates
  meals text NOT NULL               -- 5. 'lunch' / 'both' — a repeating group
);

This is roughly what a first draft looks like, and it is worth reading each fault.

route as free text will contain "Kothrud", "kothrud", "Kothrud " and "Karve Nagar" for the same route. A route is a thing — it has a name and a delivery person — so it gets a table.

plan on the customer cannot express "switched from veg to non-veg in June", and the business needs the history for billing.

price on the customer. The price belongs to the plan. But it changed in April, so a single column on plans cannot hold both — see step 5.

paused boolean. The killer. The business asked "who owes what at month end", which needs how many days someone was paused, not whether they are paused now. A boolean throws away the information the business actually asked for. Watch for this: a boolean is almost always a date or a period that has been flattened.

meals text holding 'lunch' or 'both' is a repeating group in disguise. When they add breakfast you are editing code and data.

Step 4: the schema, with the reasoning

CREATE TABLE routes (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE staff (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  phone text NOT NULL,
  active boolean NOT NULL DEFAULT true
);

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  phone text NOT NULL UNIQUE,
  address text NOT NULL,
  route_id int NOT NULL REFERENCES routes(id),
  joined date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE plans (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text NOT NULL UNIQUE,                       -- 'veg', 'non-veg', 'jain'
  name text NOT NULL
);

-- price is a fact about a plan DURING A PERIOD, not about a plan
CREATE TABLE plan_prices (
  plan_id int NOT NULL REFERENCES plans(id),
  meal text NOT NULL CHECK (meal IN ('lunch', 'dinner')),
  price_paise int NOT NULL CHECK (price_paise > 0),
  valid_from date NOT NULL,
  PRIMARY KEY (plan_id, meal, valid_from)
);

CREATE TABLE subscriptions (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  plan_id int NOT NULL REFERENCES plans(id),
  started_on date NOT NULL,
  ended_on date,                                   -- NULL = still active
  CHECK (ended_on IS NULL OR ended_on >= started_on)
);

-- which meals this subscription takes: solves the repeating group
CREATE TABLE subscription_meals (
  subscription_id bigint NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  meal text NOT NULL CHECK (meal IN ('lunch', 'dinner')),
  PRIMARY KEY (subscription_id, meal)
);

CREATE TABLE pauses (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subscription_id bigint NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  from_date date NOT NULL,
  to_date date NOT NULL,
  CHECK (to_date >= from_date)
);

CREATE TABLE deliveries (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  subscription_id bigint NOT NULL REFERENCES subscriptions(id),
  meal text NOT NULL CHECK (meal IN ('lunch', 'dinner')),
  delivery_date date NOT NULL,
  staff_id int REFERENCES staff(id),
  status text NOT NULL DEFAULT 'planned'
    CHECK (status IN ('planned', 'delivered', 'missed', 'refused')),
  delivered_at timestamptz,
  UNIQUE (subscription_id, meal, delivery_date)
);

CREATE TABLE payments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  amount_paise int NOT NULL CHECK (amount_paise > 0),
  paid_on date NOT NULL,
  method text NOT NULL CHECK (method IN ('cash', 'upi', 'bank')),
  covers_month date                                -- first of the month it is for
);

Step 5: the four decisions worth defending

Price as a history, not a column

CREATE TABLE plan_prices (..., valid_from date NOT NULL, PRIMARY KEY (plan_id, meal, valid_from));

The brief said "the price changed in April". With plans.price_paise, updating it in April silently rewrites every past month's bill — the invoice problem from the last lesson.

Getting the price on a given date needs a little work:

SELECT DISTINCT ON (plan_id, meal) plan_id, meal, price_paise
FROM plan_prices
WHERE valid_from <= DATE '2026-05-15'
ORDER BY plan_id, meal, valid_from DESC;

DISTINCT ON is a PostgreSQL extension and exactly the right tool: the latest row per group. Learn it; it replaces a window function and a subquery.

The alternative is valid_from/valid_to with an exclusion constraint preventing overlaps — tighter, more work. valid_from alone with the newest winning is a good default because it cannot develop gaps.

Money as integer paise

price_paise int NOT NULL CHECK (price_paise > 0)

Never float for money. 0.1 + 0.2 != 0.3 in binary floating point. The arithmetic error is small — summing ₹93.30 ten thousand times is off by about 0.00000016 — but equality breaks, so WHERE paid = amount_due is false for an invoice that has been settled exactly. The last lesson of this module measures it.

Integer paise, or numeric(12,2). Paise are faster and cannot be misused; numeric reads more naturally. Either is defensible. float is not, and the column name says the unit so nobody has to guess.

`deliveries` as rows, planned in advance

The temptation is to compute deliveries from subscriptions on the fly and store nothing. It works for "how many to cook tomorrow" and fails for everything else, because the business asked: "a customer complains a delivery never arrived and we need to check."

You cannot check a fact you did not record. A computed answer tells you what should have happened.

So a row per meal per customer per day, generated nightly:

INSERT INTO deliveries (subscription_id, meal, delivery_date)
SELECT s.id, sm.meal, DATE '2026-09-28'
FROM subscriptions s
JOIN subscription_meals sm ON sm.subscription_id = s.id
WHERE s.started_on <= DATE '2026-09-28'
  AND (s.ended_on IS NULL OR s.ended_on >= DATE '2026-09-28')
  AND NOT EXISTS (
    SELECT 1 FROM pauses p
    WHERE p.subscription_id = s.id
      AND DATE '2026-09-28' BETWEEN p.from_date AND p.to_date
  )
ON CONFLICT (subscription_id, meal, delivery_date) DO NOTHING;

Note ON CONFLICT DO NOTHING with the UNIQUE constraint: the job is safe to run twice. That is worth engineering for, because it will be run twice.

At 200 customers × 2 meals × 365 days that is 146,000 rows a year. Nothing.

`route_id` on the customer, not the subscription

The verb question from step 2. A route is determined by where someone lives, which is a property of the customer. If they move house the route changes and the old deliveries keep the staff member who actually carried them, because deliveries.staff_id records what happened.

Had routes been assigned per subscription, changing plan would have needed a route decision for no reason.

The general test: which entity does this fact actually depend on? Same question as normalisation's "what is this a fact about".

Step 6: answer the brief's questions

These queries are the specification. If they are hard, the model is wrong.

How many of each plan to cook tomorrow, by route:

SELECT r.name AS route, p.name AS plan, d.meal, count(*) AS portions
FROM deliveries d
JOIN subscriptions s ON s.id = d.subscription_id
JOIN customers c ON c.id = s.customer_id
JOIN routes r ON r.id = c.route_id
JOIN plans p ON p.id = s.plan_id
WHERE d.delivery_date = CURRENT_DATE + 1 AND d.status = 'planned'
GROUP BY r.name, p.name, d.meal
ORDER BY r.name, p.name, d.meal;

Did this customer's delivery arrive on the 12th:

SELECT d.meal, d.status, d.delivered_at, st.name AS delivered_by
FROM deliveries d
JOIN subscriptions s ON s.id = d.subscription_id
LEFT JOIN staff st ON st.id = d.staff_id
WHERE s.customer_id = 42 AND d.delivery_date = DATE '2026-09-12';

A direct answer, because the fact was recorded.

One honest gap: run this against rows the nightly job created and delivered_by comes back NULL, because the job does not assign staff — it only knows the route, and who drove it is decided in the morning. The schema can hold the answer; something still has to put it there. Either the delivery app sets staff_id on completion, or you assign the whole route at the start of the day:

UPDATE deliveries d SET staff_id = 1
FROM subscriptions s, customers c
WHERE d.subscription_id = s.id AND c.id = s.customer_id
  AND c.route_id = 1 AND d.delivery_date = CURRENT_DATE;

A nullable staff_id is right here: at planning time nobody has carried it yet.

What each customer owes for September:

WITH prices AS (
  SELECT DISTINCT ON (plan_id, meal) plan_id, meal, price_paise
  FROM plan_prices
  WHERE valid_from <= DATE '2026-09-30'
  ORDER BY plan_id, meal, valid_from DESC
),
billed AS (
  SELECT s.customer_id, sum(pr.price_paise) AS due_paise
  FROM deliveries d
  JOIN subscriptions s ON s.id = d.subscription_id
  JOIN prices pr ON pr.plan_id = s.plan_id AND pr.meal = d.meal
  WHERE d.delivery_date >= DATE '2026-09-01' AND d.delivery_date < DATE '2026-10-01'
    AND d.status = 'delivered'
  GROUP BY s.customer_id
),
paid AS (
  SELECT customer_id, sum(amount_paise) AS paid_paise
  FROM payments
  WHERE covers_month = DATE '2026-09-01'
  GROUP BY customer_id
)
SELECT c.name,
       round(coalesce(b.due_paise, 0) / 100.0, 2) AS due_rupees,
       round(coalesce(p.paid_paise, 0) / 100.0, 2) AS paid_rupees,
       round((coalesce(b.due_paise, 0) - coalesce(p.paid_paise, 0)) / 100.0, 2) AS balance_rupees
FROM customers c
LEFT JOIN billed b ON b.customer_id = c.id
LEFT JOIN paid p ON p.customer_id = c.id
WHERE coalesce(b.due_paise, 0) <> coalesce(p.paid_paise, 0)
ORDER BY balance_rupees DESC;

Run against three customers for September it gives:

     name     | due_rupees | paid_rupees | balance_rupees
--------------+------------+-------------+----------------
 Ravi Gadgil  |    3250.00 |        0.00 |        3250.00
 Neha Phadke  |    2470.00 |        0.00 |        2470.00
 Kavita Joshi |    4665.00 |     4000.00 |         665.00

Check one by hand, because you should always check one by hand: Kavita is on the veg plan with both meals, so 25 delivered days × 2 meals × ₹93.30 = ₹4,665. It agrees.

Two things in that query worth noticing.

Read status = 'delivered': you bill for meals delivered, not meals planned, and the pause handling is free because a paused day never became a delivery row. The design did the work.

And the round(…, 2). Without it, price_paise / 100.0 produces 4665.0000000000000000 — integer division by a numeric literal gives a numeric with far more scale than you want. A small thing, but it is the difference between a report you can hand somebody and one you cannot.

Step 7: the indexes

Only now, and driven by those queries:

CREATE INDEX ON deliveries (delivery_date, status);          -- tomorrow's cooking list
CREATE INDEX ON deliveries (subscription_id, delivery_date); -- one customer's history
CREATE INDEX ON subscriptions (customer_id);                 -- foreign key
CREATE INDEX ON subscriptions (plan_id);                     -- foreign key
CREATE INDEX ON customers (route_id);                        -- foreign key
CREATE INDEX ON pauses (subscription_id, from_date, to_date);
CREATE INDEX ON payments (customer_id, covers_month);

Note the UNIQUE (subscription_id, meal, delivery_date) already gives you a usable index, so the second one above is arguably redundant by the leftmost-prefix rule. It is — drop it and check the plan. That is the previous module's lesson applied.

Step 8: what I would do differently at scale

Honest limits of this design:

deliveries grows forever. At 200 customers it is fine for decades. At 20,000 it wants monthly partitioning, so old months can be detached cheaply.
status as free-text-with-CHECK is fine, and better than a PostgreSQL enum, because adding a value to an enum used to require a full rewrite and still cannot be done inside a transaction alongside other DDL in older versions. A lookup table is the third option and the most flexible.
No soft delete. Deliberate; see the next lesson but one.
covers_month as a date is a mild lie — it is a month, stored as its first day. A CHECK (date_trunc('month', covers_month) = covers_month) makes the lie safe.

The procedure, condensed

Nouns → tables. Ask of each: thing, or fact about a thing?
Verbs → relationships and constraints.
For each fact, ask what it depends on. That names its table.
Any fact that changes over time needs a date, not a column you overwrite.
Any boolean is suspect — it is usually a flattened date or period.
Write the queries the business asked for. If they are hard, go back to 3.
Then index.
Then write down what you would change at ten times the size.

Step 6 is the one people skip, and it is the one that finds the mistakes.

Check your work

The first question to ask of each noun. Is it a thing, or a fact about a thing?

Why paused boolean is wrong. It discards the dates, which is what the billing question needs.

The general suspicion about booleans. They are usually a flattened date or period.

Why price lives in its own table with valid_from. So changing it does not rewrite history.

The tool for "the latest row per group". DISTINCT ON.

Why never float for money. Binary floating point cannot represent decimal fractions exactly; errors accumulate.

Why deliveries are rows rather than computed. You cannot check a fact you did not record.

What makes the nightly job safe to re-run. UNIQUE plus ON CONFLICT DO NOTHING.

Where route_id belongs and why. On the customer — it depends on where they live.

What you bill for. Delivered rows, not planned ones — which makes pauses free.

When to add indexes. After the queries exist.

Why CHECK beats an enum here. Adding a value is cheaper and less constrained.

The step people skip. Writing the business's actual queries before declaring the model done.

Practice

Create all nine tables in an empty database. Fix every error you get.
Insert two plans, two price rows for one of them with different valid_from, and confirm DISTINCT ON returns the right one for a date in each period.
Insert 10 customers across 2 routes, subscriptions for each, and meals.
Run the nightly delivery-generation job. Run it twice and confirm the count is unchanged.
Add a pause covering tomorrow for one customer, re-run the job, and confirm they are absent.
Mark some deliveries delivered and some missed, then run the billing query.
Change a plan's price with a new valid_from and confirm last month's bill is unchanged.
Write the query for "which customers were paused for more than 5 days in September".
Try the same with a paused boolean design and explain why you cannot.
Add float prices to a copy of the schema, insert 1,000 rows of ₹93.30, and compare the sum with the integer version.
Drop the redundant delivery index and confirm the plans are unchanged.
Add the covers_month CHECK and try to insert the 15th.
Model the complaints table that was deferred. Decide what it references.
Now model a domain of your own from a one-paragraph brief. Write the brief first, in a client's words, then work the eight steps. This is the exercise that matters.

Official documentation

PostgreSQL — SELECT DISTINCT ON — The latest-row-per-group tool.
PostgreSQL — Numeric types — Read the warning about floating point and money in the authors' own words.
PostgreSQL — Date/time types — date versus timestamptz, which this schema uses deliberately differently.
PostgreSQL — INSERT ... ON CONFLICT — What makes the nightly job idempotent.
PostgreSQL — Table partitioning — The scale answer for deliveries.

Next: changing a schema that already has data in it.