How to Design a Database Schema With AI
Four sentences of requirements go in, a confident schema comes out — and it quietly decided three things nobody decided. Here's how to turn real domain rules and access patterns into a reviewable database schema, with the modeling gaps named as open questions instead of guessed.
Build a Schema Design PromptFour sentences of requirements, a schema full of decisions nobody made
What you have is thin: "customers subscribe to a plan and get billed monthly," "show them their invoice history," "plans have a name and a price," "customers can cancel." What you need is a data model. So you paste the four sentences into an AI and ask it to design the database, and back comes a tidy set of tables with keys and types and a confident air about it. The trouble isn't that it's malformed — it parses, it would run. The trouble is that somewhere in that tidy little schema it decided a customer can only ever hold one subscription, that an invoice's amount can be read back from the plan's current price, and that "cancel" means deleting a row. Nobody decided any of that. The requirements never said. The model needed a column, so it picked one.
Those picks are the expensive kind. An ambiguous prompt gets rewritten in a minute; a wrong cardinality gets discovered in month seven, after it has data in it and three services reading it. So the job here isn't getting tables out of an AI — that part is easy, and that's the problem. The job is getting a candidate database schema you can actually review: every relationship carrying a direction and a cardinality, every key and constraint traceable to a rule someone stated, and every question the requirements didn't answer sitting in an open-questions list instead of quietly resolved into a column. The model can draft that from what you give it — your real domain rules, your access patterns, your sample records, the constraints you already live with. What it can't do is know your business. NewPrompt's reach here stops at the prompt: it never touches a database — no connection, no introspection of an existing schema, no DDL, no migration. The schema that comes back is a candidate with nothing promised about whether it's correct, whether it performs, or how it holds up at scale, and the cardinality, the constraints, the indexes and the lifecycle rules are all things a domain owner and a database engineer confirm before anything gets created.
Why "design my database" returns a textbook, not your domain
Ask a model to design a database from a short brief and it does something reasonable: it pattern-matches your nouns onto the shape of every similar system it has seen. That's genuinely useful for a first sketch and genuinely dangerous as a design, because the gap between "a normal subscription app" and *your* subscription app is exactly where the modeling decisions live. Five failures, in rough order of how much they cost to undo:
- It resolves ambiguity silently. The requirements don't say whether a customer can hold two subscriptions at once, so the model picks one — usually by adding a unique constraint you never asked for — and now an assumption is a schema rule with no note saying it was ever a question.
- It derives facts that should be frozen. An invoice's amount gets modeled as a lookup to the plan's price, which reads fine until someone raises the price and last year's invoices change. Historical facts need to be stored as they happened, not re-derived.
- It models the happy path only. Cancel, expire, refund, downgrade, delete-my-account — the states that make a domain hard are the ones a four-sentence brief omits and a model won't invent unprompted.
- It guesses cardinality from the noun. "Order has items" becomes one-to-many; "product has tags" becomes one-to-many too, when it's many-to-many and needs a join table. The word doesn't tell you the shape — the domain does.
- It designs with no idea what you'll query. A schema is a set of trade-offs against read and write patterns, and a model given no access patterns optimizes for nothing in particular — which usually means it looks clean and reads badly.
Step 1: Bring the rules and the queries, not just the nouns
Two inputs decide whether the draft is worth reviewing, and briefs routinely carry neither. The first is the business rules in their real form, including the awkward ones: what makes a record valid, what must never happen, what changes over time and what must stay frozen once written, and what the lifecycle actually is — created, then what? The second is the access patterns: the reads and writes the application will really perform, roughly how often, and which ones have to be fast. "Show a customer their invoice history" is an access pattern; it tells you more about the shape of the invoice table than any noun in the brief does.
Then add the sample records and the constraints you already live with — an existing system's quirks, a compliance rule about retention, a reporting job that needs the data a certain way. Be equally explicit about what nobody has decided. If it's genuinely undecided whether canceling ends billing immediately or at period end, say so, in those words. That sounds like a small thing and it's the whole difference between a draft that flags the question and a draft that answers it for you. Every certainty you hand over comes back as a column — including the ones you didn't mean to express.
Step 2: Give every entity exactly one job
Before anything becomes a column, get the concepts named and bounded. Ask the model to list the entities with a one-line responsibility each, and then read that list adversarially: an entity whose description needs the word "and" is usually two entities. A `customer` that holds the billing address and the login credentials and the current plan is three ideas sharing a table, and it'll fight you the first time someone has two addresses.
The reverse mistake is real too — splitting things that never move independently just because a diagram looks tidier. The test is whether the two parts have different lifecycles or different owners. A subscription and its invoices change on completely different schedules and one is immutable once issued, so they're separate. A customer's name and email change together and belong to the same party, so they're not. Work this out at the concept level, in plain language, while it's still cheap to be wrong.
Step 3: Give every relationship a direction and a cardinality
This is where the silent assumptions hide, so make them loud. Every association gets written as A —(1:1 / 1:N / N:M)— B, with the direction stated and the meaning spelled out in the domain's own words: not "customer has subscriptions" but "a customer may hold several subscriptions at once, one per product line." Force every many-to-many to resolve into an explicit join table, because a many-to-many left implicit is a table you'll discover you needed after the data is in.
Then interrogate the optionality, which is the part everyone skims. Can a subscription exist without a plan? Must an invoice always belong to a subscription, or can there be a one-off charge? Is the relationship required at creation, or only later? "Optional" and "required" are business rules wearing a nullability costume, and if the requirements don't settle one, that's an open question — not a default. The Schema Design & ERD Prompt is built around exactly this discipline: it asks for every relationship with its cardinality and direction, it refuses to leave a many-to-many implicit, and its standing rule is that where the requirements don't decide a modeling question, the question goes in Open Questions rather than getting quietly picked. It's a copy-paste prompt — you adapt it to your domain and run it in your own AI tool.
Step 4: Choose the keys and constraints that protect the data
Constraints are where a design stops being a diagram and starts being enforceable. Go through it deliberately: the primary key for each table, every foreign key with its target and its on-delete behavior, what's unique, what's genuinely required versus merely usually-present, and the check constraints that pin a status to a closed set instead of letting `cancelled`, `canceled`, and `CANCELLED` all coexist. On-delete deserves real thought rather than a default — `CASCADE` on a customer with billing history is a compliance incident waiting to happen, and `RESTRICT` is often the right call.
Ask the model to justify each constraint against a stated rule, and treat any it can't justify as suspect. This is the step where an invented rule does the most damage, because a constraint is a decision the database will enforce forever, silently, on everyone. A check constraint nobody asked for doesn't announce itself — it just makes a legitimate future case impossible, and nobody remembers why the line is there. If the evidence for a constraint is "it seemed sensible," it belongs in the open questions with the name of the person who can settle it.
Step 5: Run the queries and the edge cases against the schema
A database schema is a bet about how data gets read and written, so test the bet before you build on it. Walk each real access pattern through the candidate model: to show a customer's invoice history, how many joins is that, and does an index actually follow from it? Then walk the edge cases — the ones the brief never mentioned. What happens when a plan is discontinued but active subscriptions still point at it? When a customer cancels mid-period? When the same invoice needs reissuing? Do it concretely, with sample rows written out, because an edge case argued in the abstract always sounds survivable and a written row shows you the null you can't populate.
This is also where the normalization trade-off should become visible rather than implicit. Normalizing by default is a sound starting position, but there are places where a duplicated field is the correct answer — an invoice's charged amount must be stored, not derived, because an invoice is a historical fact and the plan's price is a current one. What matters is that the trade-off is written down with its reason, so the next person reads a decision instead of guessing at an accident. Ask for it explicitly, and make gaps carry through: a good candidate ends with an open-questions list, each one paired with the question that would settle it.
One structural check is worth doing mechanically. The whole premise here is that gaps come back flagged, which fails quietly if the model drops the open-questions section on its way out — and a long document is exactly where that goes unnoticed. Paste the output into the AI Output Validator, give it the sections you asked for, and it scans the headings in your browser and tells you which are missing or out of order, with a repair prompt when it finds something. Be clear about what that buys you: it confirms the section is there, not that the schema is right. Structure it can check; correctness is still yours.
Common mistakes
The habits that turn an AI-drafted database schema into a foundation you regret:
- Accepting a constraint nobody asked for. A unique index on `customer_id` is a business rule the requirements never stated; make every constraint trace to a rule, and send the rest to open questions.
- Treating a lifecycle state as a delete. Canceling isn't `DELETE`; it's a state with a timestamp. A row removed is billing history removed, and the requirement to keep it usually surfaces from finance, late.
- Storing money as a float. It reads fine and rounds wrong, and a rounding bug in a billing table becomes an incident with customers in it. Money is a decimal or an integer count of minor units — a model will hand you `float` without blinking.
- Bolting on soft-delete without touching the unique index. Add `deleted_at` to a table with `UNIQUE(email)` and a deleted row still occupies the slot, so the customer who leaves can never come back. The index needed to become partial at the same moment, and nobody notices until support files the ticket.
- Picking the key type by vibe. UUID versus bigint changes index size, insert locality, and write cost at volume; it's a decision with reasons, not a default to inherit from whatever the model saw most.
- Mistaking a candidate for a decision. It's a draft the domain owner and a database engineer review — NewPrompt doesn't read your database or check that the model is right.
A worked example: subscriptions and invoices
Four sentences of real requirements, the schema a thin ask returns, and the candidate you can actually put in front of someone.
Four sentences of requirements turned into a generic schema with three invented decisions, versus a candidate schema built on the rules the brief left out — explicit cardinality, constraints traced to stated rules, a duplication trade-off argued rather than hidden, and the undecided rules kept as open questionsRAW REQUIREMENTS (all you actually have):
- "Customers subscribe to a plan and are billed monthly."
- "Show a customer their invoice history."
- "Plans have a name and a monthly price."
- "Customers can cancel."
THE FOUR-SENTENCE ASK, AND WHAT IT PRODUCES:
ask: "design my database for a subscription app"
-> customers(id, name, email)
plans(id, name, price)
subscriptions(id, customer_id UNIQUE, plan_id, status TEXT)
invoices(id, subscription_id, plan_id) <- amount read from plans.price
three decisions nobody made:
* UNIQUE(customer_id) -> one subscription per customer, forever
* invoice amount derived from plans.price -> last year's invoice
changes the day you raise the price
* status as free text -> 'cancelled' / 'canceled' / 'CANCELLED'
WHAT THE BRIEF LEAVES OUT (the rules you had to go and ask for):
lifecycle trialing -> active -> past_due -> canceled
retention a customer with billing history is never hard-deleted
add-ons sold as separate subscriptions on the same account
billing the charge is the plan price after proration, discount
and tax — so it is not always the plan price
access "invoice history" is read per customer, newest first
A CANDIDATE SCHEMA (entities, cardinality, keys, constraints, gaps):
ENTITIES (one responsibility each)
customer the billable party
plan a purchasable offer and its current list price
<- that "and" is a smell; see the open questions
subscription one customer's ongoing commitment to one plan
invoice one billing event, immutable once issued
RELATIONSHIPS AND CARDINALITY
customer 1-N subscription (add-ons: several held at once)
plan 1-N subscription (a plan is held by many)
subscription 1-N invoice (one per billing period)
KEYS AND CONSTRAINTS (each tied to a rule above, not to a hunch)
subscription.customer_id FK -> customer.id ON DELETE RESTRICT
(retention: a customer with billing history is not deletable)
subscription.plan_id FK -> plan.id ON DELETE RESTRICT
(a discontinued plan still has subscribers pointing at it)
subscription.status CHECK IN ('trialing','active','past_due','canceled')
(the stated lifecycle, closed — not a free-text field)
subscription.canceled_at nullable ts -- cancel is a state, not a DELETE
invoice.amount_charged NOT NULL -- stored, NOT a read of plan.price
invoice.currency NOT NULL
invoice.issued_at NOT NULL
UNIQUE (subscription_id, period_start) -- a period cannot be billed twice
TRADE-OFF, STATED NOT HIDDEN
amount_charged looks like a duplicate of plan.price and isn't.
plan.price is today's list price; amount_charged is what this
customer was actually charged, after proration, discount and tax.
They match on the simple case and diverge the moment billing gets
real — and deriving it would rewrite every past invoice on the
next price change.
OPEN QUESTIONS (named, not guessed)
- is a plan's price one value, or one per currency?
-> decides whether plan splits into plan + plan_price
- does canceling stop billing immediately or at period end?
-> decides whether canceled_at or ends_at drives invoicing
- can a customer hold two active subscriptions to the SAME plan?
-> add-ons say several plans; the same plan twice is undecided
REVIEW RESULT (what the two humans actually changed):
domain owner: "same plan twice is real — that's how people buy a
second seat." -> the last open question closes; no unique index
database engineer: "invoice history is read per customer, and that's
a join through subscription on every page." -> copy customer_id
onto invoice and index (customer_id, issued_at) — the second
deliberate duplication, bought for a stated access pattern
STILL YOUR JOB:
the draft organized the domain and named its own gaps. the two
decisions that actually mattered came from people who know the
business and the queries — neither is in the requirements doc.Where this fits in NewPrompt
Designing the model is one move in a longer sequence, and the AI Database Design Workflow is that sequence: model the entities and relationships, set the constraints that protect integrity, plan indexes around real queries, then document the schema and the migration. This guide covers the first three of those subjects; the workflow carries on into the documentation and migration step this guide stops before. It supplies the prompts and the order, and you run them in your own AI tool with every modeling call staying yours. Three pieces do the concrete work here: the Schema Design & ERD Prompt is the copy-paste prompt that produces an ERD-ready spec — a textual model, not a drawn diagram — and the Markdown Output Builder is the tool that builds that prompt, with the schema sections and the open-questions list already in its shape. When you want the model reasoning about trade-offs and boundaries rather than reaching for a table, the Software Architect Role Prompt is a role prompt aimed at pressure-testing a design before it gets built.
"Schema" is an overloaded word around here, so be explicit about which one this is. An extraction schema defines the fields you pull *out of* documents. A JSON schema defines the shape of a model's *response*. This is neither — it's the entities, relationships, and keys that hold your domain in a database. It also stops short of two neighbours on purpose: writing the migration that gets an existing, populated database from its current shape to a new one is a different problem with different risks, and tuning a slow query against a schema that already exists is a third. Design first, on paper, while changing your mind is free.
If the schema is for a new backend, the Build an API Backend with AI project is the fuller path this stage sits inside — its define-scope stage pins the requirements first, then its design stages run architecture, API contract, data model, and access control before the endpoints exist, and its data-model stage points at the same workflow and prompt this guide uses. The project frames the order; it doesn't write or ship the backend for you. That order is also a live argument worth knowing about: schema-first gives your API something real to sit on, contract-first pins what clients depend on. Both are defensible, both have their advocates, and the answer depends on which one your team will regret getting wrong.
Every modeling decision here carries a price tag, and the only variable is when you read it. The unique constraint nobody asked for costs a five-minute conversation this week, or a migration with downtime in month seven. The invoice amount read from the plan's current price costs one line now, or a year of quietly wrong invoices later. That asymmetry is the whole argument for writing the candidate down and fighting about it while it's still a document — and it's why the most valuable part of a good draft isn't the tables. It's the open-questions list, because that's the part that says out loud what your requirements never decided and who has to decide it. Get those arguments to happen now, on a document, and not in month seven, on a table with data in it.