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.