A short guide to database schema design

It is rarely the thing that gets a project approved, and often the thing that decides how it goes. Everything we would tell a client about database schema design in the time it takes to drink a coffee.

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. In practice this is a scheduling problem more than a technical one. It rarely shows up as a line item, which is exactly why it slips.

How to approach it

Model the real relationships, not the current screens. It is worth being explicit about, because assumptions differ quietly. Write the reasoning down alongside the decision, because the reasoning is what changes first.

Warning signs

Constraints in the database beat validation in five places. The cost of getting this wrong is rarely visible on the day it happens. It is the sort of thing that looks like polish right up until it costs you an enquiry.

The short version

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
• 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

The point is not perfection, it is knowing which of these you have consciously chosen to skip.