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.
|Inputs||Processing||Products 1||Products 2|
|pk_Input ID||pk_Processing ID||pk_Product ID||pk_Product ID|
|fk_Input ID||fk_Processing ID||fk_Processing ID|