2

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.

Matt Field
  • 25
  • 5
  • You database model clearly needs to store hierarchical data. MySQL 5.x does not have the necessary tools for this kind of data. Make sure you upgrade to MySQL 8.x, (or switch to PostgreSQL). – The Impaler Jan 10 '19 at 17:51
  • I wasn't aware XAMPP didn't include the latest version of MySQL. Thanks, I will investigate further. – Matt Field Jan 10 '19 at 18:59
  • Possible duplicate of [What are the options for storing hierarchical data in a relational database?](https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – philipxy Jan 10 '19 at 19:42
  • Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep. Make your post self-contained. – philipxy Jan 10 '19 at 19:43
  • philipxy - Please can you enlighten me regarding how I can provide an ERD in text only? And I apologise, I didn't realise a legend would be required for FK (Foreign Key), PK (Primary Key) & U (Unique / Indexed). – Matt Field Jan 10 '19 at 21:55

1 Answers1

0

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.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • Thanks for your advice and feedback. Re: "tbl_" a) That's just how I was taught to label them 15 years ago when learning ASP in Sixth Form and it's stuck as habit and b) since I am using the [UserSpice](https://userspice.com/) project which requires around ~20 tables in my MySQL database, it is actually fairly useful for grouping my core system tables together in PHPMyAdmin/PHP Storm. – Matt Field Jan 10 '19 at 21:56