0 Replies Latest reply on Apr 24, 2016 8:30 AM by oman

    How should I structure multi-step manufacturing database?

    oman

      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.

       

      Thanks!