AmouAI Hub/Courses/Software Engineering for AI Engineers/Chapter 6
Access Patterns Before Schemas
Ng gives data its own section for a reason: it is the foundation everything sits on and the hardest thing to change afterwards. Most bad schemas were designed by listing the nouns in a product brief. Good ones are designed by listing the questions the system will be asked, in the order it will be asked them, with how often attached.
By the end of this chapter you can
- Write an access-pattern table before writing a line of DDL
- Extract the query hiding inside a product requirement
- Attach a frequency and a latency requirement to each pattern, and use them
- Compare two schemas by what each one makes hard rather than by which looks tidier
- Name the question a schema cannot answer, which is the cost nobody records
- Recognise when a pattern is telling you this is not relational data at all
1Nouns produce bad schemas; questions produce good ones
The standard method is to read the brief, underline the nouns, and make a table for each one. It is fast, it feels like modelling, and it reliably produces a schema that answers the questions nobody asks and struggles with the ones everybody does.
Here is why. A noun tells you what exists. It tells you nothing about how it will be reached, how often, with what filters, sorted how, or how fresh the answer must be. Those five things decide the design, and none of them is in the noun.
Consider a brief containing “invoices”, “customers” and “payments”. Three nouns, three tables, and you are done in five minutes. Now ask what the product actually does with them:
| The question the product asks | How often | What it implies |
|---|---|---|
| Show this customer their unpaid invoices, newest first | Every page load | Filter by customer and status, sorted by date — the single most important shape |
| How much is this organisation owed, by age band? | Every dashboard load | An aggregate over a computed bucket, which is not a column |
| Which invoices became overdue today? | Once a day, in a job | A range scan on a date — cheap, and only if the date is stored rather than derived |
| What is this customer’s average time to pay? | Rarely, in a report | Needs both an issue date and a payment date on the same row, or a join nobody planned |
| Show every invoice mentioning “consultancy” | Occasionally | Not a relational question at all. This one is a search index, and finding that out now is worth a week. |
A schema is not a description of your domain. It is a bet about which questions will be asked. Writing the questions down first makes the bet explicit, which is the only way to notice later that you bet wrong — and, more usefully, the only way to notice now that one of the questions belongs in a different kind of store entirely.
2The access-pattern table
Five columns, one row per question. It takes twenty minutes and it is the highest-value twenty minutes in the whole design.
# question freq latency freshness
1 invoices for customer, newest first every load <150ms live
2 amount owed by age band, per org every load <300ms 60s ok
3 invoices going overdue today 1/day any live
4 average days-to-pay per customer 1/week any 24h ok
5 full-text search over line items rare <500ms 5min ok
Consequences
- 1 is the hot path. It decides the primary index and the row shape.
- 2 buckets on a value that is not stored. Either compute it or store it.
- 3 needs due_on as a stored column, not derived at query time.
- 4 needs issued_on and paid_on on the same row, or an expensive join.
- 5 is not a relational question. Chapter 10 gives it somewhere to live.Three things make this document earn its place. Frequency tells you which pattern is allowed to be slow — the weekly report can take four seconds and nobody will ever know. Latency turns “fast” into a number that a test can check. And freshness is the caching decision from Chapter 5, arriving before there is anything to cache, which is when it is cheapest to make.
The last section is the one people skip and the one that pays. Pattern 5 does not belong in the database
at all, and noticing it here costs nothing. Noticing it after six months of LIKE '%consultancy%'
costs a migration.
3Two schemas from one brief
The best way to feel this is to run the same questions against two designs. The database below is real SQLite, running in this page, and the queries are genuinely executed.
Both schemas hold the same facts. One was designed from the nouns; the other from the questions. Neither is more “normalised” than the other in any strict sense — the difference is which questions they anticipated.
That query is correct and it is doing three things the product will do on every page load: summing line items to discover a total, checking for the absence of a payment to discover a status, and sorting. None of those results is stored, so none of them can be indexed, and all three get slower with the size of the account.
Now the same questions against a schema designed from them:
4The query hiding inside a requirement
Product requirements almost never contain a query. They contain a sentence from which exactly one query follows, and getting it out is a skill you can practise.
The method is three questions, asked of every requirement:
- What am I filtering on? These become the leading columns of an index. If the answer is “whatever the user typed”, that is a search problem rather than a filter.
- What am I sorting by? A sort on an unindexed column means the database gathers every matching row before it can return the first one, which is why an endpoint can be fast with a hundred rows and hopeless with a hundred thousand.
- What am I aggregating? A sum or a count over a set that grows with the customer is the single most common source of a query that ages badly.
Run those three questions over “the dashboard should show how much each organisation is owed, split by how overdue it is” and you get: filter on organisation and status, no sort, aggregate a sum over a bucket derived from a date. The bucket is the interesting part — it is not a column, so either it gets computed per query or it gets stored, and that is a decision with a maintenance cost attached.
Modelling a requirement that says “search” as a LIKE filter. A leading
wildcard cannot use a B-tree index, so WHERE description LIKE '%consultancy%' reads every
row in the table, every time. The symptom is a search box that works beautifully in development and
becomes the slowest endpoint in the product within a year, and by then it is on a page nobody wants
to remove. Chapter 10 gives search somewhere proper to live; Chapter 8 explains exactly why the index
cannot help.
5What a schema forbids
The interesting property of a design is not what it supports. It is what it makes impossible, and that list is never written down.
Every modelling choice closes doors. Storing a status column forbids asking “what was the status
last Tuesday?” unless you also keep history. Storing a customer’s address on the invoice makes
historical invoices correct forever and forbids updating an address in one place. Choosing a single
total_pence forbids multi-currency without a migration.
None of those is wrong. All of them are worth writing down, because the future request that runs into one of them will arrive as “can we just…”, and the honest answer will be a number of weeks that nobody understands.
| The choice | What it buys | What it forbids |
|---|---|---|
| Status as a stored column | An indexable, sortable hot path | Asking what the status was at any past moment |
| Address on the customer, not the invoice | One place to update it | Reprinting a two-year-old invoice as it was sent |
| Address copied onto the invoice | Historical accuracy forever | Fixing a typo everywhere at once |
One total_pence integer | Exact money arithmetic, no float error | Multi-currency, without a migration |
| Hard delete on cancellation | A simple, small table | Any question that begins “how many did we cancel…” |
| Line items in a JSON column | Flexible shape, one row per invoice | Aggregating across line items without scanning every invoice |
6Build the table
The exercise below is the one worth repeating on every project. It is short, it is boring, and it prevents the three failures above.
Take the brief for anything you are about to build and write out, for each thing the system will be asked: the question in plain English, the filter, the sort, the aggregate, how often, how fast, and how fresh. Then read the list and mark the ones that are not relational questions.
Two rules make the output useful rather than decorative. First, order the rows by frequency, so the hot path is at the top and it is obvious what the schema is optimising for. Second, write a consequences section: for each pattern, one line saying what it implies about the design. A table with no consequences section is a list of requirements; a table with one is a design document.
An agent will happily generate a schema from your brief in four seconds, and it will be a competent schema for the questions it guessed. Handing it the access-pattern table instead changes the output completely, because the table contains the information the brief did not: what this data is going to be asked, and how often. That is the steering this whole course is about, applied to the part of the system that is hardest to change afterwards.
Take a table from something you have built and write down three questions it answers well and one it answers badly. Then work out whether the bad one is a missing index, a missing column, or a question that never belonged in that table. It is usually the third, and the fix is usually smaller than it looks once you have named it.
✓Checkpoint
Five questions. Commit before you read the explanation.
▶Playground
A full Ledger dataset, open. Write anything. The suggestions in the comments are the five access patterns from section 2 — try each one and notice which are easy and which are awkward.
✓Exercise set
Twelve problems. Most of them are SQL, executed against a real SQLite build seeded with the Ledger dataset — the row counts and the error messages are the ones SQLite produced. Press Run to see your result set before you press Check. Your work is saved in this browser.
Chapter 7 — Relational Modelling That Holds Up
A constraint is a rule the database enforces at three in the morning when nobody is watching. Application code is a rule you hope everyone remembers.