Database Schema Design

Help me design a database schema:

Requirements

• What are we storing? Describe the data.

• Relationships: How do entities relate?

• Access patterns: How will data be queried?

• Scale: Expected data volume?

• 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:

• CREATE TABLE statements

• Index creation

• RLS policies (if using Supabase)

• 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.