On this page
Notes · Block 1 · Week 3 · Databases and domain modelling
You cannot store what you cannot describe
From an app that forgets on refresh to the tables that remember Aarav's world.
The one thing to keep
A database is useless until you can say what shape the thing you are storing has. Three questions give the shape: what are the things, what does each thing have, how do they connect. Answer them on paper and the tables fall out. An FDE does exactly this inside a customer's business: name the things in their world before building anything around them. Read once, then do the lab; these notes are the lab's ideas without the doing.
01Where We Left Off: a 200 and a Plan That Vanishes
The lesson on building APIs and the backend ended with a working loop: Aarav, your first customer, pastes his workflow, your prompt and his words go to the model, a plan comes back and the UI shows it. The call returned a 200. Then Aarav closes the tab. On Tuesday the plan, the chat, everything he said is gone. The app worked and remembered nothing.
02Memory Dies. Storage Survives.
Everything a running program holds lives in memory, and memory dies the moment the program stops. The APIs lesson said the frontend cannot store data; the backend you built cannot either, not on its own. Storage is a place outside the running app, on disk, that keeps rows after the app is gone. That place is a database.
In the lab you switch "save to a table" on and watch each message become a row with who said it, what and when. Close the app, reopen it, the rows come back. Nothing about the model changed. Only where the words lived.
03The Real Skill Is Describing the Shape
A table has columns, and before you can save a message you must say which columns a message needs. You cannot save a shape you have never described. So before any tool, three plain questions about your user's world:
- What are the things? Candidates become tables.
- What does each thing have? Facts become columns.
- How do the things connect? Links become pointers between rows.
This is domain modelling: the domain is your user's world, and the model is the smallest description of it that still answers the product's questions.
04Question 1: The Create, Read, Update, Delete Test
Nouns are clues, not answers. The test for a thing is the one you met in the APIs lesson: can the app Create one, Read one, Update one, Delete one, on its own, and can there be many? Aarav is a user. Each chat is a conversation. Each bubble is a message. His weekly workflow is a thing; so is every diagnosis the app writes about it, because tomorrow it writes another and the old one must survive. Jira and Slack are tools, and tools appear in many workflows.
"High-priority" fails the test. It is a word inside a message; the app never creates or deletes it on its own. His manager fails too: a person in Aarav's life, not a row in your app. Over-splitting into tables is the most common beginner move, and the test is how you refuse it.
05Question 2: Facts, and the Boundary
A fact is what you need to recreate one of the thing. For a message: who said it, what was said, when. Strip the message away and "content" means nothing; that is the signature of a fact. Facts become columns.
The whole skill is drawing one boundary. Aarav picks a tone for each plan. Today tone is a fact on the diagnosis. If users could save and reuse named tones, a tone would gain identity and a life, and it would become a thing with its own table. The word does not decide. What the product needs to do with it decides.
06Question 3: Three Shapes of a Link
A spreadsheet stores facts about one thing. A database stores facts and how things link. Every link you can phrase has one of three shapes:
| Shape | In Aarav's app | How it is stored |
|---|---|---|
| One-to-one | Rare. Worth a second look: the two may be one table. | A pointer marked unique. |
| One-to-many | One user, many conversations. One conversation, many messages. One workflow, many diagnoses. | A pointer on the many side: user_id on conversations, conversation_id on messages. |
| Many-to-many | A workflow uses many tools; Jira appears in many workflows. | No single cell can hold "many". A third table, one row per pairing: workflow_tools. |
One-to-many is most of every model you will ever draw. The rule to carry: the many side points back.
07Keys: Every Row Needs an Address
Two messages both say "okay". How does the app tell them apart? It cannot, unless every row has an address that never repeats: a key, usually a column called id. Like an apartment number in a building, it lets anything else point at this exact row later.
A link is then just a column on the many side holding the other row's key. conversation_id = 7 on a message means "I belong to conversation 7". That column is a pointer (the formal word is foreign key). Follow the pointers and you can trace any message back to its conversation and up to the user who owns it.
08The Fact of a Pairing
How often does Aarav use a tool in a workflow? Jira is daily in one workflow and monthly in another. The fact belongs to neither the tool nor the workflow; it belongs to the pairing, so it lives on the workflow_tools row. The same move puts a grade on an enrollment and a quantity on an order line. Ask what each value describes, and put it there.
09History Is a Row, Not a Cell
Why is a diagnosis its own table instead of a plan column on the workflow? Because Aarav will ask for the plan again, in a casual tone, and the old one must survive. A cell can hold one value; writing a new one erases the old. If the product must remember what was true before, make it a row with a time on it. The current one is the newest row. The hospital brief in the lab drills exactly this.
10Rows or Files
Aarav attaches a screenshot of his Jira board. A cell holds a value you can type: text, a number, a date, yes or no. A screenshot is bytes. It goes in file storage, and a row keeps the link to it plus the facts about it: which message it came with, its name, when it arrived. Rows for facts, files for everything large or binary, and a pointer between them. In Supabase that is a table and a storage bucket.
11Aarav's Model, and the Bridge to Your API Table
Eight tables, every one placed by hand in the lab:
| Table | Columns | Points to |
|---|---|---|
| users | id, name | |
| conversations | id, user_id, started_at | users |
| messages | id, conversation_id, role, content, created_at | conversations |
| attachments | id, message_id, file_url, file_name | messages |
| workflows | id, user_id, description | users |
| diagnoses | id, workflow_id, plan, tone, created_at | workflows |
| tools | id, name | |
| workflow_tools | id, workflow_id, tool_id, how_often | workflows, tools |
Beside it, your API table from the practice set for The second interface: APIs: POST /workflows creates a row in workflows; POST /workflows/{id}/diagnoses creates a row in diagnoses. Every noun in an address is a thing in the model. The API is how a request reaches a row.
12Done Means It Answers the Questions
A model is not done when it looks tidy. It is done when it can answer what the product must ask: "Aarav's last conversation, every message in order", "every plan this workflow ever got, oldest first", "which screenshot went with which message". Write your own three questions first, then check that your tables can answer each one by pointing at the exact pointers. Then try to create a wrong state, a message with no conversation or the same pairing twice, and make sure the model refuses it.
13Next: The Same Tables, in Supabase
In the next lesson you connect the FastAPI backend to Supabase, type the tables above into Supabase's table editor, column by column, and watch a row survive a refresh. Nothing new to model. That is the point of doing it on paper first.
Words
You cannot store what you cannot describe.Do the lab, then finish the practice set before you start the next lesson.