A normalisation question

[mike60smart]

Thank you all for your help so far - I hadn't had chance to come back to look at all your input until now.
Kens BOM db does do what I'm wondering if I should change my db to - so it is certainly possible. My key question however is whether it is worth the work of re-jigging everything? Will it improve the overall efficiency of the db? Or is there a union query solution for the delivery note that is sufficient?
Maybe I would be better sorting out my look-ups in the first instance?

I would go with Ken's solution and import all your data into his table structure.

 

[Ken Sheridan]

Thank you all for your help so far - I hadn't had chance to come back to look at all your input until now.
Kens BOM db does do what I'm wondering if I should change my db to - so it is certainly possible. My key question however is whether it is worth the work of re-jigging everything? Will it improve the overall efficiency of the db? Or is there a union query solution for the delivery note that is sufficient?
Maybe I would be better sorting out my look-ups in the first instance?

The simplicity of the adjacency list model on which my demo is based should make it relatively easy to populate the PartStructure table, which is the heart of the database. Having created the Parts table by inserting a row for every product assembly, sub-assembly, and atomic base part, essentially, for each product you then start by inserting one row for the product itself and for each assembly which makes up the product, along with any atomic base parts which are used directly in the product other than as parts of assemblies. You then do the same for each sub-assembly used by the assemblies, and then for every sub-sub-assembly used by the sub-assemblies, and so on until you get to the point where there are no further sub-assemblies to be added.

There's one thing I should mention about my demo. Originally I'd included only one product, but later I added a second one. However, for some reason which escapes me now, I gave them product number values of 0 and 1. I've noticed however that the Add New Part form's product number control has a default value of 0. Consequently when the form is opened its subform shows the components of the product with a product number of 0. In the Parts table the product numbers should be changed to 1 and 2 to avoid this confusion. Cascade updates are enforced, so the changes will automatically be made to the relevant rows in the PartStructure table. The database will then need to closed and opened again to generate the BoM using the new values.

 

[Mark_]

Thank you all for your help so far - I hadn't had chance to come back to look at all your input until now.
Kens BOM db does do what I'm wondering if I should change my db to - so it is certainly possible. My key question however is whether it is worth the work of re-jigging everything? Will it improve the overall efficiency of the db? Or is there a union query solution for the delivery note that is sufficient?
Maybe I would be better sorting out my look-ups in the first instance?

I think you can answer this as soon as you look at real world situations in your industry.
How often do you have a case where a bottom level item (bolt, nut) is also assembled as "Bolt and Nut".
How often do you then use "Bolt and Nut" in a larger sub-assembly?
How often does this larger sub-assembly become part of an even larger sub-assembly and will this work in your existing format?

The point your design fails to support real world use is often AFTER the point you should have changed.

 

[Ken Sheridan]

One point of clarification perhaps worth making is that atomic base parts need not, and in most contexts will not, necessarily be physically atomic. In the manufacturing company in which I worked early in my career a distinction was made between production inventory and general inventory. The base parts of the former were small assemblies which were ordered in as such, not as their constituent parts. These would be the smallest components in any bill of materials. General inventory comprised everything else used in the day to day operation of the company, and was subject to normal inventory management.