Prompting for Database Schema Design

TL;DR: Database schema prompts fail quietly when they skip three things a plain description leaves out: which SQL dialect you're targeting, how the data will actually be queried, and what happens before generated DDL touches real data. State your dialect, supply real query patterns for indexing, and never run a generated migration against production without reading it and testing it against a copy first.

Why does the SQL dialect you're targeting change what a good prompt looks like?

State your database engine and, ideally, its major version in the very first line of any schema prompt. Skip it, and a model will pick a dialect for you, usually whichever shows up most in its training data, and hand you syntax that's wrong for the engine you're actually running.

The differences aren't cosmetic. An auto-incrementing primary key is AUTO_INCREMENT in MySQL, GENERATED ALWAYS AS IDENTITY (or the older SERIAL shorthand) in PostgreSQL, IDENTITY(1,1) in SQL Server, and an implicit behavior of INTEGER PRIMARY KEY in SQLite. Paste PostgreSQL syntax into a MySQL migration tool and it fails outright; paste it into a SQL Server one and it fails differently. The same gap shows up in how each engine quotes identifiers, how you paginate a result set, and whether native JSON storage exists at all.

Treat this table as a reason to check your specific engine and version before you ship anything it produces, not as the final word: engines change, and a syntax detail that's accurate today can move in a future release. The one instruction worth putting in every schema prompt, regardless of dialect, is to name the engine and version explicitly and to ask the model to flag anywhere its answer depends on a dialect-specific feature, so you know exactly what to double-check.

This matters even when you're the only one who'll ever read the prompt. It's tempting to assume "it's just SQL" and skip stating the obvious, but a model has no way to infer your engine from a plain description of entities and relationships, and the syntax it defaults to when unstated is not necessarily the one you're running. Our broader developer prompt set covers this same discipline, stating the runtime and version explicitly, across other coding tasks; schema design is just the case where getting it wrong is hardest to spot at a glance, because invalid SQL for the wrong dialect can still look entirely plausible sitting in a chat window.

How do you prompt for normalization without over- or under-doing it?

Ask for a normalized starting point, then decide on purpose where to deviate from it, rather than asking for "a good schema" and hoping the model picks the right level on its own. Normalization (structuring data so each fact lives in exactly one place, described classically through 1NF, 2NF, and 3NF) is close to the right default for transactional data: the tables your application writes to constantly, where an update should only need to touch one row.

It's often the wrong default for reporting tables, dashboards, and analytics workloads, where a heavily normalized schema means every read requires several joins across tables that rarely change. If a table exists mainly to be read, and read often, in a shape that's known in advance, a deliberately denormalized or pre-aggregated structure is frequently the better engineering call, not a shortcut. The failure mode to watch for is a model defaulting to strict normalization everywhere because it's the textbook-correct answer in isolation, without knowing that a specific table in your system is a read-heavy reporting surface where that correctness costs you real query performance. Say which kind of table you're designing, and why, before asking for its structure.

Two prompts asking about the same entity can reasonably produce two different, both-correct schemas once you supply that context. A customers table backing a transactional checkout flow wants one customer's data in one place, updated in one transaction, so a purchase never leaves the record half-updated. A customers table feeding a nightly analytics dashboard might reasonably duplicate a customer's current tier or region directly onto every order row it's joined against, trading a small amount of update complexity for dashboard queries that don't have to join three tables every time someone opens a report. Neither shape is more "correct" in the abstract. The model can only pick the right one for your case if your prompt says which job the table is actually doing.

How do you prompt for indexes that actually help?

An index only helps the queries it was built for, and a model has no way to know your query patterns unless you supply them. Asked generically for "good indexes," you'll typically get indexes on primary and foreign keys, which most schemas already have by default and which don't address your actual slow queries.

Give it what it actually needs instead: the queries you run most often (or plan to), specifically the columns that show up in WHERE, JOIN, and ORDER BY clauses, along with a rough sense of table size and how often the data changes. An index that speeds up reads also slows down writes, since every insert or update has to maintain it, so index selection is a genuine tradeoff that depends on your table's read-to-write ratio, not just its column list. A schema-design prompt that never mentions how the data gets queried can only produce structurally reasonable defaults, not indexes tuned to your actual workload.

Ask for the reasoning behind each proposed index, not just the CREATE INDEX statement itself. "Index on orders.customer_id and orders.created_at together, because your stated query filters by customer and sorts by date" is something you can check against your own knowledge of the system and either confirm or push back on. A bare list of index statements with no stated reason gives you nothing to evaluate except whether the syntax is valid, which is the least important thing to get right about an index. And be explicit about scale before asking: an index that's clearly worth the write overhead on a hundred-million-row table can be pure overhead on a lookup table with two hundred rows that changes twice a year, and the model has no way to know which situation it's in unless your prompt says so.

Is it worth prompting for relationships before generating the actual DDL?

Yes, as a cheap review step, before you're reading through columns and constraints. Ask for the entities, their relationships, and the cardinality of each relationship in plain language, or a simple text-based diagram, before asking for table definitions. This surfaces the mistakes that are expensive to notice later while they're still a single sentence to fix: a relationship described as one-to-many that's actually many-to-many and needs a junction table, an entity that's really two separate concepts sharing one table, a "belongs to" that only holds true most of the time.

A wrong cardinality caught at the relationship-description stage is a sentence you correct before anything else exists. The same mistake caught after the schema, the migration, and a chunk of application code all assume the wrong relationship is a real refactor. This is closely related to how we'd approach prompting for tables and structured data generally: get the shape agreed before you commit detail to it, because detail is expensive to move once other things depend on it.

Why should you never run generated DDL blind?

Because several ordinary-looking schema changes are destructive or hard to reverse, and a generated migration reads exactly as confidently whether it's safe or not. DROP COLUMN deletes that column's data the moment it runs, with no built-in undo. Renaming a column or table can silently break application code still expecting the old name, and that failure often shows up somewhere far from the migration itself. Altering a large table can lock it long enough to cause real downtime, and how long depends on your engine, its version, and the table's size in ways a generated script has no way to know about your specific database.

This matters more, not less, the more fluent and specific the generated migration looks. A script with clean formatting, sensible-looking column names, and no obvious typos reads as trustworthy, but none of that tells you whether the model correctly reasoned about your actual data, your table's current size, or which of your services still queries the column it just renamed. Review a generated migration the same deliberate way you'd review any other generated code change before merging it; our guide to prompting for a genuinely useful code review applies here directly, since a migration is code that happens to be able to delete data, not a special category exempt from the same scrutiny.

A worked example: prompting for a schema with the details that matter

Role: Database schema designer for a specific engine, not a generic answer.

Context
- Database: PostgreSQL 17
- Application: [one-sentence description of what the app does]
- Entities and relationships: [e.g. "a customer has many orders; an order
  has many line items; a line item references one product"]
- Fields that must be unique or required: [list them]
- Expected scale: [rough row counts and growth, e.g. "orders table growing
  ~50k rows/month"]
- Most common queries: [e.g. "look up a customer's orders by date range",
  "find all line items for a product across all orders"]

Task
Propose a normalized schema (tables, columns, types, primary/foreign keys)
for PostgreSQL 17 specifically.

Rules
- Use PostgreSQL syntax throughout; flag anywhere a choice depends on a
  PostgreSQL-specific feature that wouldn't port to another engine.
- Propose indexes only for the query patterns listed above, and explain
  which query each proposed index is for.
- Flag any place a strictly normalized structure would hurt one of the
  listed query patterns, and suggest the deliberate deviation if there is one.
- Do not include destructive statements (DROP, TRUNCATE) in this response;
  this is schema design, not a migration.

Output
Table definitions with types and constraints · proposed indexes with the
query each one serves · anything flagged for dialect-specificity or
normalization tradeoffs.

Every field in that context block is doing real work: the engine name prevents dialect drift, the query patterns turn "good indexes" into indexes actually built for your access patterns, and the scale figure gives the model a reason to think about whether a design that's fine at a thousand rows still holds at fifty million. None of this is unusual rigor for a schema-design prompt. It's closer to the minimum information a competent engineer would ask for before designing the same schema themselves.

If what you actually need out of this step is a machine-readable description of the schema, for a codegen tool, a review script, or documentation, rather than raw DDL first, ask for it as structured JSON instead and generate the dialect-specific SQL from that as a second step; our JSON prompt generator has templates for exactly that kind of structured extraction. Keeping the schema description and the dialect-specific SQL as two separate outputs also makes the dialect table above easier to apply consistently: you only have to re-target one small conversion step per engine, not regenerate the whole design from scratch for each one.