Data Models
Conceptual vs Logical vs Physical Data Models in SDD
Learn the difference between conceptual, logical and physical data models, what each one decides, and why the logical model belongs in the spec.
Why the Data Model Belongs in the Spec
The database is the hardest part of an application to change. A screen can be regenerated in a minute. A table that already holds forty thousand rows cannot. Every other artifact can be rewritten; the data has to be migrated.
That is why data modeling matters more, not less, when an AI writes the code. Hand an assistant three feature specs with no data model and it will invent a schema for each one. Three features later you have a customers table, a clients table and an accounts table that all mean the same thing, and nobody decided that they should.
Spec-Driven Development closes that gap by treating the data model as a spec artifact: written before the code, reviewed by a person, and treated as the truth the code must match.
SPEED states the rule plainly: when the specification and the code disagree, the specification is correct and the code is the defect. For data, the specification is the logical model and the code is the migration.
Three Models, Three Questions
Data modeling has three layers. Each answers a different question and is read by a different audience.
| Model | Answers | Contains | Leaves out | Read by |
|---|---|---|---|---|
| Conceptual | What exists? | Things and how they relate, by name only | Attributes, keys, types | The business |
| Logical | What does it mean? | Attributes, identifiers, cardinality, optionality, normal form | Data types, indexes, platform | Business and builders together |
| Physical | How is it stored? | Tables, columns, types, keys, constraints, indexes, access policies | Nothing, because this is what runs | The database |
Each layer refuses one shortcut. The conceptual model refuses a noun nobody can define. The logical model refuses a fact stored in two places. The physical model refuses a table that no entity accounts for.
Where Each Model Sits in the Pipeline
The three models map onto the SDD pipeline you already know:
- Requirements. The nouns appear in your stories and EARS requirements. Sketch the conceptual model here. It should take ten minutes, not a day.
- Design. The logical model is a section of the design document. It is reviewed and approved with the rest of the design. This is where a person signs off.
- Tasks. Each migration becomes a task, and each task names the entity it builds.
- Implementation. The migration is generated from the approved logical model, never the other way round.
Once approved, the logical model is part of the project's authoritative record, not a sketch. Every migration is then checked against it before it ships, and the check asks one question: does this conform to the approved model?
The Mistake This Prevents
The common sequence runs backwards: prompt, then migration, then screens, and months later someone generates an ERD from the database for an onboarding deck. That diagram is a photograph, not a specification. It records what happened, accidents included.
The SDD sequence runs forwards: story, logical model, approval, migration, then a check that the live database matches what was approved.
The test for which one you have is short. If the diagram was generated from the database, it cannot tell you the database is wrong.
Key Takeaways
- The data model is a spec artifact. It is written first, reviewed by a person, and it wins disagreements.
- Conceptual asks what exists, logical asks what it means, and physical asks how it is stored.
- The logical model is the one a person approves. The physical model follows from it.
- A diagram generated from the database is a record, not a spec. It cannot catch the database being wrong.
Example
%% A conceptual model is small enough to write in a minute.
%% Mermaid erDiagram syntax renders in GitHub, GitLab, Notion and most docs tools.
erDiagram
MEMBER ||--o{ RESERVATION : places
TOOL ||--o{ RESERVATION : "is wanted in"
MEMBER ||--o{ LOAN : borrows
TOOL ||--o{ LOAN : "is lent in"
LOAN ||--|{ LOAN_EVENT : records%% A conceptual model is small enough to write in a minute.
%% Mermaid erDiagram syntax renders in GitHub, GitLab, Notion and most docs tools.
erDiagram
MEMBER ||--o{ RESERVATION : places
TOOL ||--o{ RESERVATION : "is wanted in"
MEMBER ||--o{ LOAN : borrows
TOOL ||--o{ LOAN : "is lent in"
LOAN ||--|{ LOAN_EVENT : recordsWhere You'll See This in the Real World
Any team that has had to merge two tables that turned out to mean the same thing has paid for a missing logical model. Migration tools such as Supabase, Prisma and Rails record the physical model very well. None of them can tell you that clients and customers should never have been separate. Only a model that was written and approved before the migrations can do that.