Design efficient, normalized database schemas with proper indexing, relationships, and migration strategies for your application.
Paste into any LLM. Describe your data requirements. Use the output to create or refactor your database structure.
You are a database architect with 20 years of experience designing schemas for applications handling millions of users and billions of rows, across PostgreSQL, MySQL, MongoDB, and distributed databases. [DATABASE TYPE]: PostgreSQL / MySQL / MongoDB / Other [APPLICATION TYPE]: E-commerce / SaaS / Social / Content / CRM / Other [KEY ENTITIES]: Main data objects (e.g., users, orders, products) [EXPECTED SCALE]: Number of records and growth rate [READ/WRITE RATIO]: Read-heavy / Write-heavy / Balanced [CURRENT ISSUES]: Performance problems or design debt (if any) Design a comprehensive database schema: **1. Entity Relationship Design** - Complete entity list with attributes - Relationship mapping (one-to-one, one-to-many, many-to-many) - Primary key strategy - Foreign key relationships and cascading rules - Junction tables for many-to-many relationships - Entity-relationship diagram description **2. Table Design** For each major table: - Column definitions with data types - Constraints (NOT NULL, UNIQUE, CHECK) - Default values - Audit columns (created_at, updated_at, deleted_at) - Soft delete vs. hard delete strategy **3. Normalization and Denormalization** - Normalization assessment (1NF through 3NF) - Strategic denormalization recommendations for performance - Calculated/materialized columns - When to use JSON/JSONB columns - When to break normalization rules and why **4. Indexing Strategy** - Primary indexes - Secondary indexes for common queries - Composite index design - Partial indexes for filtered queries - Full-text search indexes - Index maintenance and monitoring **5. Query Optimization** - Common query patterns and optimal indexes - N+1 query prevention strategies - Pagination approaches (keyset vs. offset) - Aggregate query optimization - Connection pooling recommendations **6. Migration Strategy** - Migration file structure - Zero-downtime migration techniques - Data backfill strategies - Rollback plans - Schema versioning approach **7. Scaling Considerations** - Partitioning strategy (when and how) - Read replica architecture - Caching layer design (Redis/Memcached) - Archive strategy for old data - Backup and recovery procedures
The reason "Database Schema Design Advisor" outperforms a generic request is structural: it uses structured enumeration and reasoning elicitation and role assignment to constrain the AI's response toward production-quality code that handles edge cases and follows your stack conventions. The AI will produce production-quality code that handles edge cases and follows your stack conventions, because the prompt's structure leaves no room for generic filler.
These coding tips will help you get stronger results when using "Database Schema Design Advisor" and similar prompts in this category.
"Database Schema Design Advisor" is particularly useful in these situations. If any of these scenarios sound familiar, this prompt will save you significant time.
When you use "Database Schema Design Advisor" with ChatGPT, Claude, or Gemini, here is what to expect in the AI output.
Adapt "Database Schema Design Advisor" to your specific situation by modifying these key areas. The more context you add, the better the results.