Plan Database Schema

You are a database architect designing a schema for a production application.

Requirements

{{REQUIREMENTS}}

Entities

{{ENTITIES}}

Constraints

{{CONSTRAINTS}}

Database

{{DATABASE}}

Schema Design Process

1. Entity Analysis

For each entity:

• Identify all attributes with appropriate data types
• Determine primary key strategy (UUID, serial, composite)
• Identify required vs. optional fields
• Define default values and constraints (CHECK, UNIQUE, NOT NULL)

2. Relationship Mapping

• Identify all relationships (one-to-one, one-to-many, many-to-many)
• Determine foreign key placement and cascading rules
• Design junction tables for many-to-many relationships
• Consider polymorphic associations if needed
• Define referential integrity constraints (ON DELETE, ON UPDATE)

3. Normalization

• Apply 3NF as baseline
• Identify intentional denormalization for read performance
• Document trade-offs for each denormalization decision
• Consider materialized views for complex computed data

4. Indexing Strategy

• Primary key indexes (automatic)
• Foreign key indexes for JOIN performance
• Composite indexes for common query patterns
• Partial indexes for filtered queries
• Full-text search indexes if applicable
• GIN/GiST indexes for array or JSONB columns

5. Data Integrity

• CHECK constraints for domain validation
• UNIQUE constraints for business rules
• Trigger-based validation for complex rules
• Row-level security policies if applicable

6. Performance Considerations

• Estimated row counts per table
• Expected query patterns (read-heavy vs. write-heavy)
• Partitioning strategy for large tables
• Connection pooling requirements

Output Format

1. Entity-Relationship Diagram: Text-based ERD showing tables and relationships
2. DDL Statements: Complete CREATE TABLE statements with all constraints
3. Index Definitions: CREATE INDEX statements with explanations
4. Sample Queries: Common queries with EXPLAIN analysis expectations
5. Migration Strategy: How to deploy this schema safely
6. Seed Data: Example INSERT statements for testing