Unit 2 · Module 3

Designing A Schema From A Product Description

Every concept in this module, applied to one fictional app, one decision at a time

Putting it together

Every page in this module has covered one piece of relational design. Relationship shapes. Foreign keys. Reading schemas. Splitting tables. Denormalizing. Migrations. None of those exist in isolation in a real project. They all show up at once, in the same fifteen minutes of designing a new feature.

This page is the assembly. One product description, walked through from plain English to a working schema, with the decisions named out loud. Nothing here is magic. Every step is a question from a previous page, answered for this specific case.

The product

That description is doing a lot of quiet work. Hidden inside it are the nouns the schema needs to model, the relationships between them, and the queries the dashboard will eventually run. The job is to extract those pieces.

Step 1: list the nouns

Before any columns, any types, any foreign keys — read the description and pull out every noun that sounds like a thing the system would need to track.

Six candidate tables. Some of these will survive into the final schema, some will collapse into columns on other tables, and at least one will spawn a join table that wasn't on the list. That's typical of the first pass.

Step 2: ask the "Should This Be Its Own Table?" question for each one

Walk down the list and apply the four questions from earlier in the module: lifecycle, cardinality, queryability, its own data.

Stands. Each stand has its own lifecycle, the company has many stands, they'll absolutely be queried independently ("show me every stand's sales"), and they carry their own data (name, location). Yes, table.

Locations. Each stand is in one location and the location is just an attribute of the stand (address, neighborhood). No independent lifecycle. No "show me all locations" query that's different from "show me all stands." This is columns on the stands table, not a separate table.

Employees. People have their own lifecycle (hired, quit), one company has many of them, they'll be queried independently ("who's working today?"), and they carry their own data. Table.

Shifts. Each shift has its own lifecycle (scheduled, happens, gets logged), there are many per stand and many per employee, they'll be queried in their own right ("what shifts are tomorrow?"), and they have their own attributes (start time, end time, which stand, which employee). Table.

Drinks. Each drink type is its own thing (Classic Lemonade, Strawberry, Mint). Drinks have their own lifecycle (added to the menu, removed). Many drinks per stand, and the same drink can appear at multiple stands. They'll be queried independently. They carry their own data (name, price). Table.

Sales. Every sale is its own event, with a timestamp, a drink, a shift it happened during. Many sales per shift, many per drink. Definitely queried independently — the entire dashboard is built on sales queries. Table.

Five surviving tables: stands, employees, shifts, drinks, sales. Locations collapsed into columns. Already the schema is taking shape.

Step 3: identify the relationship shapes

For every pair of tables that might be related, ask: one-to-one, one-to-many, or many-to-many?

The stands-drinks many-to-many is the most interesting decision. Without asking the shape question, it's tempting to put a list of drinks on each stand row, or a list of stands on each drink row. Neither works in relational SQL. The right answer is a join table.

Step 4: draft the schema

With the tables and relationships decided, the SQL is mostly mechanical:

The first draft

CREATE TABLE stands (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  address       TEXT NOT NULL,
  neighborhood  TEXT,
  opened_on     DATE NOT NULL
);

CREATE TABLE employees (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT NOT NULL,
  email         TEXT UNIQUE NOT NULL,
  hired_on      DATE NOT NULL,
  is_active     BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE shifts (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  stand_id      UUID REFERENCES stands(id),
  employee_id   UUID REFERENCES employees(id),
  starts_at     TIMESTAMPTZ NOT NULL,
  ends_at       TIMESTAMPTZ NOT NULL
);

CREATE TABLE drinks (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT UNIQUE NOT NULL,
  base_price    NUMERIC(6,2) NOT NULL
);

-- The many-to-many join table
CREATE TABLE stand_drinks (
  stand_id      UUID REFERENCES stands(id),
  drink_id      UUID REFERENCES drinks(id),
  local_price   NUMERIC(6,2),               -- per-stand override
  PRIMARY KEY (stand_id, drink_id)
);

CREATE TABLE sales (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  shift_id      UUID REFERENCES shifts(id),
  drink_id      UUID REFERENCES drinks(id),
  sold_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  price_charged NUMERIC(6,2) NOT NULL
);

Six tables. Five one-to-many relationships and one many-to-many. Notice that stand_drinks isn't just a pure connector — it has a local_price column, because the same drink might cost differently at different stands. The moment a join table needed an attribute of its own, it earned that attribute, exactly the way the "Should This Be Its Own Table?" page described.

Also notice sales.price_charged. The price is on the sale row itself, not pulled from drinks.base_price or stand_drinks.local_price at read time. That's a small deliberate denormalization, and it's the right call: prices change over time, but a sale that already happened needs to remember what it actually cost at that moment. Recomputing from the current price would silently rewrite history.

Step 5: sanity-check against the queries

A schema looks right when the questions the product needs to answer can each be expressed as a clean query. Going back to the dashboard requirements:

"Daily sales per stand."

Daily sales per stand

SELECT
  stands.name,
  DATE(sales.sold_at) AS day,
  SUM(sales.price_charged) AS revenue
FROM sales
JOIN shifts ON sales.shift_id = shifts.id
JOIN stands ON shifts.stand_id = stands.id
GROUP BY stands.name, DATE(sales.sold_at)
ORDER BY day DESC, revenue DESC;

Three joins, one aggregation. Reads cleanly. The schema handled the question without contortion.

"Who's working tomorrow, and at which stand?"

Tomorrow's shifts

SELECT
  employees.name AS employee,
  stands.name    AS stand,
  shifts.starts_at,
  shifts.ends_at
FROM shifts
JOIN employees ON shifts.employee_id = employees.id
JOIN stands    ON shifts.stand_id    = stands.id
WHERE DATE(shifts.starts_at) = CURRENT_DATE + INTERVAL '1 day';

Two joins, a date filter. Also clean.

If a question that the product clearly needs to answer turns out to require a query that's awkward, twisted, or has no clean form at all, that's the schema telling you something is wrong with the table layout, not the query. Sanity-checking against the real queries is how design problems surface before they're set in stone.

Step 6: anticipate the migrations that will happen

Even with a careful first pass, this schema will change. Some realistic near-future migrations:

None of these are failures of the first design. They're the schema growing into a product that's also growing. Every one of them is a migration that gets written, reviewed, and committed like any other change.

Where Unit 2 stands now

The unit started with "what is a database" and ends here with "design one for a real product." The path between those two points was: where data lives, what a database is, how to read and write rows with SQL, how relationships work, and how to design tables on purpose.

The next module steps from theory into the specific tool used through the rest of the course: Supabase. Setting it up, connecting it to apps, running real queries through the dashboard and the client library, and seeing every concept from this unit show up in a project you can actually open in a browser.