Our manufacturing business produce several product lines from several input materials. The material gets processed in phase 1 and results in two outputs, one of which is finished goods, the second then gets processed using a different method, and results in two products. Both of these products can either be sold as finished goods or can go through a third process to be refined further. The third process for each results in two other types of finished goods and that is it.
I want to be able to log info about each step in the process. I also want to know how much of the source material is being used in the processes and how much remains. Then I need to have a tally of finished goods that shows across all the processes, which finished goods (and how much) came out of that material source.
My initial thinking is that I have a input materials table with details on the materials purchased for processing. Each material purchase would have a lot# that can be carried throughout the processing steps so I know that finished goods X came from material lot#4 for example.
Then a second table for the first manufacturing process. Records there would link back to the materials table. We can log various attributes of this processing step in the table including the % results going to finished goods and how much will move to the next level of processing. A third table (linked by a new ID) would contain the next step in the processing pretty much same as the second table.
If I have a series of these tables, how do I link them all up so that the results in a table down the line can be indicated through a series of record numbers generated at each step in the production process?
Any help or example databases would be greatly appreciated. Happy to clarify if my explanation doesn't make sense.