After deleting a board, nobody could add a new one
Adding a site failed with a bare Validation error once any board had been deleted. A deleted board still held its code, and the error named nothing.
Topic
Data Modelling
Schemas, constraints and keys, and the rules they are really enforcing.
35 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.
He marked attendance every day and the report missed him
A depot report showed nothing for an employee with 404 attendance records. A join that dropped anyone without a depot entry hid his whole history.
I sent a notice to one region and the wrong staff got it
A region-wise notice reached 11 of the 500 people it was meant for. Two different lists of regions shared one name, and the form wrote one and read the other.
People who left months ago still showed on the team list
A manager's team screen showed 39 people, five of them deleted months earlier. 75 people across 60 leaders' teams were only half-deleted.
Tagging one handover photo quietly deleted another
Office staff label each handover photo. Relabelling one removed the photo that held the label, and the next click failed six times in a row.
The proof deck put the close-up where the wide shot belonged
The report sent to advertisers shows two photos per board. If the close-up was taken first, it printed in the wide-shot slot. One field was doing three jobs.
The same quiz could pay a child points again and again
A rewards scheme paid per attempt, so a retry was safe but a retake paid again. A flat price also made payouts grow with the quizzes added. Found in review.
The spreadsheet and the system disagreed on board locations
A client kept board coordinates and bookings in two Excel files with no site code. Of 47 boards in both, 34 differed, by 4 m to 3,365 m. How we reconciled them.
Every booking scheme lost its last day
A booking window ran to 5 September. On that day a dealer placing a first order was told it had closed. Every scheme the company had run had lost its last day.
A hundred of the rules we tracked may cite a repealed law
A 184-obligation construction register was six weeks old when we found about a hundred entries may cite a law that had been replaced. Nothing in it could tell us.
The product page listed the same crop 36 times
A dealer-app product screen repeated every crop name 36 times and took 2.5 seconds to open. The table behind it mapped every product to every crop in every state.
This number is already in use, and nobody has it
A depot could not register a distributor because the phone number was taken. Nobody had it. An employee had been locked out for three months by the same mechanism.
We sent a stranger another customer's pickup time
WhatsApp confirmations to foreign customers were failing. The failures were the lucky cases: three messages with a UK customer's booking reached a stranger's phone.
9,300 contacts nobody could call, and an import 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.
A chatbot over your company data can be confidently wrong
Before building an AI assistant over an orders database, we found 2,667 approved orders it would have reported as cancelled, with no error.
A migration that would have failed only in production
Forty key columns moved to one ID format. Development ran a newer database than production, so the obvious fix would pass every test and fail on the live box.
The tax officer asked why invoice 41 was missing
In several countries an invoice series must be unbroken. A plain database counter leaves holes the moment a draft is abandoned. Here is what we built instead.
Compliance software that only tracks staff certificates
Most of what you can be inspected on is filings, notices and inspections, not certificates. A register of 184 obligations shows why that matters.
When the tax portal is slow, does your shop stop selling?
Some tax regimes make an invoice invalid until the government stamps it. Built naively, one slow afternoon at the regulator stops every till in your shop.
The auditor asked what this record said in March
An auditor wants a record as it stood in March and who changed it since. Most systems only promise staff will not edit history. Ours makes the edit impossible.
The till would not bill, because another business was busy
A till refused to bill for no reason its owner could see. The invoice number came from a clock, repeated every 16 minutes and was shared with every other customer.
The low-stock screen was empty while the shelves ran down
Stock was visibly running out and the low-stock screen showed nothing. The opposite failure, an alert on every order, comes from treating blank as zero.
Removed from the app, yet her tablet still worked
In a children's learning app, removing a child left her tablet signed in, and adding her back made a blank copy with none of her history.
One building with a press, a store and a shop counter
Stock software often splits one building into three locations. We describe a site by what it can do instead, and found a check that used the wrong rule.
Deleting a product must not erase last year's sales
Retiring a product left 211 orphaned prices and 104 orphaned variants, and half the stock rows pointed at nothing. Removal must never touch sales history.
Six weeks on, the customer asks why the table cost that
A customer queries a line six weeks after the quote. The sheet rate has moved twice, the estimator is on site, and a price you cannot reconstruct is one you concede.
The system would not book in a full sack from a supplier
A stock system refused 100 kg received against a 90 kg order, so clerks edited the agreed order to get goods shelved. The rule was the fault.
Which pass of the press is losing you product?
Yield was recorded per batch, so a bad pass through the press was hidden. Recording output per run fixed that, but only from a date forward.
Why a promotion ended at the wrong hour
A promotion and every customer's free trial ended five and a half hours away from the time the business meant, because the software kept the wrong clock.
Composite keys: tenant isolation nobody has to remember
Composite keys stop a cross-tenant row being written; row-level security limits what is read. Two systems, and the gaps each one wrote down.
Credited twice, and nobody could explain the balance
A child did one piece of work and was paid for it twice. A parent asked where the points went and nobody could answer. Why a total you can edit cannot be trusted.
Booking software with nowhere to put 'until further notice'
About fifteen of a media owner's thirty-eight campaigns had no end date. Leaving the date blank would have quietly broken availability. What we did instead.
The software would not raise an invoice without a booking
Every invoice had to sit on a booking. A partner's share, a supplier's mounting bill, an advance, a credit: real invoices with no booking could not be recorded.
Car rental software showing a driver you removed
Eighteen trips showed a driver who had been taken off, and on seven the removed driver could no longer be identified. The record kept for safety caused it.
Partner profit share and agent commission are not the same
A co-investor's profit share and an agency's commission both go out to a company each month. Added together, they give a total that means nothing.