Search code examples
mysqldatabasevb.netrdbms

MySQL: Linking a column in one table to two entries of the same column in another table

What I'm trying to do is the following: suppose you have a 3 step manufacturing process:

ore ----> ingot -----> I-Beam

Of course, every I-Beam is made out of more than one ingot. If I have a table of ingots and a table of I-Beams, it can be made of of two, three (all the way up to let's say 6)

How do I go about making that relationship?

I'm relatively new to RDMS and MySQL, so I apologize ahead of time if the question is misplaced, not clear, or does not make any sense

Solution 
  • Product_materials table
FormulaID  composite key  (though this alone would be unique)
ProductID  composite key  
MaterialID composite key
Material_QTY
Material_UOM
Product_QTY
Product_UOM    
InstructionID FK to steps to follow for manufacturing etc.

    Note you could have multiple formulas for the same product and material combination. Implying there's more than 1 way to make an "IBEAM" that has the same SKU.

    So in your example:

    we have a formula for materialID of ORE which with specific qty and UOM we use to make the product ingots.

    Then we have a formula for material ingots which with a specific qty and uom we use to make the product ibeam.