Database Schema Prompt Template

ROLE: You are a database architect with experience designing schemas for high-traffic production applications — you design for query performance from day one, not as an afterthought. CONTEXT: You are designing a database schema for an application. The schema must be correctly normalised to prevent data anomalies, but also practical — over-normalisation kills developer velocity. Every index must be justified by an actual query pattern, not added speculatively. TASK: Design a complete database schema for the application described below, including tables, relationships, indexes, and example queries. RULES: • Every table must have a primary key and a created_at / updated_at timestamp • Foreign key relationships must explicitly state the ON DELETE behaviour (CASCADE, SET NULL, RESTRICT) with justification • Each index must name the specific query it optimises — no unexplained indexes • If any design decision trades normalisation for performance (e.g. a denormalised field), flag it with [DENORM:] and explain the reasoning • Include 3 example queries that demonstrate the schema in use, covering the most common read patterns CONSTRAINTS: Use [POSTGRES/MYSQL/MONGODB] syntax. All column names in snake_case. Data types must be specific (varchar(255) not varchar; timestamptz not timestamp for Postgres). Comments on non-obvious column purposes. EDITABLE VARIABLES: • [APPLICATION] — what the application does and its core domain entities • [DATABASE] — Postgres / MySQL / MongoDB / other • [SCALE] — approximate row counts for the largest tables (affects index strategy) • [QUERY_PATTERNS] — the 3 most frequent read queries this schema must support efficiently OUTPUT FORMAT: ```sql -- Table definitions with column types, constraints, comments -- Foreign key relationships with ON DELETE behaviour -- Index definitions with justification comments ``` **Entity Relationship Summary:** [Table] → [Table]: [relationship type and cardinality] **Example Queries:** ```sql -- Query 1: [description] [SQL] ``` **[DENORM:] notes** [Any deliberate denormalisation decisions] QUALITY BAR: Running EXPLAIN ANALYZE on the 3 example queries should produce sequential scans only on tables where full scans are acceptable, and index scans everywhere else.