Database Schema Design & Optimization

You are a database architect with extensive experience designing schemas for high-traffic production systems. Create a comprehensive database design for the following application.

Application Context:
Application Type: [E-COMMERCE/SAAS/SOCIAL PLATFORM/ANALYTICS/CMS/OTHER]
Database Engine: [POSTGRESQL/MYSQL/MONGODB/DYNAMODB/MULTI-DATABASE]
Expected Data Volume: [SMALL/MEDIUM/LARGE/VERY LARGE]
Read-Write Ratio: [READ HEAVY/WRITE HEAVY/BALANCED]
Query Patterns: [SIMPLE LOOKUPS/COMPLEX JOINS/AGGREGATIONS/FULL TEXT SEARCH]
Compliance Requirements: [GDPR/HIPAA/SOC2/NONE]

Section 1 - Entity Relationship Design and Normalization:
Identify all core entities and their attributes with appropriate data types and constraints. Map the relationships between entities including one-to-one, one-to-many, and many-to-many associations with proper foreign key definitions. Apply normalization to third normal form while documenting any intentional denormalization decisions with performance justification. Define the primary key strategy choosing between auto-increment integers, UUIDs, ULIDs, or natural keys with tradeoff analysis for each. Create the enum and lookup table strategy for categorical data. Design the audit trail columns including created_at, updated_at, created_by, and soft delete mechanisms across all tables.

Section 2 - Indexing Strategy and Query Optimization:
Define the primary indexes, unique constraints, and composite indexes for each table based on the expected query patterns. Analyze the most frequent queries and design covering indexes that satisfy them without table lookups. Specify partial indexes for filtered queries and expression indexes for computed values. Establish the index maintenance strategy including monitoring for unused indexes and bloated indexes. Document the explain plan analysis approach for identifying slow queries and verifying index usage. Create the query optimization guidelines covering join order, subquery versus join decisions, and common table expression usage patterns.

Section 3 - Data Integrity and Constraint Design:
Define all check constraints, not-null constraints, and domain-specific validations enforced at the database level. Design the referential integrity strategy including cascade rules for updates and deletes across related tables. Create database triggers or generated columns for derived data that must stay consistent. Specify the transaction isolation levels for different operation types balancing consistency with concurrency. Design the optimistic locking mechanism using version columns for conflict detection. Establish the data validation boundary between application-level validation and database-level constraints.

Section 4 - Partitioning and Scaling Strategy:
Design the table partitioning scheme for large tables choosing between range, list, or hash partitioning with the partition key justified by query patterns. Specify the archival strategy for historical data including partition detachment, cold storage migration, and retention policies. Create the read replica configuration for scaling read-heavy workloads including replication lag tolerance and routing rules. Define the connection pooling strategy with pool sizes tuned for the expected concurrency. Plan the vertical and horizontal scaling milestones with specific data volume triggers for each scaling action. Address the sharding strategy if the system needs to distribute data across multiple database instances.

Section 5 - Migration and Seed Data Strategy:
Create the migration framework approach including version-controlled migration files, rollback procedures, and zero-downtime migration patterns. Define the seed data strategy for development, testing, and production environments. Specify how large schema changes like adding columns to billion-row tables are handled without locking. Design the data transformation pipeline for migrating between schema versions when existing data needs restructuring. Establish the backup and point-in-time recovery configuration with recovery time and recovery point objectives. Document the database change review process that prevents unsafe migrations from reaching production.

Section 6 - Monitoring, Maintenance, and Compliance:
Define the database monitoring dashboard covering query performance, connection utilization, disk usage, replication lag, and lock contention. Create the automated maintenance schedule for vacuum, analyze, reindex, and statistics collection operations. Design the data encryption strategy for at-rest and in-transit encryption including column-level encryption for sensitive fields. Specify the access control matrix defining which application services and user roles can access which tables and columns. Create the compliance data handling procedures including data subject access requests, right to deletion, and audit logging. Document the disaster recovery runbook with failover procedures and recovery testing schedule.