How to get database schema design right
It is rarely the thing that gets a project approved, and often the thing that decides how it goes. The short answer to database schema design is that it is mostly a sequence of small decisions, not one big one.
The question is rarely whether something can be built, but what it costs to keep running afterwards. Write the reasoning down alongside the decision, because the reasoning is what changes first.
Why it matters
Schema mistakes get more expensive every month they survive. There is a version of this that is over-engineered, and it is worth avoiding. It is the sort of thing that looks like polish right up until it costs you an enquiry.
The steps
- Establish what you have today before changing anything
- Model the real relationships, not the current screens
- Constraints in the database beat validation in five places
- Write down the decision so the next person does not re-litigate it
Constraints in the database beat validation in five places. The cost of getting this wrong is rarely visible on the day it happens. If it only works because one person remembers to do something, it does not work yet.
Turning this into a decision
In practice
Most development decisions are really maintenance decisions wearing a different hat. Three things worth confirming about database schema design before you move on:
- Someone can say what the current setup is without going to look
- Model the real relationships, not the current screens — and you know whether that is true here
- There is a way to tell whether the last change to this helped
None of this needs a rewrite. Most of it is a morning's work once someone decides to do it.