1 2 3 Previous Next 38 Replies Latest reply on Oct 25, 2016 3:52 PM by BillisSaved

    Database Structure Manufacturing

    oman

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

       

      Inputs

      Processing

      Products

       

      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.

       

      Thanks,

      Oman

       

      InputsProcessingProducts 1Products 2
      pk_Input IDpk_Processing IDpk_Product IDpk_Product ID
      fk_Input IDfk_Processing IDfk_Processing ID
      fk_Product ID
        1 2 3 Previous Next