I'm building am app which contains a requirement to store BOM information. I have an idea how I *could* store the info but what to ask whether this is the optimal way - so any input would be appreciated.
The BOM's are for unique,custom designed and built parts, and hence do not have assigned part numbers (bar the raw materials), hence I thought of using the order number (for the finished product - since it is unique) as the primary identifier for the BOM, and then add a numbered suffix to the end to reference the next level down to reference the component parts, and so on down the levels...
E.g. if order no is 234, a sample structure might be:
Then any parts which do have specific part numbers (i.e. the purchsed items/raw materials) could be added in an optional identifier field within the data tables.
The idea then would be to store each product level in a seperate table (they could all be stored in the same table, but I prefer the idea of separating them). Then if a union query was performed, and a select where ID=234*, and sort on ID, the result would be an accurate BOM. Then a bit of VBA jiggery pokkery would allow indentation of lower levels to generate an easy to understand diagram.
I spent a very long time mulling over this, and this was the best solution I could come up with, however I am still concerned that there is probably a better solution. If anyone knows a better solution I would be interested to hear it.
Thankfully, the number of levels is only small, with at most 2/3 (at present) beneath finished item - hence the reason I feel it possible to treat each level independently - if there were many more it would require general code to apply to limitless levels - but that is a task I fear greatly!
There isn't a set number of levels, the specific structure depends on the given case. What I think you're asking (though I'm probably wrong) is whether all parts go down to the same level - which alas they don't. And, as you have probably gathered, the location and number of branches varies - basically any structure is possible, but (so far) no products have branched down more than 3 levels. But within that limit there are countless variations, meaning that a standard/proforma isn't really feasible.
Anywho, you could use a table for each level in this scenario, assuming there are a MAX of three tables.
Then you could use a table for each level and link them by part number.
Another thought would be a a table for the part, and then another table for partDetail. In the partDetail table, you would use part_id as a foriegn key. You could also define two more fields, one for level, and one for part number. I don't uynderstand what the second grouping of numbers means, but you should be able to adapt this structure to account for that So for instance, given your part number of 234: