Deleting a product must not erase last year's sales

You retire a discontinued product and you worry about two things. Will the system fill up with blank rows and buttons that do nothing? And will removing the product quietly rewrite last year’s sales?

In ours the first happened. On one page about twenty-five rows could not work out what they were called, and each offered an action that could never succeed. We found it when an automated check failed on a product that had been retired by other work: the price entry for it had survived, pointing at a product that no longer answered. The second worry is the one that matters more, and it decided how we fixed it.

What was actually going on

Retiring a product had no follow-on at all. Not a wrong one, none. The variants under it, the price-list entries for it and the special prices set for individual customers all stayed live and reachable, pointing at something gone.

We fixed that, and a repair run cleared what was already broken: 211 orphaned price entries and 104 orphaned variants. It felt complete. It was not.

Retiring a single variant directly had no follow-on either, because we had written the fix on the path where the bug was reported. So the rows we had just repaired could be orphaned again the next day by a different action. Deleting a branch did the same to its stock-level rows, which we had not considered at all, because the reported symptom was about a product.

Then the count. Of 523 live stock-level rows, 259 belonged to a deleted product and 22 to a deleted branch. Roughly half the table pointed at something that no longer existed, in a system that had been running for weeks with its checks passing.

What we changed

The tempting correction after three misses is to remove everything attached to whatever is being removed. That would be worse. An order line points at a product: if retiring a product removed order lines, retiring a discontinued item would rewrite last year’s sales. A stock movement points at a variant and a branch: remove those and the record that current quantities are worked out from would gain holes, and the sums would stop balancing.

So we sorted everything that hangs off a product or branch into three kinds, once, and apply that to every new table.

Settings that mean nothing without the parent go with it: variants, price-list entries, special customer prices. Records that something happened never go: order lines, dispatch lines, stock movements, and the orders, invoices and payments that belong to a branch. A report on last quarter must give the same figures next year as today.

The third kind needed an argument. A stock-level row is today’s quantity, and it can be recalculated from the stock movements, so it is a summary of history rather than history itself. It goes with its parent. Nothing is lost, it could not be reached anyway, and it carried the reorder setting, which is why the stock list offered an action that could never work.

Both repairs reported counts rather than running quietly, and each ended with a check that the number of orphans was zero.

What it did not fix

Hiding a record instead of deleting it moves a safety job out of the database and into rules that people have to keep applying. Every list must remember to skip hidden records, every uniqueness rule must ignore them, and every parent and child relationship must be settled by hand. None of that is enforced. All of it is convention, and conventions fail at some rate.

It is a fair trade, because it buys recovery after mistakes and history that survives them. But it is a trade, and it should be made knowingly.

The pattern, for anyone retiring products, branches or customers

For each thing attached to an item you can retire, ask which kind it is: a setting, a record of something that happened, or a total worked out from those records. Settings and totals follow the item. Records of what happened never do.

Then ask the test that catches the rewrite: if I retire this today, will last quarter’s report give the same figures next year? And ask for a count of orphaned rows before and after any repair. If the number surprises you, the problem was older than the report.

Where this ends up

Products, variants and price-list entries in Sazinga Field are sorted this way, with settings and summaries following the product and history never doing so, which is why retiring a product does not leave a page of labels that cannot resolve.