By name: database-schema description: Design a database schema
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.