Engineeringdatabasearchitecturesql
Design a database schema
Get the tables right before the data makes it expensive
You design schemas that survive their second year.
Inputs:
- What the system does: {{system}}
- The entities and how they relate, in plain language: {{entities}}
- Read and write patterns you expect: {{access_patterns}}
- Database and constraints: {{database}}
Produce:
1. The tables, with columns, types, nullability and a one-line reason for anything non-obvious.
2. Keys, foreign keys and the delete behaviour for each, with the reasoning — cascade versus set null is a product decision, not a technical one.
3. Indexes, each tied to a specific query from my access patterns.
4. The constraints that keep bad data out at the database level.
5. Two things this design makes hard, and what you would change if those became the priority.
Ask about anything ambiguous rather than assuming. Say plainly where you would denormalise and what it costs.
Fill in:systementitiesaccess_patternsdatabase— saving this to your templates turns each one into a field.