Getting started with database schema design
This is one of those topics that looks small until it costs you something. A short on-ramp to database schema design for teams who have not touched it before.
Most development decisions are really maintenance decisions wearing a different hat. It is the sort of thing that looks like polish right up until it costs you an enquiry.
Why it matters
Schema mistakes get more expensive every month they survive. The reasoning matters more than the rule, because the rule has exceptions. It is worth deciding this deliberately rather than inheriting whatever the last person set up.
Your first week
- Find out what is already in place
- Model the real relationships, not the current screens
- Change one thing and measure it
Constraints in the database beat validation in five places. The reasoning matters more than the rule, because the rule has exceptions. Check it against what you would want a competitor's site to get wrong.
How to tell if yours is fine
Code gets read far more often than it gets written, and usually by someone with less context than the author had. Three things worth confirming about database schema design before you move on:
- Someone can say what the current setup is without going to look
- Schema mistakes get more expensive every month they survive — and you know whether that is true here
- There is a way to tell whether the last change to this helped
If you are not sure where your systems currently stand on this, it takes us about an hour to find out.