Nine thousand contacts nobody could call, and one import that kept making more
The owner suspected the phone numbers were wrong. They were — 9,300 of them — and the check meant to prevent it had only ever tested one end.
Topic
Data Modelling
Schemas, constraints and keys, and the rules they are really enforcing.
22 articles
About this topic
Most of the expensive bugs in an operational system are modelling decisions that were made quickly and then lived for years. Whether a balance is stored or derived, whether a soft delete has a defined answer for every child row, whether a foreign key is a business rule or a convenience — none of these announce themselves at the time, and all of them are discovered later by an accountant.
The recurring theme here is that a constraint is a claim about the world, and it is worth checking the claim before enforcing it. A check constraint that made over-delivery impossible to record did not stop lorries arriving with more than the order; it stopped the system being able to say so. That is worse than the problem it was written to prevent.
Twenty-one of the joins were invisible to the schema, so we extracted them from the code
Text-to-SQL guesses join paths. We catalogued the real ones from the queries the service runs, graded by evidence, and checked every table name against the database.
A column named id is a naming convention, not a type
Converting forty key columns to a single id format, three of them had to be reverted, because what they held was a handle, a display name and a mixed bag.
A draft invoice must not have a number, and a sequence cannot give you a gapless one
Auditors ask why invoice 41 is missing. A database sequence will make holes, and a number allocated at draft becomes a hole the moment the draft is discarded.
A compliance register is not a list of obligations, it is a rule that produces a list
The first model was a person, a certificate and an expiry date. Then came a register of 184 obligations across 15 domains, almost none of them certificates.
An invoice that is not real until a third party says so
Statutory real-time invoicing turns each sale into a distributed transaction with a regulator. The first design put the network call inside the sale transaction.
Append-only is a database privilege, not a coding convention
A draft function set a row's identifier after inserting it. The table had UPDATE revoked, so the schema would have rejected code that every review had passed.
The document number was six digits of a clock, and the retry loop was the giveaway
A comment said the numbering scheme avoided race conditions. The loop underneath it retried on duplicate key ten times, which is what you write when it does not.
Not set and zero are different answers, and one input box cannot hold both
A reorder level of zero means alert me when it runs out. No reorder level means never alert me. The empty string coerces to zero and the distinction is gone.
Remove and Add are not inverses, so the child came back as a stranger
Removing a child archived the row. Adding her back created a new one. Her whole history stayed behind, and her old tablet credential stayed live.
A site is not a type, it is a set of capabilities, and the difference shows up in month two
One building held a press, a store and a shop. A type column forces a choice; a capability set does not, and the check reading it used the wrong operator.
Soft delete needs a taxonomy of children, not a decision per table
Deleting a product left 211 orphaned price entries and 104 orphaned variants. Fixing the product path missed the variant path, which orphaned exactly the same rows.
Store the working, not just the answer, and features you have not thought of become cheap
Keeping the full evaluation trace on the quote line paid for itself twice — in explaining old quotes, and in a bill of materials nobody had planned for.
A check constraint made over-delivery impossible to record, and lorries do not read constraints
The received quantity could not exceed the ordered quantity. That is a policy, not a fact, and the warehouse hit it the first time a supplier sent a full pallet.
The constraint names remembered a table that no longer existed
A production output table had a nullable parent column, an old parent beside it, and two identical foreign keys — one migration, told three ways.
Store everything in UTC, but never decide anything in it
A test proved expired discounts still applied. The test was wrong, the bug was real, and both came from treating a business date as an instant in time.
Making a cross-tenant reference physically impossible with composite foreign keys
Tenant isolation enforced by WHERE clauses depends on nobody forgetting one. Enforced by the schema, a cross-tenant row cannot be written at all.
Never store a balance. Store the entries that produce it
A mutable points column looks simpler for about a week, and then a retried request pays twice and nobody can explain where the balance came from.
How to store a booking that has no end date without breaking every date query you own
Runs until the client stops" is a real commercial term. Storing it as a null end date breaks overlap checks, calendars and availability all at once.
A not-null foreign key is a business rule, and this one was not true
Every invoice had to reference a booking. The business raised invoices without bookings all the time. The constraint had encoded an assumption nobody checked.
The soft delete was the bug, not the safety net
Keeping a husk row for the audit trail produced phantom drivers on eighteen legs. Deleting the row and logging the change was the safer design.
Two payouts that look identical on a bank statement and must never share a table
A co-investor's profit share and an agency's introduction commission are both money out to a company. Everything else about them differs.