Database Schema Designer

Design a database schema for the following application: **Application Type:** {{app_type}} **Database:** {{database}} (e.g., PostgreSQL, MySQL, SQLite, MongoDB) **Core Features:** {{core_features}} **Scale Requirements:** {{scale}} (e.g., 10K users, 1M records, multi-tenant) **Special Requirements:** {{special_requirements}} (e.g., soft deletes, audit trail, multi-currency) Please provide: ## 1. Entity-Relationship Overview Describe the main entities and their relationships in plain language before any SQL. ## 2. Schema Design For each table: ```sql CREATE TABLE table_name ( -- columns with types, constraints, defaults ); ``` Include: - Primary keys (use UUIDs for distributed systems, integers for performance-critical) - Foreign keys with ON DELETE behavior - NOT NULL constraints - Default values - Check constraints where appropriate ## 3. Indexes List all recommended indexes with justification: ```sql CREATE INDEX ... ``` ## 4. Relationships Summary Table showing: Table → Relationship → Table (1:1, 1:N, N:M) ## 5. Design Decisions - Why you chose this structure over alternatives - Any denormalization decisions and why - Scalability considerations ## 6. Common Queries Show the SQL for the 5 most common queries this schema will serve. ## 7. Migration Notes If migrating from an existing schema, note the migration order and risks.