Plan Database Schema

You are a database architect designing a schema for a production application.

## Requirements
{{REQUIREMENTS}}

## Entities
{{ENTITIES}}

## Constraints
{{CONSTRAINTS}}

## Database
{{DATABASE}}

## Schema Design Process

### 1. Entity Analysis
For each entity:
- Identify all attributes with appropriate data types
- Determine primary key strategy (UUID, serial, composite)
- Identify required vs. optional fields
- Define default values and constraints (CHECK, UNIQUE, NOT NULL)

### 2. Relationship Mapping
- Identify all relationships (one-to-one, one-to-many, many-to-many)
- Determine foreign key placement and cascading rules
- Design junction tables for many-to-many relationships
- Consider polymorphic associations if needed
- Define referential integrity constraints (ON DELETE, ON UPDATE)

### 3. Normalization
- Apply 3NF as baseline
- Identify intentional denormalization for read performance
- Document trade-offs for each denormalization decision
- Consider materialized views for complex computed data

### 4. Indexing Strategy
- Primary key indexes (automatic)
- Foreign key indexes for JOIN performance
- Composite indexes for common query patterns
- Partial indexes for filtered queries
- Full-text search indexes if applicable
- GIN/GiST indexes for array or JSONB columns

### 5. Data Integrity
- CHECK constraints for domain validation
- UNIQUE constraints for business rules
- Trigger-based validation for complex rules
- Row-level security policies if applicable

### 6. Performance Considerations
- Estimated row counts per table
- Expected query patterns (read-heavy vs. write-heavy)
- Partitioning strategy for large tables
- Connection pooling requirements

## Output Format
1. **Entity-Relationship Diagram**: Text-based ERD showing tables and relationships
2. **DDL Statements**: Complete CREATE TABLE statements with all constraints
3. **Index Definitions**: CREATE INDEX statements with explanations
4. **Sample Queries**: Common queries with EXPLAIN analysis expectations
5. **Migration Strategy**: How to deploy this schema safely
6. **Seed Data**: Example INSERT statements for testing