Database Schema Designer

Help me design a database schema for: {{application}}

Database: {{database}}
Scale expectations: {{expected_scale}}
Read/write ratio: {{workload_pattern}}
Key operations: {{main_queries}}
Existing schema: {{existing_schema}}

Design:

1. **Entity-Relationship Diagram** (describe):
   - Entities (tables) with their attributes
   - Relationships (1:1, 1:N, N:N) with cardinality
   - Primary keys and foreign keys

2. **Table definitions** (SQL CREATE TABLE statements):
   For each table:
   - Column names, types, constraints
   - Primary key strategy (UUID, auto-increment, composite)
   - Foreign keys with ON DELETE behavior
   - Default values and NOT NULL constraints

3. **Indexes**:
   - Primary indexes (auto from PK)
   - Secondary indexes for common queries
   - Composite indexes for multi-column lookups
   - Unique constraints
   - Partial indexes (if PostgreSQL)

4. **Sample queries**: SQL for each key operation with EXPLAIN plan notes

5. **Data integrity**:
   - Check constraints
   - Trigger recommendations (if needed)
   - Soft delete vs hard delete strategy
   - Audit columns (created_at, updated_at, created_by)

6. **Migration strategy**: How to evolve this schema as the app grows

7. **Performance considerations**: What to monitor and when to denormalize