Database schema design: a practical guide

We end up explaining this on discovery calls often enough that it deserved writing down. This guide covers what database schema design actually involves, where it usually goes wrong, and how to tell whether yours is in reasonable shape.

Code gets read far more often than it gets written, and usually by someone with less context than the author had. Nothing below assumes a large team or a large budget — most of it is a decision somebody has to make and then write down.

Why this earns attention

Schema mistakes get more expensive every month they survive. That sounds obvious written down. It is still the thing most often skipped. It is worth deciding this deliberately rather than inheriting whatever the last person set up.

For most businesses the question is not whether this matters but how much of it is worth doing right now. That depends on what you are trying to achieve in the next few months, not on best practice in the abstract. None of that requires a large budget, only a decision and someone to own it.

How to approach 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.

Most development decisions are really maintenance decisions wearing a different hat. The version that works in practice is usually less elaborate than the version described in the guides.

Constraints in the database beat validation in five places. Getting it slightly wrong is survivable. Ignoring it entirely is not. The version that survives contact with a real deadline is the simple one.

A working checklist

If you want a quick read on where you stand, work through this. Anything you cannot answer confidently is where to start.

• Schema mistakes get more expensive every month they survive
• Model the real relationships, not the current screens
• Constraints in the database beat validation in five places
• Someone is named as the owner, not just assumed to be
• There is a date in the calendar to review it again
• The decision and the reasoning behind it are written down somewhere findable
• You could explain the current setup to a new hire in five minutes

The mistakes we see most

The most common failure is not doing this badly. It is doing it once, during a launch, and never revisiting it. Circumstances move, the setup does not, and the gap widens quietly until something breaks or somebody notices the numbers.

• It was configured during a launch and has not been touched since
• Different people in the business believe different things are true about it
• There is no way to tell whether the last change helped or hurt
• The only person who understands it has left, or is about to

The question is rarely whether something can be built, but what it costs to keep running afterwards. Where this goes wrong is almost never a lack of knowledge.

How we approach it

On our projects this gets handled during the build rather than added afterwards, because retrofitting it costs several times more than including it. We write down what was decided and why, so the next person to touch it is not guessing.

If you are working with someone else, the questions worth asking are simple: who owns this, how will we know it is working, and what happens when it needs to change?

Where to go from here

Pick the single item from the checklist above that would cause the most trouble if it turned out to be wrong. Fix that one, confirm it worked, then move on. If any of that sounds like a description of your current setup, it is fixable.