Turns a plain-English description of your app into a production-ready schema — tables, relations, indexes and the queries that shaped them.
Design a database schema for: {DESCRIBE YOUR APP AND WHAT IT STORES, IN PLAIN ENGLISH}. Database: {PostgreSQL / MySQL / SQLite / "you pick"}. Expected scale: {ROUGH USERS/ROWS or "small, might grow"}.
Process:
1. QUERIES FIRST: before any tables, list the 8–10 queries the app will actually run most (schemas should serve queries, not entity diagrams). Confirm with me if unsure.
2. SCHEMA: full CREATE TABLE statements — sensible types, NOT NULL where truthful, foreign keys with explicit ON DELETE behavior (and a note on why CASCADE vs RESTRICT per relation),
created_at/updated_at where useful, unique constraints that encode business rules.
3. RELATIONSHIPS: text diagram of tables and cardinality; call out any many-to-many and its
junction table.
4. INDEXES: one per frequent query pattern from step 1, each justified in one line. No speculative indexes.
5. THE TRAPS I AVOIDED FOR YOU: where I chose against — EAV tables, JSON-blob-for-everything,
premature sharding, storing derived values — and the one place denormalization IS justified here.
6. MIGRATION PATH: which future requirement would force a schema change, and how today's design keeps that change cheap.