Model Data Structure for Database SQL of Bill of Materials

sorry for my language....

I have to build bill of materials database model but i have more than 1 level of hierarchy. Example:

  1. Article-A
    • Article-B
    • Article-C
      • Article-D
    • Article-E

I have seen in various posts that the solution is with the use of two tables, one of the product and another that contains the composition with column ID parent and id child. But if the hierarchy can have more than two levels, even 4, how can I search from the database for article A through article D if it does not have a direct relationship except through article C ?? Furthermore, the composition table will have a number of tuples that will easily reach millions of tuples, could this lead to problems of slowness?

Using MySQL I tried to use the composition as if it were a JSON type column, populating the composition dynamically for each article and thanks to which I can also select within that column. Have you ever tried to use it or is this solution not convenient in any case?


Read more here:

Content Attribution

This content was originally published by ITobeI at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: