Database Schema Designer

Why this prompt works

What separates this from a schema-generator is the requirement to justify decisions: ON DELETE behaviour with reasoning, soft delete vs hard delete with reasoning, indexes only on columns used in WHERE/JOIN/ORDER BY. Each of these is a place where an inexperienced schema design produces working but suboptimal SQL; the prompt forces the model to explain its choice, which is also useful for the engineer reading the output and learning from it. The 'prefer UUIDs for security' rule is opinionated and not always right, but it's defensible enough that having it explicit makes the spec easier to debate.

When to reach for it

• You're scoping a new application and need a starting schema before backend work begins.
• You're planning a migration from an existing schema to a refactored one and want a clean target spec.
• You're a frontend engineer who needs to understand what the data model could look like before requesting backend work.
• You're studying database design and want a worked example showing not just the tables but the reasoning behind the choices.

How to customise it

The database engine input is non-trivial. PostgreSQL and MongoDB produce structurally different schemas, and naming the engine wrong gives you output you can't use. The 'application description' field benefits from including the actual access patterns: 'orders need to be queried by user_id and by date range frequently' shapes index choices in a way that 'an order management system' doesn't. For schemas with PII, tell the model to add an 'encryption-at-rest' column note where relevant; the standard template doesn't surface this.

What good output looks like

An ER description (text-based, naming each entity and relationship), then per-table specs in a column-table format, then keys and constraints with justifications, then a list of indexes with reasoning, then a migration SQL file in the correct dependency order, then 3 to 5 example queries. The migration SQL uses CREATE TABLE statements with comments explaining non-obvious choices. UUIDs are used for primary keys; foreign keys reference them. The whole spec runs 1,500 to 3,000 words depending on entity count.

Watch out for

Indexes are the section most worth challenging on the first pass. The model defaults to suggesting indexes on every foreign key column; in practice this is over-indexing for write-heavy applications, and the right answer depends on the read/write ratio of each table. The soft delete vs hard delete recommendation tends toward soft delete by default; if your domain doesn't have a regulatory or audit reason for soft deletes, hard delete is often simpler and the model's reasoning may not apply.