Database Schema

Database Schema Design

Help me design a database schema:

Requirements

1. What are we storing? Describe the data. 2. Relationships: How do entities relate? 3. Access patterns: How will data be queried? 4. Scale: Expected data volume? 5. Database: PostgreSQL, MySQL, MongoDB, etc.?

Entity Design

For each entity, define:

Table Structure

• Table name (plural, snake_case)
• Columns with types
• Primary key
• Timestamps (created_at, updated_at)
• Soft delete (deleted_at) if needed

Constraints

• NOT NULL where required
• UNIQUE constraints
• CHECK constraints for validation
• DEFAULT values

Relationships

• Foreign keys
• ON DELETE behavior
• Junction tables for many-to-many

Indexes

• Primary key index
• Foreign key indexes
• Query-specific indexes
• Composite indexes where needed

Schema Generation

Generate:

1. CREATE TABLE statements 2. Index creation 3. RLS policies (if using Supabase) 4. Sample seed data

Optimization Review

Check for:

• Normalization level appropriate
• Index strategy sound
• Query patterns supported
• Future scaling considerations

Generate the complete schema with comments explaining decisions.