Database Structure Manufacturing

Discussion created by oman on Oct 23, 2016
Latest reply on Oct 25, 2016 by BillisSaved

Hi all, I'm setting up a database to monitor manufacturing of several products. My tables are:






Each Process has only one Input.

Each Process can have many Products.

Here is the complicating factor: Each Product can either be an end product, or it sometimes has to be Processed again.

When something needs to be Processed multiple times, I need to be sure that the original unique Input ID number is retained on the final Product.


Right now, I have it setup so that you create a Process record, and assign either an Input or a Product to it. Then I have a portal on that layout that I can create new records in the Products table based on what happens in that Process.


For the Process records that have an Input assigned, all the data is there. For the Process that had a Product assigned, it can't look back at the Input that created that Product to give me its original data (ID number and couple other fields).


Can anyone recommend from a structure standpoint the best way to solve for this? Could I just have an Inputs and Processing table that fed back into Inputs, but permanently stored the original Input Unique ID in another field?


Any help pointing me in the right direction would be appreciated. Here is a rough summary of my tables and the fields linking them. Products 2 is a second instance of the Products table.





InputsProcessingProducts 1Products 2
pk_Input IDpk_Processing IDpk_Product IDpk_Product ID
fk_Input IDfk_Processing IDfk_Processing ID
fk_Product ID