Where this goes next: fact, dimension, and vector tables¶
Optional extension reading. This is not assessed. It connects the small model you just built to the table roles you will meet in analytics and AI work.
The model you built in Airtable is an operational one. It is designed to run the clinic: capture each visit truthfully, store each fact once, and keep everything consistent. Once you want to analyse what the clinic did, or bring AI into the picture, you meet a few more table roles. They are not a new skill. They are the same "what is a thing, what is a fact about it, how do they connect" thinking, applied at a larger scale and for a different purpose.
Two jobs, two shapes¶
A data model is shaped by the job it has to do.
- Operational (what you built). Optimised for writing correctly. Store each fact once, avoid duplication, capture every visit as it happens. This is the transactional, day-to-day system.
- Analytical. Optimised for reading and summarising fast. Answer questions like "visits per clinician per month" across millions of rows without slowing down.
The same clinic reality gets modelled two ways, because writing a visit and analysing a year of visits are different problems.
Fact and dimension tables¶
The analytical shape has a name: dimensional modelling, often drawn as a star schema. It sorts tables into two roles, and they map almost exactly onto what you already built.
- A fact table stores the measurements of an event: numbers, and the foreign keys (links) that give them context. Your Visits table is a fact table in miniature. It is the event, it holds the "when" (the date), and it links out to the patient and the clinician. Add a cost or a quantity of medicine, and those are its measures.
- A dimension table stores the descriptive context, the who, what, where, and when. Your Patients, Clinicians, and Medicines tables are dimensions. So is a dedicated Date table. They change slowly and are referenced by many events.
| Your exercise table | Dimensional role | Because |
|---|---|---|
| Visits | Fact | The event and its measurements, plus links to context |
| Patients | Dimension | Descriptive facts about a person, stable across visits |
| Clinicians | Dimension | Descriptive context, referenced by many visits |
| Medicines | Dimension | A thing referenced across many visits |
Notice this is the exact lesson from Why model data? at a larger scale. "Patients hold the facts that stay true across every visit" is the definition of a dimension. "A visit is its own thing because you have many of them" is a fact table.
Where analytics inverts a rule you just learned
The operational model says never copy the same value onto every row. Dimensional models often do the opposite on purpose: they denormalise, happily repeating a clinician's name across a wide dimension table, to make reads fast. This is not a contradiction to hide. It is the trade-off at the next altitude: normalise for writing, denormalise for analytics. Same thinking, opposite optimisation.
One more idea comes with fact tables: grain. A fact table forces you to state exactly what one row means. One visit? One medicine line on a visit? That is the sharper version of the question you have been asking all along: "why is this its own thing?"
Aggregate tables¶
An aggregate table stores pre-calculated, pre-summarised data ("visits per clinician per month", already totalled) so that heavy reports return instantly instead of recomputing over millions of rows.
It is worth being clear that this is not a new thing in the world. It models nothing new. It is a cache of answers, derived from your fact and dimension tables and kept for speed. You reach for it late, when scale demands it, not when you are first working out what the things and facts are.
Vector embedding tables¶
A vector embedding table is the outlier, and the one most relevant to AI. It does not come from relational modelling at all.
It stores high-dimensional vectors that represent unstructured data: the meaning of a clinical note, an image, an audio clip, learned by a model. It is still "a table of things with facts about them", but the fact is a vector, and you query it by similarity, not by exact match or join.
That changes the question it answers:
- Fact and dimension tables answer "what are the things, and how do they connect?"
- A vector table answers "what is this content like?" It finds items by shared meaning and conceptual similarity, which is what powers semantic search and retrieval-augmented generation (RAG).
In practice these coexist. You might keep a clinical note in an ordinary row and store its embedding in a vector table keyed back to it, so a system can both look the note up exactly and find other notes that mean something similar.
The through-line¶
| Role | Stores | Answers | In the clinic example |
|---|---|---|---|
| Fact | numeric measures + foreign keys | how much, how many, when | Visits: date, cost, links to patient and clinician |
| Dimension | descriptive attributes (who / what / where / when) | which patient, which clinician, which location | Patients, Clinicians, Medicines, a Date table |
| Aggregate | pre-summarised rollups of facts | the fast answer to a heavy report | "visits per clinician per month", pre-computed |
| Vector embedding | high-dimensional vectors of unstructured data | what is this content like | embeddings of clinical notes for semantic search / RAG |
The same question scales the whole way up: what is a thing, what is a fact about it, and how do they connect? Fact and dimension tables reshape that for analysis. Aggregates cache it for speed. Vector tables add retrieval by meaning. The small base you built in Airtable is the foundation all of it rests on.