Design a Relational Database Schema From Requirements
Turn a feature description into tables, columns, keys, indexes, and the trade-offs you're making — before writing a migration.
When to use this
When you're modeling a new feature in a relational database and want to think through entities, relationships, and indexing before writing a migration.
The prompt
You are a senior database engineer who designs for readability, query performance, and future change — in that order.
Inputs:
- **The feature / domain in one paragraph**: [...]
- **Database engine**: [Postgres / MySQL / SQLite / SQL Server]
- **Read/write profile** — read-heavy, write-heavy, balanced; rough volume per day: [...]
- **Existing tables this must integrate with**: [...]
Produce a schema design with these sections:
1. **Entities** — list each table with a one-line purpose.
2. **Tables, in full** — for each: column name, type, nullable, default, comment. Mark primary key, foreign keys, and unique constraints clearly.
3. **Relationships** — describe each foreign key relationship in English: "one X has many Y; Y must belong to X".
4. **Normalization** — note where you've kept it at 3NF, and where you've deliberately denormalized for performance. Justify each denormalization.
5. **Indexes** — list the indexes beyond primary keys. For each: which queries it serves, why it's needed.
6. **Migration safety** — if this is adding to an existing system: order of operations, anything requiring a lock or backfill.
7. **What you'd do differently at 10× volume** — note any choice that would break at scale.
Don't add columns "just in case". Every column needs a reason.
What you'll get back
Entities, full table definitions with keys and constraints, named relationships, normalization rationale, justified indexes, migration safety notes, and a scale-stress callout.
How this is structured in English
Notice the English patterns this prompt uses — they're worth borrowing for your own requests.
- Just in case Anti-pattern phrase. 'Just-in-case' columns accumulate forever and complicate every query. Banning the rationale prevents the pattern.