BOM for sub-assemblies
I would appreciate advice on Best Practice for generating a Bill Of Materials for Products that include sub-assemblies.
I have a Products Table, with records for products such as:
I have a Product Items Table with the ingredients of each product. For example, the Product 'Cake' could have:
The solution accepts a Sales Forecast for the range of Products and generates the appropriate MRP (Materials Requirement Plan) down to Ingredient level. That all works perfectly.
The next stage that I am asking for advice on is that an 'Ingredient' could be 'Caramelised Chestnuts'. That Ingredient is a sub-assembly - it requires the Purchasing Department to buy the ingredients 'Chestnuts' and 'Sugar'.
This is a really common requirement (the automotive industry must be full of such requirements, for example), so I am reluctant to try to implement a method without first checking what the current industry modus operandi is for such a calculation. Can anyone offer advice on the principle I should be using to calculate down to Ingredient level? I would like the solution to be extensible, obviously - hopefully working even if 'Caramelised Chestnuts' had an Ingredient that was itself another sub-assembly.
I have developed a 'Proof Of Concept' file that loops through Sub-assembly levels, but it is very complex, and I fear I have missed some fundamental principle that has long-before been established.
All suggestions appreciated.