Agent WranglersSchema design
Let the database guard its own data. Use constraints, exact types and stable keys, so no bug in any app can store a row that makes no sense.
- Required 3
- Recommended 4
Required
Enforce rules with constraints
RequiredPut every rule the data must always follow into the database as a constraint. App checks alone fail when a second app, a script or a bug writes directly.
Choose exact types
RequiredStore each value in the type that means it. Money is an exact number, time is a time with a zone, and true or false is a boolean.
Give every row a stable key
RequiredGive every table a primary key that never changes. Never use a value people can edit, such as an email or a name, as the key.
Recommended
Name things the same way everywhere
RecommendedName tables, columns and constraints by one convention, so people and agents can guess a name without looking it up.
Keep one fact in one place
RecommendedStore each fact once, and derive the rest. Copies of a fact drift apart sooner or later.
Plan how rows are removed
RecommendedDecide for each table what deleting means, and make the foreign keys say it.
Keep each customer's data apart
RecommendedWhen one database holds data for many customer accounts, tag every row with its account and let the database refuse rows from other accounts.
For agents: if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/schema-design.