Data Models
AI Prompts for Data Modeling: ERDs, Migrations and Drift
Use five paste-ready prompts that have an AI assistant draft a logical model in Mermaid, check its shape, write the migration and check for drift, while the approval stays with you.
The Rule for AI and Data Models
The AI drafts, you approve, and the spec wins. Let the assistant do the typing and the tedious cross-checking. Never let it approve its own model, and never let it "fix" the model to match the database without telling you.
Prompting for Models
Five paste-ready prompts, in pipeline order. Each one hands the assistant the previous step's approved output, not its own earlier guesses. Replace the bracketed parts.
Prompt 1: Stories to Conceptual Nouns
text
Here are our user stories: [paste stories].
List every noun the business cares about. Sort each one into exactly one of:
entity, event, attribute, role, or reference set, with one line of reasoning.
Then list the relationships between the entities as plain sentences.
No attributes, no keys, no data types yet.
List any noun you could not classify as an open question.Prompt 2: Conceptual Model to a Logical ERD in Mermaid
text
Using the approved conceptual model below, draft the LOGICAL model and
render it as a Mermaid erDiagram with crow's foot cardinality.
For each entity: a one-sentence meaning, the business identifier people say
out loud (mark it PK; no surrogate keys), and its attributes. In Mermaid's
type slot use a domain word (identifier, name, date, moment), not a database
type. Show no foreign keys.
For each relationship, also write both sentences: "One X has [zero or more /
one or more] Y. Every Y belongs to [exactly one / at most one] X."
Resolve every many-to-many into an associative entity. Normalize to 3NF.
Mark derived attributes as derived, not stored.
Label every attribute, relationship and rule STATED (in the stories) or
ASSUMED (your guess). Do not hide assumptions in prose.
[paste conceptual model + stories]Prompt 3: Review the Record Shape
text
Review this logical model for record-shape problems only. For each finding
give the entity, the problem and a proposed fix:
1. A record that holds both what a thing IS and what HAPPENED to it, where an
update would destroy history someone could ask about.
2. A single status field changed by two owners for two different reasons.
3. A fact stored in two places, including a stored value that could be
computed from others.
If a category has no findings, say "none found" for it.Prompt 4: Logical Model to a Migration That Cites Its Entity
text
Generate a Postgres migration from the APPROVED logical model below.
- First line: a comment naming the entity this migration builds, and the
model version it was generated from.
- uuid primary keys; each business identifier kept as a UNIQUE column.
- Foreign keys on the many side; choose ON DELETE explicitly and explain it.
- Index every foreign key column.
- Rules become CHECK constraints. Derived attributes become views.
- Enable row level security on every table, then one policy per operation.
History tables get no UPDATE or DELETE policy.
Add nothing that is not in the model. If something seems missing, stop and
list it as a question instead of adding it.
[paste approved logical model]Prompt 5: Live-Schema Drift Check
text
Compare the live schema below (exported from the running database, not
read from the migration files) with the data dictionary entries below.
Report four lists: tables with no entry, entries with no table, mismatches
(attribute, type, relationship or rule), and tables with row level security
turned off.
Do not propose editing the dictionary to match the database. For each
finding say which it looks like: the table is wrong, or the model needs a
deliberate change that someone must approve.
[paste schema export + dictionary entries]Before You Approve: Six Questions
Read the AI's output with these questions, and do not approve until each one has an answer:
- Can every entity be defined in one sentence a business owner would agree with?
- Does every entity have an identifier that people actually use?
- Is every relationship written as two sentences, and do both read true?
- Is every ASSUMED item either confirmed or deleted?
- Is any fact stored twice, or any summary stored instead of computed?
- Does the physical migration add anything the logical model did not ask for?
Key Takeaways
- The assistant drafts and checks. A person approves, and the approved model is the spec.
- Feed each prompt the previous step's approved output, not the assistant's earlier guesses.
- Make assumptions visible by asking for STATED and ASSUMED labels.
- Tell the assistant never to add structure the model did not ask for, and never to rewrite the model to match the database.
Example
markdown
## Data model review record
Model: tool library, logical v1
Reviewed with: library coordinator
Date: 2026-09-23
| Item | Was | Now | Note |
|---|---|---|---|
| Loan renewals limited to one | ASSUMED | STATED | Coordinator: "one renewal, then it must come back" |
| Member email stored on loan | present | removed | 3NF: depends on member, not loan |
| Tool status (single field) | one field | two fields | Lending state vs maintenance state, two owners |
| Loan status | stored | derived | Computed from latest loan event |
Decision: APPROVED. Physical migration may be generated from this version.Example — MARKDOWN
## Data model review record Model: tool library, logical v1 Reviewed with: library coordinator Date: 2026-09-23 | Item | Was | Now | Note | |---|---|---|---| | Loan renewals limited to one | ASSUMED | STATED | Coordinator: "one renewal, then it must come back" | | Member email stored on loan | present | removed | 3NF: depends on member, not loan | | Tool status (single field) | one field | two fields | Lending state vs maintenance state, two owners | | Loan status | stored | derived | Computed from latest loan event | Decision: APPROVED. Physical migration may be generated from this version.
Where You'll See This in the Real World
Teams that let an assistant generate migrations straight from a feature prompt usually find the damage a few sprints later: duplicate tables, status fields that disagree, and a table nobody can explain. The prompts in this lesson are a way of putting the approval step back. The assistant is still doing most of the work, but a person has read and agreed to the model before any of it touches the database.