A data modelthat survives yearthree

An identifier should carry no information. The moment an id encodes the year, the branch, the category or the sequence, somebody is parsing it, and the day the business changes one of those things you have a parsing bug in four systems and a customer-facing number that is now wrong.

Keep the meaningful reference as its own field — the invoice number, the case reference, the SKU — and let it be editable, nullable and formatted however the business wants this year. The primary key stays an opaque value nobody reads aloud.

The exception to argue about is whether that opaque value is sequential. Sequential keys leak volume to anyone who can see two of them, and they make a record's age guessable. For anything a customer can see, that is usually reason enough to avoid them.

A status column is a state machine with the rules deleted. It tells you where a record is and nothing about how it got there or where it may go next, which means every rule about legal transitions ends up scattered across whatever code happens to write to it.

Write the states down first: what they are, which transitions are legal, who may perform each one, and what must be true for the transition to be allowed. Then enforce that in one place. Most enterprise bugs that matter are illegal state transitions — a record that reached "dispatched" without ever being "picked", an approval that exists for a request that was later withdrawn.

You do not need an elaborate framework for this. A transition table and one function that is the only thing allowed to change the state is enough, and it is the difference between a rule and a convention.

Almost every row has at least three different times attached and they are routinely collapsed into one. There is when the thing happened in the real world, when the system was told about it, and when the row was last written. They are not the same, and reports built on the wrong one are quietly wrong in a way nobody notices for a year.

An order placed by phone on Friday and entered on Monday has a real-world time of Friday. A revenue report by the system time will move that order into the next week, and the first time anyone finds out is when a finance lead says the numbers do not match their own.

So: an explicit business timestamp, an explicit recorded-at, and the row's own updated-at. Store them in UTC, store the offset or zone where the real-world time is local to somewhere, and never let the application's default clock be the authority for a business fact.

"Delete" means at least four different things in an enterprise system: remove from the user's view, mark as void but keep for audit, anonymise because somebody exercised a right, and genuinely remove the bytes. These have different legal consequences and different consequences for every foreign key pointing at the row.

Pick the meaning per entity, in writing, before the first delete button exists. The expensive version of this conversation is the one where a record was soft-deleted for three years, a regulator asks for it to be actually erased, and seven tables reference it.

Soft deletion is the usual default and it has a cost people underestimate: every query, every unique constraint and every join from then on has to remember the flag. If you choose it, make the filtered view the default thing developers reach for, not something they must remember to add.

Status values, categories, reasons, types — these change. A code is retired, a new one is added, two are merged. If they only exist as application constants, the retired value is still sitting in ten thousand historical rows with nothing to explain what it meant.

Put them in a table with a code, a label, an active flag and the dates they were valid. Historical rows keep pointing at the retired code and the system can still render it correctly, which is exactly what you want when somebody opens a three-year-old record.

The temptation is to treat this as over-engineering for a list of five values. The five values are never five for long, and the migration that turns a constant into a table once there is history is considerably less fun than the table would have been.

Store an amount as an integer in the smallest unit along with its currency, always together, never as a float. This is well-worn advice and it is still the single most common bug class I see in systems that handle payments.

The part that is less often said: an amount frequently needs more context than currency. The rate used, when it was fetched, whether tax is included, and which jurisdiction's rules applied. A total that cannot be reconstructed from its own row is a total that will be disputed and cannot be defended.

If the system ever displays a historical amount, it must be able to show it as it was, not as it would be recomputed today. That means storing the result, not just the inputs.

Append-only, written in the same transaction as the change, carrying the actor, the entity, the before value, the after value, and the reason where the business requires one.

Added at the start this costs about a day. Added later it is a rewrite of every write path in the system, and the records from before it existed are simply gone. The first time an external audit asks who could see a record on a particular date, the difference between those two situations is a lookup and a project.

Do not build it from database triggers alone. A trigger knows what changed; it does not know who asked for it or why, and those are the two columns anybody actually wants.

A model optimised purely for writing is a model that cannot answer anything without six joins and a weekend. The reporting requirement arrives in every project, usually late, usually from someone who was not in the modelling conversation.

Ask one question during design: what will somebody want to count, and by what. The answers tell you which dimensions have to be first-class fields rather than values buried in a JSON column — region, channel, owner, category, period. Finding that out in week two costs a column. Finding it out in year two costs a backfill across millions of rows where the information was never captured in the first place.

That is really the whole theme. Almost every rule here is cheap at the point where the model is still an argument on a wall, and close to impossible once there is history. Spend the afternoon.