What a part genealogy record must contain
Most genealogy implementations answer the backward question well and the forward question badly. The cause is almost always one modelling decision made early.
Two questions, not one
Backward: given this unit, what went into it? Forward: given this component, which units contain it? They sound symmetrical and they are not, because the backward query walks a tree you already have a handle on, and the forward query searches for it.
A design that only anticipates the backward query produces a record that is perfectly readable per unit and unusable during a containment.
The mistake that breaks recall
Storing consumed components as a JSON blob on the unit. It is convenient, it renders nicely, and it makes the forward query a full scan with a text match. That is fine at ten thousand units and unusable at two million, which is roughly four years of a modest line.
Consumption belongs in its own rows, one per component, indexed on the lot code and on the child serial. The forward query then becomes a lookup.
Serialised children and lots are different things
| Row label | Serialised child | Lot consumption |
|---|---|---|
| What is recorded | The child unit's own identity | A lot code and a quantity |
| Has its own history | Yes — full sub-tree | No — the lot is external |
| Forward query | Child serial → parent units | Lot code → all units that consumed it |
| Typical example | Stem sub-assembly built on your line | Gasket batch from a supplier |
Modelling both as one nullable pair of columns, with a constraint that exactly one is populated, keeps the two forward indexes separate and the query planner honest. Collapsing them into one text column labelled "component" is what forces a scan later.
Depth is part of the record
If a sub-assembly is serialised and built on your line, it carries its own genealogy, and the parent inherits the whole tree. An auditor will ask how deep the record goes, so the answer should be visible rather than inferred.
Cap the walk depth in the query. A cycle in genealogy data should be impossible — a unit cannot be its own component, and that is worth a database constraint rather than a code comment — but an unbounded recursive walk on bad data will take a production database down, and the fix is a bound, not optimism.
What every link must carry
- The parent unit and the component, as above.
- The BOM line it satisfies. Without it you cannot tell a legitimate substitution from a mis-build.
- The operation sequence it was consumed at. "Which operation fitted the bad bearing" is a real question during containment.
- The station and the operator. Sometimes the pattern is one operator on one shift.
- Both timestamps — device and server — like every other evidence row.
- A quantity, for lot links. Consuming 2 of a lot and 200 of it are different exposures.
The second forward axis people forget
Component lots are not the only thing that goes wrong. An instrument drifts, a fixture wears, a machine runs out of calibration between Tuesday's check and Thursday's. The question is then "which units did this device touch in this window", and it is not a genealogy query at all — it is a measurement query, and it needs its own index on device and time.
Systems that record only that a scan happened at a station cannot answer it. Recording which instrument produced each reading is what makes it a lookup.
A test worth running on any system you are evaluating
- 01Ask the vendor to show a forward trace from a lot code on a dataset with at least a million units, and watch the clock.
- 02Ask them to show the same query by instrument and time window.
- 03Ask what happens to the record when a component is substituted with an approved alternate.
- 04Ask to see a unit whose sub-assembly was reworked, and check whether the sub-assembly's own history is still attached.
Written by Dinesh Kumar G, ElectronIx, Coimbatore. If something here is wrong or incomplete for your process, tell us — we would rather fix it.