Look at any system that has been running for fifteen years. Almost everything around it will have changed. The interface has been rewritten twice. The framework has been migrated, or abandoned and reimplemented. The deployment mechanism has changed three times. The language may even be different.
The shape of the data usually remains. There are tables in that system whose columns were named in the first month. Those columns now have forty things depending on them. Nobody will ever rename them, because the cost is unbounded and the benefit is aesthetic.
That is why the database deserves more care than it often gets. A framework decision can feel urgent because developers touch it every day. A data decision can look dull in month one. Years later, the framework may be gone, while the first version of the data model is still shaping reports, integrations and decisions.
A framework mistake is bounded. A data mistake spreads.
Replacing a framework is a project. It is well understood, it can be done incrementally, the tests tell you when it is finished, and when it is done the system behaves identically.
Changing a data model is different. Every consumer has to change with it. Historical data has to be transformed, and the transformation frequently requires information the historical data does not contain. Reports that people have relied on for years produce different numbers, and somebody has to explain why the figure for 2021 is not what it was last month.
That difference should determine how much thought goes into each decision. In most projects, the effort goes the other way. Enormous energy goes into framework selection, which is reversible. Comparatively little goes into the data model, which is usually the harder thing to undo.
The business consequence is simple. A weak framework choice creates an unpleasant and bounded migration project. A weak data model becomes part of the organisation’s memory. It affects what the business can ask, what it can prove, and what it can safely change later.
Some early data decisions become effectively permanent
A short list of the ones we have seen cause the most pain later.
- Whether history is preserved. If a row is updated in place, the previous value is gone. Deciding later that you need history means you can have it from that date forward and never before. This is the single most consequential decision on the list and it is usually made by default rather than deliberately.
- The granularity of the core entity. Whether an order is one row or a header and lines. Whether a booking is per night or per stay. Getting this wrong is a rewrite of everything that touches it.
- How identity works. Whether a customer is one record or several linked ones. Whether an identifier is stable when somebody changes their email. Merging duplicate customer records years later is a project that never fully succeeds.
- Whether money is a number or a number and a currency. Systems built for one currency almost never survive contact with the second without a migration, and the second currency always arrives.
- How time is stored. Local time without an offset is a decision that costs somebody a fortnight eventually. Storing an instant and a timezone separately is boring and correct.
These choices look small when the first tables are being created. They rarely stay small. Each one becomes a rule that other code, reports and integrations start to rely on. By the time the weakness is obvious, the cost of changing it is no longer limited to the database.
Keep the events, because current state is not enough
If there is one principle we would press hardest, it is this. State tells you what is true now. Events tell you what happened. You can always derive the first from the second and never the reverse.
A system that stores only current state is throwing away information every time something changes. It does that permanently and silently. When somebody asks in three years what the price was on a particular date, or who approved something, or how often orders get amended after submission, the answer is that we did not keep that.
Those questions are normal in a long-lived system. They come from finance, operations, management and sometimes from outside the company. The problem is rarely that the team cannot write the query. The problem is that the fact was overwritten years earlier.
Storing events costs storage, which is cheap. It also costs some complexity in reading, which is manageable. It buys the ability to answer questions nobody has asked yet. That category of question is exactly what a long-lived system generates.
Start with the truth before optimising for speed
The old advice remains roughly right. Start normalised, because normalisation is a statement about what is true and denormalisation is a statement about what is fast, and the truth should come first.
Normalised data keeps each fact in the place where it belongs. That matters because copied facts drift. When the same value lives in several places, one of those places will eventually disagree with another.
Where denormalisation is needed for performance, it should be derived and rebuildable rather than authoritative. A materialised view or a maintained summary table, which can be dropped and regenerated, is a cache. A denormalised column that is the only place a value lives is a liability, because it will drift and there will be no way to tell that it has.
The distinction is important. A cache can be rebuilt from the source of truth. A copied value that became the only source cannot be checked against anything else. That is how a performance shortcut becomes a permanent ambiguity.
Names last longer than anyone expects
Column names outlive careers. They appear in reports, in integrations, in the queries analysts write, in documentation and in the mental model of everybody who works on the system.
A name that reflects a temporary business arrangement will confuse people for a decade. So will an abbreviation only the original team understood, or a concept the business has since renamed. People build explanations, training and reporting habits around those names.
It is worth an extra ten minutes and a second opinion. It is also worth resisting the temptation to encode the current org chart or the current product naming into the schema. Those things change. The column name often remains.
Database constraints protect the assumptions everyone depends on
There is a fashion for putting all validation in the application and treating the database as a store. It optimises for development speed early and it produces data that does not match anybody’s assumptions later.
Foreign keys, not null, uniqueness and check constraints are the only mechanism that guarantees an invariant across every path into the data. That includes the batch job somebody wrote in 2029, the migration script, and the analyst with write access.
Application-level validation guarantees the rule for the paths that went through the application. That is a much weaker statement than it sounds. Long-lived data gets touched by more than the original application. It gets loaded, migrated, exported, corrected and analysed.
The cost is that you have to think about what is actually true before you write it down. That cost is the exercise. A constraint forces the team to turn an assumption into a rule the database can enforce.
Design for the questions the business will ask later
A useful exercise before finalising a model: write down five questions the business will want answered in three years.
- Which customers changed plan more than twice.
- What proportion of orders are amended after submission.
- How long does an item spend in each state.
- Who approved the exceptions last quarter.
- What did this record look like a year ago.
Then check whether the model can answer them. Frequently it cannot, and the reason is always the same: something was updated in place rather than recorded as an event. Fixing that at design time is free.
This is where the business consequence should come before the technical mechanism. If the company later needs to explain a number, investigate a change or understand how work moves through a process, the answer depends on what the system kept. If the system only kept the latest value, the earlier fact has gone.
The schema should be designed deliberately, not generated as a side effect
A specific and common failure is letting the object relational mapper decide the schema. An object relational mapper connects application objects to database tables. The failure happens when the framework generates tables from class definitions, the developer never looks at the result, and the shape of the data becomes a side effect of how somebody organised their code on a Tuesday.
The application model and the data model serve different purposes. Class structure serves the application and can be refactored freely. Table structure serves everything that will ever read the data. That includes the reporting tool, the integration, and the system that replaces this application in nine years.
A join table named after a class relationship, with a surrogate key and no meaningful constraints, is a perfectly reasonable object model and a poor data model. It may make sense to the code. It may tell a future analyst very little about what the relationship means or what rules protect it.
We write the schema deliberately and map to it, rather than generating it and hoping. It takes longer at the start. It also means the schema can be read by somebody who has never seen the application, which is the state it will be in for most of its life.
Status values need to make sense without the application code
Every system has status fields, and the way they are stored causes more small pain than any other single decision. Storing a database-level enumerated type feels tidy and makes adding a value a schema migration, which on a large table can mean a lock.
Storing an integer that maps to a name in application code creates a different problem. The database is unreadable without the application. An analyst querying it directly sees a column of fours and sevens.
What has worked well for us is storing short, readable strings with a check constraint or a lookup table. The data is self-describing and the set of legal values is still enforced. Adding a value is then an insert or a constraint change rather than a rewrite, and somebody reading the table in 2035 can tell what a row means without the source code.
The related discipline is never to reuse a value for a new meaning. Statuses accumulate, and a status that meant one thing until 2028 and something else afterwards makes every historical query wrong in a way nobody detects.
Retention needs to be designed into the model
Almost nobody decides at design time what happens to data when it gets old, and the default is that it accumulates forever. That is fine until a table has a billion rows and every query against it needs an index nobody planned for. It is also fine until somebody exercises a deletion right and you discover the record has derivatives in nine places.
Deciding early is cheap. These are the questions that need answers.
- Which entities have a retention period and what is it.
- What is deleted.
- What is anonymised.
- What is archived to somewhere slower.
- Whether deletion of a customer cascades to their orders.
- If deletion does not cascade, what an order with no customer means for the reporting that assumes one.
Those questions have answers that are obvious to the business and invisible to the schema unless somebody asks. Recording them as decisions, with the reasoning, is the difference between a controlled data lifecycle and a table that everybody is afraid to touch.
We make these decisions at the start of a project
At the start of a project, we make these choices deliberately.
- Model events for anything with a lifecycle, and derive current state from them.
- Constraints in the database, always, including the ones the application also checks.
- Money as an amount and a currency, from day one, even for a single-currency client.
- Instants stored in UTC with the originating timezone kept alongside where it has meaning.
- Soft deletion only where there is a reason, and a documented retention rule when there is.
- An architecture decision record for every one of these, so the next team knows it was deliberate.
None of these choices is glamorous. They are the choices that keep a system understandable after the first team has moved on, after the framework has changed, and after the business starts asking questions the original build did not anticipate.
Spend the design effort where mistakes are hardest to undo
When choosing a framework, ask what happens if this turns out to be wrong. The answer is usually a migration project, unpleasant and bounded.
When choosing a data model, ask the same question, and notice that the answer is different. Everything built on top of it inherits the mistake. The cost of correction grows with every year and every integration. At some point the correct decision becomes to live with it permanently.
That is the reason to spend a disproportionate amount of the design budget in the place that looks least exciting.
More on how we approach this in custom software development, and on building for a long horizon in energy and utilities.
