Database schema design: what to get right first

This is cheap to get right at the start and expensive to retrofit. If you only fix one thing about database schema design this quarter, make it the first item below.

Most development decisions are really maintenance decisions wearing a different hat. It is worth deciding this deliberately rather than inheriting whatever the last person set up.

Start here

Schema mistakes get more expensive every month they survive. It is worth being explicit about, because assumptions differ quietly. Doing this properly once is usually cheaper than doing it approximately three times.

Then this

Model the real relationships, not the current screens. There is a version of this that is over-engineered, and it is worth avoiding. It is worth deciding this deliberately rather than inheriting whatever the last person set up.

Eventually

Constraints in the database beat validation in five places. In practice this is a scheduling problem more than a technical one. If it only works because one person remembers to do something, it does not work yet.

The short version

The question is rarely whether something can be built, but what it costs to keep running afterwards. Three things worth confirming about database schema design before you move on:

• Someone can say what the current setup is without going to look
• Constraints in the database beat validation in five places — and you know whether that is true here
• There is a way to tell whether the last change to this helped

Worth checking on your own setup before it becomes someone else's problem to fix.