Using an LLM for Database Schema Design, Carefully

Ask a model to design a schema for a booking system and you get seven tables, sensible names, foreign keys in the right direction, and created_at on everything. It looks like the work of a competent engineer, and reviewing it feels unnecessary.

That is exactly the risk. A schema is the one artefact in an application that is expensive to change after it has data in it. Application code gets rewritten every year; a column type that was wrong on day one is still wrong three years later, wrapped in five layers of compensating logic.

Brief it with queries, not with nouns

The usual prompt describes the domain: users, projects, tasks, comments. From a noun list, a model produces a normalised textbook schema, because that is what the training data associates with noun lists.

A schema is not determined by the nouns. It is determined by the reads. Give the model the ten queries the application will actually run, with rough frequencies and latency expectations, and the output changes materially — you get a covering index instead of a generic one, a denormalised counter where a count would be run on every page load, and a partial index on the status value that 95 percent of queries filter by.

Write the queries in plain language if you have not written the application yet. "Load a project page: the project, its 50 most recent tasks, each task's assignee name and comment count, ordered by due date" is enough. The point is that the model is now designing against access patterns rather than against a vocabulary.

Include the things that grow. "Tasks will reach roughly 50 million rows; comments will reach 400 million; users will stay under 100,000" changes the answer more than any other single line in the prompt, because it decides which joins are affordable.

What models are genuinely good at here

Three tasks come back reliably good. First, critiquing an existing schema you paste in: missing indexes on foreign keys, columns that should be NOT NULL and are not, text columns storing enumerations, timestamps without time zones. This is pattern recognition against a very large corpus of schemas and it works.

Second, naming and consistency. If half your tables use user_id and half use owner, a model will notice every instance and propose a consistent scheme, which is tedious and error-prone for a person to do across forty tables.

Third, generating the boring mass of a schema once the shape is decided. Lookup tables, join tables, audit columns, and the accompanying DDL are mechanical, and reviewing mechanical output is fast because you are checking a pattern rather than a judgement.

What it gets wrong, consistently

Cardinality is the big one. Models default to the relationship that is most common in training data rather than the one your domain has. An address becomes one-per-user when your business has customers with fifty delivery sites; a payment becomes one-per-order when partial payments and refunds exist. Ask explicitly, for each relationship, whether the "one" side is genuinely always one, and expect to correct roughly a third of them.

Uniqueness is the second. A model will happily add a unique constraint on email, which is right until you support soft-deleted accounts and someone re-registers. The generated schema and the generated soft-delete column are individually reasonable and jointly broken. This class of error — two locally correct decisions that contradict each other — is the one to watch for, and it is closely related to the mechanics described in why LLMs hallucinate: the model is producing locally probable output, not globally consistent output.

Time is the third. Expect naive timestamps, storage of local times without a zone, and no distinction between the time an event happened and the time it was recorded. If your domain has any scheduling in it, state the requirement explicitly and check every temporal column by hand.

Make it justify every constraint

The single most valuable prompt instruction is to require a one-line justification for each constraint, index and type choice, written as a comment in the DDL.

The justifications are useful documentation, but the real benefit is diagnostic. A constraint the model cannot justify is one it added by reflex, and those are where the wrong cardinality lives. When you read "unique(user_id, date) — a user can only have one entry per day" you can immediately test that claim against the domain, whereas the bare constraint slides past.

Ask for the counter-case too: for each unique constraint, one concrete scenario that would violate it. Half the time the model produces a scenario your product actually supports, and you have found the bug before the migration ran. This is a cheap form of the review gate discussed in human-in-the-loop design — the model does the enumeration, a person does the judgement.

Migrations are a different, more dangerous task

Designing a schema on a blank page is low risk. Writing a migration against a table with 80 million rows in it is not, and the model has no idea which of those two situations it is in unless you say so.

Always state the row count and whether the table is under live write traffic. Otherwise you will get an ALTER that rewrites the whole table, or an index creation without a concurrent flag, and it will lock production for the duration.

Require every migration to be expand-and-contract: add the new column nullable, backfill in batches, switch the reads, then drop the old column in a separate later migration. Models will collapse this into one step unless told not to, because the one-step version is what appears in tutorials.

Never let generated DDL reach a database without a human running it against a restored snapshot first. A schema change is the one category where a wrong answer cannot be rolled back by reverting a deploy, and the same reasoning that makes generated SQL a read-only affair by default applies with more force to DDL.

Keep the loop tight and mechanical

The workflow that works is not one long conversation. It is: state access patterns and volumes, get DDL with justifications, run it against a throwaway database, run the ten queries with EXPLAIN, paste the plans back, and iterate on the ones that scan.

That loop grounds the model in something real. A sequential scan in a query plan is a fact, and feeding facts back is far more productive than asking a model to reason about performance in the abstract, where it will confidently recommend indexes that the planner would never choose.

Ask for the output in a fixed shape so this can be scripted — one DDL statement per block, one migration file per change. If you are wiring this into a tool rather than a chat window, the constraints in structured outputs and JSON mode save a lot of parsing.

The decision rule

Use a model to critique a schema you wrote, to make an existing schema consistent, and to generate the mechanical bulk once you have decided the shape. Use it to enumerate the constraints you might have missed and the scenarios that violate them.

Do not use it to decide cardinality, uniqueness or temporal semantics — those come from the domain, and the model does not have your domain. And never let it write a migration against a large live table without a person reading every line and testing it on a restored snapshot.

A useful test before accepting any generated schema: can you name the query each index serves? If you cannot, the index was decoration, and so was the review that let it through.