How Much Is Where? Modelling a Multi-Stage Production Flow

The hardest part of building a production system is not the code. It is the model.

Here is the problem in plain words. Material arrives. It is issued to production. It passes through several stages, one after another. On the way some of it is rejected, some is reprocessed, and at the end what is left is packed and shipped.

Now answer this, at any moment: how much is where?

It sounds simple. It is not.

Three things that make it hard

1. The same material must never be counted twice

A quantity can be issued, in production and partly finished all at once, depending on who is looking. If two screens can both claim the same metres, your totals are fiction.

2. Quantities do not survive the journey

What goes in is not what comes out. There is loss, rejection and reprocessing. A model that assumes nothing is lost will be wrong on the first day it meets a real factory.

3. One batch can split, and pieces can join

So the flow is not a straight line. It branches and joins. The totals must hold whichever path you follow.

The rule I settled on

The database must make a wrong state impossible to write. Not unlikely. Impossible.

A check in the screen can be skipped. A rule inside the database cannot. So the links between records, and the limits on quantities, are enforced by the database itself, not by hope.

One example: each stage refuses to receive more than the stage before it delivered. That single rule catches most typing mistakes at the moment they happen, while the operator is still standing at the machine.

Pen and paper first

I spent far longer with a pen and paper than in a code editor, and it was the best time I spent on the whole project. You cannot code your way out of a wrong model. Every hour of modelling saved me days of fixing.

Understand the business before you design the tables. The tables are where that understanding lives.

The bug that taught me what transactions are for

One "save" in a production system is rarely one thing. Saving a production entry may write the main record, several detail lines and a running status. Three or four writes that only make sense together.

My early code did them one after another. Then one failed in the middle.

The first two writes were already saved. The third was not. The database now held a record that could never exist in real life: an entry that was only half itself.

Nothing looked broken. There was no error screen. Just quietly, permanently wrong data, waiting to be found by someone reading a report a week later.

That is the worst kind of bug. It does not crash. It lies.

The fix: all of it, or none of it

A transaction groups the writes. Begin, do every write, and keep them only if every one succeeded. If anything fails, undo everything, as if nothing had been tried.

Transactions are now used everywhere in the system where one action touches more than one place.

I used to think transactions were an advanced subject for banks. They are for anyone whose save writes more than one row. That is almost everyone.

Why a business owner should care

A crash is a good day. A crash tells you something went wrong. Silent wrong data tells you nothing, and you find out when someone makes a decision on numbers that were never true.

When you look at any production software, ask two questions:

1. If the power fails in the middle of a save, what is left in the database?
2. Can the system ever show more finished goods than the material that was issued?

A good system has a clear answer to both.

Protect the data. Everything else can be recovered.

You can see this model working in the textile mill ERP. For the wider picture, read what a textile mill ERP must track.