Before you invest in database schema design

The advice here is unglamorous, which is probably why it gets skipped. Before you spend anything on database schema design, it is worth confirming a few things are already true.

Code gets read far more often than it gets written, and usually by someone with less context than the author had. Budget a little time for it every quarter and it never becomes a project of its own.

Prerequisites

• You can describe the outcome you want in one sentence
• Someone owns it after the work is done
• Schema mistakes get more expensive every month they survive
• You have a way to tell whether it worked

Common failure modes

Model the real relationships, not the current screens. 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.

Constraints in the database beat validation in five places. It is worth being explicit about, because assumptions differ quietly. It is worth deciding this deliberately rather than inheriting whatever the last person set up.

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.