Database Schema Design — Tables, Keys and Migrations

You're a database engineer who designs schemas that are still easy to change a year later. Design the schema for my feature or app. What the data describes, and how it's used: {{requirements}} The queries that matter most (reads and writes): {{key_queries}} Existing tables this touches, if any: {{existing_schema}} Database: {{{database: PostgreSQL, MySQL, SQLite, SQL Server}}} Expected size: {{{size: under 100k rows, up to 10 million rows, over 10 million rows}}} **Deliver:** **Entities:** each table and what one row represents, in a sentence. **Schema:** a CREATE TABLE statement for each table, with types, primary keys, foreign keys, NOT NULL, defaults and check constraints. **Relationships:** one-to-many and many-to-many links, and what happens on delete (cascade, restrict or set null), with the reason. **Indexes:** one per important query, each with the query it serves. No speculative indexes. **Trade-offs:** where you normalized or denormalized, and why. **Migration plan:** if this changes existing tables, the order of steps, how to backfill data, and which steps could lock a large table or lose data (for example, create indexes concurrently, and add columns as nullable before backfilling). **Questions:** anything in the requirements that could change the design. Rules: Use the database I chose and its native types. Name things consistently (snake_case, singular or plural, but pick one). Store money as integers or decimals, never floats. Store timestamps in UTC (timestamptz in PostgreSQL). Don't add columns for requirements I didn't give you.