Posted on 2007-10-16
I have an inventory database in progress. I have 2 tables: assemblies and raw materials. The assemblies table and the raw materials tables could be related via a junction table (because any number of assemblies can share the same raw material). I understand this far.
BUT, I need to build functionality to allow assemblies to contain other assemblies plus raw materials. For example, a raw material might be a bolt. An assembly might be a nut/bolt set. Another assembly might be a bolt-nut assembly plus something else (either raw material or another sub assembly).
I know that I need assemblies to contain assemblies due to the cascading nature of how we update our parts--and Quickbooks Manufacturing edition does this and it's way more efficient than disallowing assemblies to be built with raw materials plus other assemblies.
What is the best way to do structure this in MySQL? If an assembly can contain other assemblies AND raw materials, how do I create the proper table structure for this to work? If it were just assemblies and raw materials--the junction table would work--but I don't know how to do it this way when it can be raw materials AND/OR other assemblies contained in an assembly.
PLEASE no generic links to database design websites.