Back to prompts
Coding & DevelopmentPremiumadvanced
4.8

Database Schema Designer

Design a normalized, scalable database schema from a project description — tables, relationships, indexes, and queries.

Copy & Paste this prompt
You are a database architect with deep expertise in relational and NoSQL databases. Design a complete database schema for my project.

Project description: [WHAT YOUR APP DOES]
Database: [PostgreSQL / MySQL / MongoDB / SQLite / OTHER]
Expected scale: [THOUSANDS / MILLIONS / BILLIONS of records]
Read vs Write ratio: [READ-HEAVY / WRITE-HEAVY / BALANCED]
Key queries: [MOST COMMON QUERIES THE APP WILL RUN]

Design:

1. **ENTITY ANALYSIS**
   - Identify all entities from the project description
   - Define relationships (1:1, 1:N, N:N)
   - Entity-Relationship diagram (text-based)

2. **SCHEMA** (for each table/collection)
   - Table name, columns, data types, constraints
   - Primary keys, foreign keys, unique constraints
   - Default values and NOT NULL decisions
   - Normalization level with justification

3. **INDEXES**
   - Which columns to index and why
   - Composite indexes for common query patterns
   - When NOT to index
   - Estimated impact on read vs write performance

4. **QUERIES**
   - SQL/queries for the 5 most common operations
   - Optimized versions with EXPLAIN analysis notes
   - Pagination strategy
   - Full-text search approach (if needed)

5. **MIGRATION PLAN**
   - CREATE TABLE statements (ready to run)
   - Seed data script
   - Future-proofing: columns you might need later
   - Scaling considerations

Output as executable SQL/schema files I can run directly.
#database#schema-design#sql#postgresql#data-modeling

Works with

chatgptclaudegemini

💡 Pro Tips

  • •Include your most common queries — they drive index decisions
  • •Start normalized, denormalize only when performance requires it
  • •Always add created_at and updated_at timestamps to every table

🧠 Why This Works

A well-normalized schema prevents data anomalies and scales gracefully. This prompt applies database normalization rules, index strategy, and relationship modeling to produce schemas that are both performant and maintainable from day one.

📅 When to Use This Prompt

Use when starting a new project and need to design your database from requirements, when refactoring a denormalized schema that's causing data integrity issues, or when you need to add new features to an existing database and want to get relationships right.

🎯 What You'll Get

You'll receive a complete schema design with table definitions, column types, primary/foreign keys, indexes, constraints, and an entity-relationship diagram—plus migration SQL ready to execute and notes on scaling considerations.

🔗 Related Prompts

Coding & DevelopmentPremium

System Design Interview Simulator

Practice system design interviews with a realistic AI interviewer — get architecture feedback, follow-up questions, and…

system-designinterviewarchitecture
★4.9
advanced