ChatGPT Prompts for Database Schemas: Design Fast, Scale Later
ChatGPT prompts for database schema design. Entity relationships, normalization, index strategy, and migration plans for PostgreSQL, MySQL, and NoSQL.
The Prompt
Act as a database architect who has designed schemas for applications processing 100M+ transactions per day.
Design a database schema for:
Application: {describe the application and its core entities}
Database type: {PostgreSQL / MySQL / MongoDB / DynamoDB / SQLite}
Scale requirements: {expected rows per table at 12 months, read/write ratio}
Key queries: {the 5 most frequent queries this schema needs to support efficiently}
Constraints: {GDPR data deletion requirements / multi-tenancy / soft deletes / audit trail}
Migration from: {existing schema if applicable — or "greenfield"}
Output:
1. Entity-relationship description (plain English before the DDL — what tables exist and why)
2. CREATE TABLE statements (with all constraints, foreign keys, and check constraints)
3. Index strategy:
- Required indexes (for the key queries provided)
- Composite indexes with column order rationale
4. Decisions log (for each non-obvious design decision: what you chose and why, with the alternative considered)
5. Migration plan (if migrating from existing schema — ordered steps with rollback option at each step)
6. Query examples (the 5 key queries provided — written for this schema, with EXPLAIN ANALYZE notes)
Constraints:
- Soft deletes must use deleted_at timestamp, not a boolean flag
- All tables must have created_at and updated_at columns
- UUID vs auto-increment IDs: recommend based on the scale requirements
- Avoid EAV (Entity-Attribute-Value) patterns unless explicitly needed
- Foreign key constraints must be explicit — no implicit relationships
Variables to fill in
-
{application}Description of the application and its core entities -
{database type}PostgreSQL, MySQL, MongoDB, DynamoDB, or SQLite -
{key queries}The 5 most frequent queries this schema must support efficiently -
{scale requirements}Expected rows per table and read/write ratio -
{constraints}GDPR, multi-tenancy, soft deletes, audit trail requirements
How to use this prompt
- Define your key queries before running — schema design is query-first, not entity-first
- Use the decisions log as your architecture decision record (ADR) for future team members
- Run the migration plan one step at a time with rollback testing at each step
- Review the index strategy with your DBA before deploying to production
Schema design is query design
The best database schema is the one that makes your most frequent queries fast. Normalizing a schema perfectly but failing to account for your access patterns is a common mistake — you end up with beautiful schema that requires 7 JOINs to answer your most common question. The prompt requires the 5 key queries up front so the schema is designed around how data is actually accessed.
The decisions log is your architecture ADR
Every non-obvious design decision — why UUID instead of auto-increment, why denormalize this particular table, why a polymorphic association instead of multiple tables — should be documented while you still remember the reasoning. The decisions log in this prompt’s output is your schema’s architecture decision record, written while context is fresh.
Migrations must be reversible
A migration that adds a column is reversible. A migration that drops a column or changes a data type is not — unless you plan for it. The prompt’s migration plan output includes rollback options at each step, because a migration that breaks production with no rollback path is the most stressful production incident there is.
Related free tools
- Free AI Prompt Generator — build structured prompts instantly
- AI Token Counter — estimate API costs before long runs
- AI ROI Calculator — measure AI’s impact on developer productivity