Fill in these first
- Database: [MySQL 8, PostgreSQL 16, SQLite, SQL Server]
- What the app does: [two or three sentences]
- Things to store: [e.g. users, orders, products, reviews]
- Rules: [e.g. a user can review a product once; orders keep the price paid]
- Expected scale: [rows per table in a year]
- Most common reads: [e.g. list a user's orders newest first]
Task
Design a schema that stores this data correctly, keeps it consistent and serves the common reads quickly.
Output format
- Entity summary: each table and one line on what a row represents.
- Table definitions, one block per table:
| Column | Type | Null allowed | Default | Constraint | Notes |
- Relationships: one line per foreign key, with the delete behaviour and why.
- Index plan: each index, its columns and the read it serves.
- SQL: complete CREATE TABLE and CREATE INDEX statements for the stated database.
- Trade-offs: any deliberate denormalisation and what keeps it in sync.
- Open questions: decisions that need the product owner.
Quality rules
- Every table has a primary key.
- Store money as integer minor units or a fixed decimal, never a float.
- Store timestamps in UTC with a clear type.
- Use unique constraints to enforce the business rules given, not only application code.
- Name tables and columns consistently in snake_case.
- Do not store passwords; store a password hash column only.
- No index without a stated read it supports.
Example
| order_total | BIGINT | no | 0 | CHECK >= 0 | amount in paise or cents |






