Product Lineage db

[ct]

We need to put together a database that tracks product
development and there are several points in the process
where the LotNumber will get split and then the new
LotNumber(s) (and possibly the original) will go on to the
next phases(testing, additional processing, additional
testing, assembly,etc). In the end we need to trace back
through this lineage to the original lot. I'm thinking
that each phase(split) requires a different table with the
LotNumber and an OriginalLot as the links but I'm really
struggling with how I will pull this altogether? Does
anyone know of any sample databases with similar
requirements that I might get some ideas from? Thanks.

 

[Allen Browne]

If the LotNumber can spawn children an unpredictable number of times, your
LotNumber table could have a field for:
DerivedFromLotID
which store the primary key number of the record in the same table that the
record derived from.

The table is joined to itself. In code, it is possible to follow that
recursively up the tree until you reach the record where DerivedFromLotID is
Null, which is assumed to be the top of that tree.

To research this kind of structure, search for "Bill of Materials". Starting
point (including downloadable example):
http://www.mvps.org/access/modules/mdl0027.htm

Although it is relatively straightforward to do this in code, tracing these
trees in SQL is messy. Some links:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.devx.com/premier/mgznarch/vbpj/2001/07jul01/sqlpro0107/rein0107/rein-1.asp
http://www.dbmsmag.com/9603d06.html