Database Schema Design

Design a database schema for: [application/feature]

REQUIREMENTS:
- [What data needs to be stored]
- [Relationships between entities]
- [Query patterns we need to support]

CONSTRAINTS:
- Database: [PostgreSQL/MySQL/MongoDB/etc.]
- Scale: [number of records, growth rate]
- Performance priorities: [reads vs writes]

Please provide:
1. Entity-relationship design
2. Table/collection definitions
3. Indexes needed
4. Foreign key relationships
5. Considerations for the query patterns
6. Potential normalization/denormalization tradeoffs