AI Prompts for Database Schemas: The Column That Stores Two Facts

AI prompts for database schemas are useful right up to the moment you trust them. One sentence goes in, a full set of tables comes out, and it looks finished.

That is the trap: the output is clean and semantically thin, because it repeats the form you described instead of the business underneath. Six months later someone asks a question the form never had a field for. Here are the three failures that follow, and the prompts that do the drafting.

AI Prompts for Database Schemas: The Sentence Is Not the Model

A schema is not a list of columns. It is a set of sentences about what one row means, and a model only writes the sentences you said out loud.

The Model Receives a Form, Not a Business

What you paste into the prompt is a functional description: "I want to record orders with a customer, some items and their payments." The model answers with the cheapest structure that satisfies it - one table per named thing, plus a status column for whatever changes. Nothing is broken. It just does not know your future questions, so it optimizes for the form on your screen.

The One-Line Test: A Row Is...

Before reading any output, write this by hand: a row of this table is ___. If the sentence needs an "and" ("a row is an order and the payment made against it"), you have two tables. If it needs a "sometimes", there is a hidden entity. If you cannot finish the sentence, the model cannot either, and it will finish it without saying which version it chose.

Failure One: The Column That Stores Two Facts

It is the most common defect in generated designs and the easiest to miss, because the column has a reasonable name.

paid_partial Is Not a Status

Ask for order states and you get a closed list: pending, paid, paid_partial, refunded, refunded_partial, cancelled_after_payment. Only the first value describes where the document sits in the process. The rest describe how much money came in and how much went back out, which is an amount and a date, not a label. A status column holding both answers two questions at once and answers neither reliably. To report "collected in October" you then list the counting values by hand inside a query.

Split the Thing From the Event

Keep the entity (the order) in one table and the event (a payment row with amount, date, method and reference) in another, then derive the state when you read it. Two tables and a join, instead of a status field that quietly turns into a report.

Failure Two: The Constraint Nobody Wrote

The only part of your design the system enforces is the part you declared: primary key, unique, foreign key, not null, check. Everything else is a convention, and a convention survives exactly as long as every writer respects it.

Duplicates Become the Truth

Without a unique key on the natural identifier (the supplier invoice number, the normalized email, the customer-and-period pair), the second CSV import inserts a second row and both rows are now the truth. Every count after that is a choice. Pick the key by finding the question that cannot have two answers.

The Foreign Key Is a Promise

A relationship without a reference lets rows point at an id that no longer exists. The join does not fail, it silently omits, and the report comes out smaller with no warning. With the foreign key the delete fails on purpose, and that error is the feature: one fact, one place, defended by the engine (Wikipedia).

Failure Three: The Type That Rounds

Money Is Not a Float

PostgreSQL documents that numeric stores numbers with a very large number of digits and recommends it for monetary amounts where exactness is required (PostgreSQL 15 docs). The reason sits on the other side: most decimal fractions cannot be represented exactly as binary fractions, so the decimal you type is only approximated by what gets stored (Python docs). Two totals that should match come out a cent apart. Store money in an exact type, or in integers in the smallest unit.

A Time With No Zone

A timestamp without time zone saves a local hour without recording which locality. Add a second timezone or a daylight saving change and ordering and subtracting stop working. Store in UTC and present in the reader's zone. The same logic kills the free-text boolean: a field for yes and no holds yes, Yes, Y, 1, true and empty. The type is what closes the list.

The Value List That Will Grow

Every generated schema ships a closed list: an enum, a check, or a varchar with a comment. The question is who decides when the third value appears: a closed list means a migration per value, a lookup table means a row you administer, and free text with a hope is the model's default and not an option.

Three Catalog Prompts, Three Different Jobs

The Prompt That Designs the Model

The SQL Query and Schema Builder prompt ($2.99) turns a plain-language description into schema design, queries, indexes and performance notes. Ask for the schema and the "a row is..." sentence for each table in the same pass, then ask where its own design would allow two rows for one fact. It cannot know your reports or your natural key.

The Prompt That Reads the Model Aloud

The REST API Design System prompt ($4.99) starts from a feature list and returns resources, endpoints, status codes, authentication and an OpenAPI spec. Use it as a second opinion, because an API is the schema read out loud. If you cannot name the resource as a plural noun your customer recognizes, the table is not an entity: it is an event wearing a table's clothes.

The Prompt Where the Constraint Gets Written

The Laravel Feature Builder prompt ($4.99) builds a feature in order, starting with the migration (indexes, foreign keys, defaults), then the model with casts and relations, validation and Pest tests. The migration is the only place where a constraint is actually written. If the output validates only in the form, the second writer walks past it.

The Model Card: Five Lines Before the First Migration

Fill this in by hand, per table, before you ask for the migration.

1. The row is: one sentence, no "and" - what a single row represents.
2. The key is: the natural identifier that must never repeat, and why two rows with it would be a contradiction.
3. One fact per column: for every column, the question it answers - and the column that answers two gets split.
4. The values that must never exist: the closed lists, the negative amounts, the date that cannot be in the future, the row that cannot exist without its parent.
5. The question I will ask in a year: the report you already know you will need, written down now so the schema can answer it.

If line five cannot be written, you are not designing a schema, you are storing a form. If line two can be satisfied by two different rows, there is no key: there is an auto-increment id covering the problem.

Three Tests on the Generated Schema

1. The sentence. Read "a row is..." aloud, once per table. Every "and" is a missing table or a column that should not be there.
2. The twin. Try to write two legitimate rows by hand that represent the same fact. If you can, there is no constraint and the database will accept the lie.
3. The year. Ask line five as a query. If you have to guess which status values count, the status was carrying two facts.

One honest limit: the model does not see your data or your business. The natural key, the money type and the value list are your decisions. Do not paste production data into a prompt; a column list and two invented rows are enough. None of this is legal or accounting advice.

FAQ

Why does the schema ChatGPT gives me look right and fail later?

It was built from a screen description. It stores what the form asks for, not the question that appears six months later.

Is one status column always wrong?

No. It works when the status is one fact. It fails when the label carries an amount or a second fact.

Should money be a float or a decimal?

An exact type, or integers in the smallest unit. Floating point approximates most decimal fractions.

Do I need a migration for every new value?

Only if the list is closed by the business. Decide that before the first migration.

Write "a row is..." for your three most important tables. The one that needs an "and" is where next quarter's bug lives. To draft faster, bring those lines to the schema builder prompt.