My question might be confusing but I will edit it accordingly when I find better words. This is not a direct coding question, but more of an advice on how to approach this design problem.
In our DB model we store all products used in manufacturing (components, assemblies, material) and finished products for sale in the same table "products". Each product we sell and assemblies have their BOM saved in separate pivot table "product_boms". It's kind of like this:
Table: products
- id
- sku_number
- name
- type
- ...
Table: product_boms
- id
- product_id (FK to products table)
- child_product_id (FK to products table)
- quantity
As you see table "product_boms" defines list of child products for certain product. This is part of entire BOM for this product. Part because a child product here can have further child products defined in the "product_boms" table.
I need to create a tree view of entire BOM for certain product. This is easy to do in a code, but results in many SQL queries as I need to look for every child product if it has further children... I am now looking into the SQL and if it's possible to get this out with a single query? I know this would be possible with stored procedure, but can't use those...
Final result (BOM) for product named "product_x" would look like this:
- product_x
- component_a
- component_b
- assembly_a
- material_a
- component_c
- component_d
- assembly_b
- material_b
- component_e
- component_f
- component_g
I also have option to redefine DB model in order to save BOM more efficiently and better for getting it out of DB. Open to any suggestion.
