Database Design Decisions That Are Expensive to Reverse

Database Design Decisions That Are Expensive to Reverse

Most schema changes are easy. A few are not, and they tend to be the ones made in the first week, when the data is hypothetical and everything seems changeable. These are the decisions worth slowing down for.

Identifiers

Every record needs an identifier that never changes and never gets reused. Surrogate keys serve internal relationships; externally visible identifiers should be separate, non-sequential and safe to expose. Rewriting identifiers later means rewriting every reference to them.

Relationships and ownership

Decide which record owns which, and enforce it in the database rather than in application code that might forget. Deleting a customer should have a defined meaning: cascade, restrict or anonymise, chosen per relationship rather than left to a default. Foreign keys are cheap now and invaluable later.

History and audit

If the system will ever be asked "what did this say last month", current state is not enough. Keep the changes: who changed what, when, from which value to which. Rebuilding history after the fact is impossible, and guessing is worse than having none.

Money, time and units

Store money in minor units with an explicit currency; never floating point. Store timestamps with time zone, and record which zone a business rule refers to. Keep quantities with their unit. These are the errors that surface as reconciliation problems months later, when the data is already inconsistent.

Normalise first, denormalise deliberately

Start with a structure that has one home for each fact. Denormalisation is a legitimate performance tool, but it should be an answer to a measured problem, with the duplication accounted for and a way to keep it consistent.

Plan for scale, not for fantasy

Index the queries you actually run, understand the growth rate of the largest tables, and know which operations will not survive it. Beyond that, premature optimisation costs more than it returns.

How we approach it

Data modelling happens during planning at Black Origin IT, before interfaces are built on top of it, and is reviewed again when the first real usage patterns appear.

Starting a system around important data? Tell us about your project and we will design the schema with care.