0

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

robotHamster
  • 609
  • 1
  • 7
  • 24
  • The concept your trying to create is a Bill of Materials (BOM) it's a manufacturing process which takes raw materials and results in a finished good. Because ingot to Finished good is a Many-to-Many you need to resolve that relationship using a junction table which would contain the "materials" necessary to make the product (finished good) in question similar to a "recipie" in cooking In it you would list the materials needed with quantities, necessary to make the IBEAM. Table (Product_materials) with QTY and UOM. you may even have multiple recipes to make the same finished good. – xQbert Feb 13 '17 at 16:27
  • @xQbert So from what I understand, I would need another table (With its own primary key), and in this table I will have two columns (ingotID and ibeamID) and the entries in that table would be what I need to look at to trace an ibeam back to its ingots? – robotHamster Feb 13 '17 at 16:34
  • Close since you could have multiple formula's to make the end product, you need a formula ID. Technically you don't have to have the composite key since the formualID would be unique. and ProductID, materialId could just be foreign keys. – xQbert Feb 13 '17 at 16:38
  • @xQbert I think you're correct about the fact that I don't HAVE to have the composite keys. I haven't used any yet, because I came to the same conclusion. Knowing about them and how BOMs are structured was a great help – robotHamster Feb 13 '17 at 18:22

1 Answers1

1
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.

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Thank you! I think the composite key is what I was looking for! – robotHamster Feb 13 '17 at 16:37
  • 1
    I do suggest reading up on fields used in Bill of Materials as it can get quite involved if you're after full functionality of this kind of manufacturing processes. Here's one example: http://stackoverflow.com/questions/17651424/bill-of-materials-database-model – xQbert Feb 13 '17 at 16:40