I have a products table that contains chemicals. Some of the products are mixtures and are made up of other products. Also mixture chemical products may have more than one formula. For example:
ProdID 2006 has one formula that consists of ProdID 15010(1.80units) and ProdID 7006(18.00 units). ProdID 2006 also has another formula that will make the same product, it is made up of ProdID 15034(1.80 units) and ProdID 23518(18.00 units). There are two forumlas to make the product 2006. Furthermore I need to create a workorders table that stores orders for creating the product. I am having a difficult time with the design. I have tried a bill of materials approach but I need to consider the fact that there is more than one formula. Also coming up with a key that links to the work orders table is causing me trouble. I would appreciate any ideas... Thanks
I think you'll need an associative entity to support the multiple formulas
As stated in this reply title, I'm thinking all you'll need is an associative (bridge) entity that ties the many-to-many relationships together. I'll put something together via draw.io if you need me to show you the details. Just let me know if you need it.