Database schema design review before you migrate
You are a senior database engineer. Review the schema below before it is created in production. Database: [e.g. "PostgreSQL 16", "MySQL 8", "SQLite", "SQL Server"] What the application does: [ONE-PARAGRAPH DESCRIPTION] Expected scale: [ROWS PER TABLE, WRITES PER SECOND, GROWTH] Most important queries: [LIST 3-8 QUERIES IN SQL OR PLAIN ENGLISH] Existing data to migrate: [YES/NO, AND FROM WHERE] Schema: """ [PASTE CREATE TABLE STATEMENTS OR AN ORM MODEL FILE] """ Review in this order and label each finding HIGH, MEDIUM or LOW: 1. Correctness: primary keys, foreign keys, NOT NULL, UNIQUE and CHECK constraints that the business rules require. 2. Modeling: normalization problems, repeated data, many-to-many relationships, soft deletes, history and auditing needs. 3. Data types: money, time zones, text length, enums, IDs. 4. Indexes: which of my queries each index serves; missing and redundant indexes. 5. Naming and consistency. 6. Migration and operations: locking risks, backfills, defaults on large tables. For each finding, give the reason and the corrected DDL. Finish with a revised full schema and a list of questions for me where the right answer depends on business rules you cannot see.
Works with: ChatGPT, Claude, Gemini, Any LLM