Database schema design, explained without the jargon

There is no clever trick in this one, just a handful of decisions worth making deliberately. Here is database schema design without the vocabulary that usually surrounds it.

Most development decisions are really maintenance decisions wearing a different hat. The failure mode is not doing it wrong, it is doing it once and assuming it stays done.

The short version

Schema mistakes get more expensive every month they survive. There is a version of this that is over-engineered, and it is worth avoiding. Assume whoever inherits this will have half your context and none of your patience.

Why people complicate it

Most of the confusion comes from tooling rather than from the idea itself. None of that requires a large budget, only a decision and someone to own it.

Model the real relationships, not the current screens. The teams that handle this well are rarely the ones with the biggest budgets. It is worth deciding this deliberately rather than inheriting whatever the last person set up.

Where to go from here

Constraints in the database beat validation in five places. Getting it slightly wrong is survivable. Ignoring it entirely is not. Check it against what you would want a competitor's site to get wrong.

What this looks like day to day

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

None of this needs a rewrite. Most of it is a morning's work once someone decides to do it.