0

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?

Thanks

Shadow
  • 33,525
  • 10
  • 51
  • 64
ITobeI
  • 1
  • The duplicate question and answers to it list the common options for storing hierarchical data in a relational database. You need to choose one of the methods based on your requirements. All solutions have pros and cons. If you have mysql v8.x, then adjacency list traversal is easy with recursive CTEs. However, each solution discussed in the duplicate question enables you to traverse the entire tree, but the exact method is different for each data model. – Shadow Apr 08 '21 at 11:50
  • Btw, table-per-hierarchy is a class inheritance representation technique (representing an OOP model in an rdbms), however what you need is storing of hierarchy of items. – Shadow Apr 08 '21 at 11:54
  • If you want to go with the adjacency model described in your question, then the second duplicate shows you how to traverse the tree in various mysql versions. – Shadow Apr 08 '21 at 11:58

0 Answers0