How to Prompt AI to Design a Database Schema

To prompt AI to design a database schema well, describe the questions your app will ask the database and how each record changes over its life. Give it a list of entities instead and you will get a textbook schema: correct, generic, and wrong for your access patterns. The difference between those two prompts is about eight lines of input and roughly a month of avoided migrations.

Here is the input format that works, a full worked prompt, and the four mistakes to check for every time.

Why entity lists produce mediocre schemas

Ask for a schema for 'a booking app with customers, staff, services and appointments' and the model has enough to produce four tables with sensible foreign keys. It has nothing to decide the things that actually matter.

Can two appointments share a slot? Does a price live on the service or on the appointment, and what happens to old bookings when the price changes? Is a cancelled appointment deleted, flagged, or moved? Does staff availability repeat weekly, or is it set per day? Every one of those is a schema decision, none of them is inferable from an entity list, and each wrong guess is a migration later.

So supply them. Not as prose, as two specific lists.

The input format: queries and lifecycles

Queries are the questions your app asks, in plain language, in rough order of frequency. They tell the model what has to be fast, what needs an index, and where denormalising earns its keep.

Lifecycles are what happens to a record from creation to disappearance. They tell the model where you need status fields, historical copies, and timestamps, which is where generic schemas fail hardest.

Design a PostgreSQL schema for a booking app for small salons.

The queries the app runs, most frequent first:
1. All appointments for one salon on one day, with customer and staff names
2. Free slots for a given service and staff member over the next 14 days
3. One customer's full appointment history, newest first
4. Revenue by service for a month
5. Which staff members can perform a given service

Record lifecycles:
- An appointment is booked, may be rescheduled any number of times,
  and ends as completed, cancelled or no-show. We must keep cancelled
  and no-show appointments for reporting.
- Service prices change. Past appointments must keep the price that
  was charged at the time.
- Staff leave. Their past appointments stay visible and attributed.
- Customers can request deletion of their personal data while we keep
  anonymised revenue history.

Constraints:
- One salon per row of data, multi-tenant, all queries scoped by salon
- Expect 200 salons, ~50k appointments per year total
- Double-booking a staff member must be impossible at the database level

Give me the DDL with primary keys, foreign keys, unique and check
constraints, and indexes justified against the query list above.
For any decision where you had to guess, list the guess and the
alternative you rejected.

Four things in that prompt do the heavy lifting. The ordered query list drives indexing. The price sentence forces a price snapshot on the appointment rather than a join to a mutable service row. The deletion sentence forces personal data into a shape that can be nulled without destroying the revenue rows. And the double-booking line pushes the rule into a database constraint instead of application code, which is the difference between a rule and a hope.

The last instruction matters just as much. Asking the model to surface its guesses turns a confident artefact into a reviewable one, which is the same idea as getting AI to ask clarifying questions before it commits to an answer.

Four mistakes to check for every time

These recur across models and across prompts. Check them in this order, because the first two are cheap to fix now and expensive to fix later.

Business rules left in application code. The model will describe a rule in a comment rather than enforce it. Anything that must never happen belongs in a constraint. PostgreSQL's constraint documentation covers the options, and exclusion constraints in particular are the right tool for the no-overlapping-appointments case that most generated schemas try to solve with a check in code.

Free text where an enum belongs. Status columns come back as plain text, and six months later production holds 'cancelled', 'Cancelled' and 'canceled'. Ask for an enum type or a check constraint listing the permitted values.

Missing uniqueness. Models add primary keys reliably and natural uniqueness rarely. One email per customer per salon, one slug per workspace, one active subscription per account: state each one and require a unique index.

Over-normalising the things you never query separately. A separate address table for one address per customer is a join you will pay for on every screen. Normalisation is a default, not a virtue, and the query list is what tells you where to break it.

Review the schema against its own queries

The fastest review is not reading the DDL. It is making the model prove the schema answers the questions you gave it.

For each of the 5 queries above, write the actual SQL against this
schema. Then tell me, for each one:
  - which index it uses
  - whether it needs a sequential scan on any table
  - what it costs at 10x my stated volume

Then list every rule from my lifecycle section that is NOT enforced
by a database constraint, and say why not.

Ugly SQL for a common query means the schema is wrong for your access pattern, and now is the cheapest moment to find out. That second list is the one to read closely: everything on it is a rule your application has to remember forever. Writing those queries out is a skill of its own, covered in prompting AI to write SQL queries.

Decide the engine before you prompt, not after

Naming the database in the prompt changes the output substantially, because the useful features are not portable. Exclusion constraints, partial indexes, JSON columns and array types all differ, and a schema written for 'a SQL database' generically gets none of them. Pick first, using something like how to choose a database for an AI built app, then prompt.

And treat the first schema as a draft you will amend. Getting the migration workflow right early is what makes that cheap, which is the subject of AI coding agents and database migrations.

Frequently asked questions

Should I ask for the whole schema at once or table by table?

All at once for the first draft. Relationships are the hard part and a model reasoning about all the tables together produces more coherent foreign keys than one that meets each table in isolation. Iterate table by table afterwards, once the shape is agreed.

How do I stop it inventing columns I did not ask for?

Add a line requiring every column to trace to a query or a lifecycle rule you supplied, and to be listed separately if it does not. Speculative columns are not always wrong, but they should be visible rather than blended into the DDL.

Can I give it my existing schema and ask for changes?

Yes, and it works better than a fresh design, because your current schema already encodes decisions nobody wrote down. Paste the DDL, state what is changing, and ask explicitly for a migration path with the order of operations. Ask what breaks if the migration is interrupted halfway, since that is the question that separates a usable plan from a risky one.

Is a generated schema good enough for production?

For a straightforward application with well-stated requirements, frequently yes, once you have run the four checks above and the query review. What it is not is a substitute for knowing your own access patterns, because that is the input, not the output. The wider technique of structuring prompts this way is covered in the prompt engineering pillar.