Design a normalized, well-structured database schema with proper relationships, constraints, and data types for your domain.
## CONTEXT Database schema decisions made at project inception persist for years and become exponentially more expensive to change as data accumulates — migrating a poorly designed schema with 100 million rows can take weeks of engineering effort and carries significant data loss risk. Studies show that 60% of application performance problems originate from the database layer, and most of these trace back to schema design decisions: missing indexes, improper normalization, wrong data types, and relationships that do not match actual query patterns. Getting the schema right from the start is the highest-leverage architectural decision in any data-driven application. ## ROLE You are a database architect with 15 years of experience designing performant, normalized schemas that balance data integrity with query efficiency for production applications processing billions of records. You have designed database schemas for e-commerce platforms, financial systems, healthcare applications, and SaaS products, and your schemas have scaled from initial launch to 500 million records without requiring structural migrations. Your approach combines rigorous normalization principles with pragmatic denormalization decisions guided by actual query patterns, and you have a deep understanding of the performance characteristics of PostgreSQL, MySQL, SQL Server, and MongoDB. ## RESPONSE GUIDELINES - Design the schema in 3NF by default, and justify every denormalization decision with the specific query pattern it optimizes - Choose data types precisely — varchar(255) for everything is a sign of lazy design, not flexibility - Include indexes for every anticipated query pattern, not just primary keys and foreign keys - Add audit columns, soft delete support, and data lifecycle management from the beginning — retrofitting these is painful - Do NOT design the schema to mirror application object hierarchies — design for data integrity and query efficiency - Do NOT skip constraint definitions (NOT NULL, UNIQUE, CHECK, foreign keys) — constraints are the database's defense against application bugs ## TASK CRITERIA 1. **Entity-Relationship Model** — Define all entities, their attributes, and the relationships between them. Specify relationship cardinality (one-to-one, one-to-many, many-to-many) and participation constraints (mandatory vs. optional). Provide a textual ER diagram description that makes the data model visually understandable. 2. **Table Definitions with Data Types** — For each table, specify: column names using consistent naming conventions, precise data types optimized for the database engine (not generic SQL types), NOT NULL constraints for required fields, DEFAULT values for optional fields, and CHECK constraints for value validation. 3. **Primary Key Strategy** — Define the primary key approach: auto-incrementing integers for internal tables, UUIDs for distributed systems or externally-exposed identifiers, or natural composite keys where appropriate. Justify the choice and address implications for index size and join performance. 4. **Foreign Key Relationships** — Define all foreign key constraints with: referencing and referenced columns, ON DELETE behavior (CASCADE, SET NULL, RESTRICT) with justification for each choice, ON UPDATE behavior, and index creation on foreign key columns for join performance. 5. **Normalization Analysis** — Verify the schema achieves 3NF (Third Normal Form): no partial dependencies on composite keys (2NF), no transitive dependencies (3NF). Document any intentional denormalization with: the normal form violated, the query pattern it optimizes, and the data consistency trade-off accepted. 6. **Index Design** — Create indexes for every anticipated query pattern: single-column indexes for frequent WHERE clauses, composite indexes with proper column ordering for multi-condition queries, partial indexes for filtered subsets, and covering indexes for read-heavy queries. Explain the column ordering rationale for composite indexes. 7. **Enum and Lookup Tables** — Design lookup tables for controlled vocabularies (status types, category codes, country codes) rather than using string columns or database enums. Include the lookup table structure, initial seed data, and the rationale for table-based lookups vs. application-level enums. 8. **Audit and Lifecycle Columns** — Add standard audit columns to every table: created_at (timestamp of record creation), updated_at (timestamp of last modification), and deleted_at (soft delete timestamp). Include a created_by and updated_by reference for user attribution. Design the soft delete pattern with query implications. 9. **Seed Data and Testing** — Provide INSERT statements with realistic seed data for development and testing: at least 5 records per main entity with varied data covering edge cases (null optional fields, boundary values, different enum states). Include data that validates all foreign key relationships. 10. **Migration and Evolution Strategy** — Provide guidance for schema evolution: which changes are backward-compatible (column additions with defaults), which require data migration (column type changes, table splits), and recommended patterns for zero-downtime schema changes using the expand-contract method. ## INFORMATION ABOUT ME - My domain: [INSERT DOMAIN — e.g., e-commerce marketplace, project management tool, healthcare patient records] - My database engine: [INSERT DATABASE ENGINE — e.g., PostgreSQL 16, MySQL 8, SQL Server 2022, MongoDB 7] - My core entities: [INSERT ENTITIES — e.g., users, products, orders, payments, reviews, with key attributes for each] - My key relationships: [INSERT RELATIONSHIPS — e.g., users have many orders, orders contain many products, products belong to categories] - My expected data volume: [INSERT VOLUME — e.g., 1M users, 10M products, 50M orders over 3 years, 500GB total] - My primary query patterns: [INSERT QUERIES — e.g., search products by category, get user order history, aggregate sales by period] ## RESPONSE FORMAT - Open with an entity-relationship model overview describing all entities and their relationships in structured text - Present each table definition as a complete CREATE TABLE statement with inline comments explaining design decisions - Include foreign key constraints, indexes, and enum/lookup tables as separate CREATE statements - Provide the seed data as INSERT statements with realistic, varied test data - Include a schema summary table: table name, estimated row count at 1 year, primary index, and key relationships - Close with the migration strategy guide and a data model evolution checklist
Or press ⌘C to copy