AI Prompts Library

You are an expert database architect. Design a normalized, performant database schema based on the application requirements below.

**Application:** [DESCRIBE_YOUR_APPLICATION]
**Database Engine:** [PostgreSQL/MySQL/SQLite/MongoDB]
**Expected Scale:** [Number of users, records, transactions per day]
**Key Requirements:** [LIST_MAIN_FEATURES]

**Your schema design must include:**

1. **Entity-Relationship Diagram** (describe in text format):
   - All entities with their attributes and types
   - Primary keys, foreign keys, and unique constraints
   - Relationship types (1:1, 1:N, M:N) with cardinality
   - Junction tables for M:N relationships

2. **Table Definitions** (SQL CREATE statements):
   - Proper data types (VARCHAR lengths, DECIMAL precision, ENUM values)
   - NOT NULL constraints where appropriate
   - DEFAULT values (timestamps, UUIDs, boolean flags)
   - CHECK constraints for data validation
   - Proper indexing strategy (B-tree, GIN, GiST where appropriate)
   - Composite indexes for common query patterns
   - Partial indexes for filtered queries

3. **Normalization Analysis**:
   - Confirm the schema is at least in 3NF
   - Document any intentional denormalization with justification
   - Identify potential data anomalies and how they are prevented

4. **Performance Considerations**:
   - Index recommendations based on expected query patterns
   - Partitioning strategy if tables will exceed 10M rows
   - Materialized views for complex reporting queries
   - Connection pooling and caching recommendations

5. **Migration Script**: A versioned migration script (using a tool like Alembic, Flyway, or Prisma) that creates the schema from scratch.

6. **Seed Data**: Sample INSERT statements with realistic test data for development.

7. **Common Queries**: Write the 5 most common queries the application will need, with EXPLAIN analysis notes.