Designing a
data model
Structured data lives in tables of rows and columns, connected by IDs. What goes into those tables is a durable design decision.
Scroll, click, or use the arrow keys to move through.Scroll, or use the arrows and dots to move through.
The structure defines the questions that can ever be answered.
A question the structure cannot express is a question no amount of AI will answer. A system that stores only a customer’s current industry cannot report which customers changed industry last year. A system that stores an order total and not the tax line cannot tell you what the tax was.
Data that was never captured is not retrievable later.
An entity is an element the business tracks in its own right.
A customer, an order, a site, a quote, an inspection. Each one gets a table. Each row gets an ID that is issued once and never changes.
A thing that exists only as a column on something else cannot be counted, compared, or given a history. A site stored as a text field on an order is text in a field. The database does not know it is a site.
A value stored twice will eventually disagree with itself.
A customer’s address in three tables will differ in all three sooner or later. Nothing in the data says which is right.
The usual cause is copying a value for convenience instead of pointing at the row that owns it. Contradictions in a dataset are usually a structure problem.
A column holds the present. A dated relationship holds the history.
Store the company on the person and a job change overwrites the past.
| person | company |
|---|---|
| Megan | Ridgeline |
| person | company | from | to |
|---|---|---|---|
| Megan | Corva | 2019-03 | 2024-01 |
| Megan | Ridgeline | 2024-01 | — |
The second costs one extra table. It answers who she worked for in 2022 and how long the average tenure runs.
Anything that changes over time has the same two options.
Which rep owns an account. What price a product carried. What status a claim was in, and for how long.
Each can be a column holding the present or a dated relationship holding the history. The choice is effectively permanent once data starts arriving.
A status column and two timestamps carry most of the reporting.
The status field holds a short list of allowed values. The timestamps record when a row was created and when it last changed.
They make it possible to ask how long things sit in each stage, what has changed since the last run, and what the system believed on a given day.
A model reasons about what the schema made explicit.
Structure is context. A table with named columns and real types tells a model what each value means. A column called notes holding six different kinds of information tells it nothing. It will infer something anyway.
A convention that lives in someone’s head does not survive the trip into a prompt.
The model is a decision about what can be asked later.
Name the things the business already names. Store each fact once. Put dates on what changes. The structure outlives the screens built on top of it.