I’m creating quoting software for the manufacturing business I work for and I’m unsure how to handle assemblies within my MySQL database structure as they are a little like a Russian Doll.
Here’s a rough ERM and a simple example:
- Three small parts (2×A & 1×B) are welded together to form sub-assembly C. This is easily handled in the “PartAssem” table
- Sub Assembly C is then a piece of Assembly D
- Assembly D is then used in larger Assemblies, like E.
In this case the assembly portion is only two levels deep, with only Assembly D being both a “parent” and “child” assembly, but there will be a lot of cases for larger assemblies where this hierarchy will be much deeper.
Obviously you can’t have a single UID being used as two separate foreign keys in another table as I showed. As most of the time I’ll be querying for the Parent ID, I could just establish the SubAssemChildID as the foreign key, but I fear this may cause complications and/or I am missing an alternative solution.
Thanks for any help you can offer.
Advertisement
Answer
You’re on the right track. What you need to do next is understand how to query a recursive structure in SQL using Common Table Expressions. Once you get a toy example working, try it with your own tables. It’s not the easiest thing to understand at first, but once you get it you may be surprised how useful it is. On a good DBMS implementation, you may also be surprised how quick it is.
While I’m here, two opinions. First, you don’t need “tbl_” prefixes on your names. Of course it’s a table; what else would follow FROM? And, if you have a choice, check out Postgres, which is well regarded among SQL geeks. For one thing, date arithmetic actually works, and February 31 is an error.