BOM - Bill of Materials

[mihmih]

Hi,

BOM - I think how to do it in my database.

A bill of materials or product structure (sometimes bill of material, BOM or associated list) is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts and the quantities of each needed to manufacture an end product.

For example product1 include:

part1, 1pc
part2, 2pc
assembly1, 2pc
assembly2, 1 pc

Assembly 1 include:

part1, 2 pc
part3, 2 pc
assmebly3, 1 pc

Assembly2 iclude:

part4, 5 pc

Assembly3 include:

part1, 6 pc
part5, 2 pc

And at the finish i would like to have a list of parts(with quantity), that i need to product1. I'm talking about the structure of the tables - which will be the best? It can be assumed that the biggest level of complexity is 4 (sub - sub - sub - assemblies). Maybe one table [parts(also assemblies)] and one junction table[parts-parts] and big UNION query? Thanks for any help, the design of this structure is the key ...

[Villeroy]

viewtopic.php?f=9&t=86366

[mihmih]

Thanks Villeroy, this example is very nice and help me a lot. But my problem is: there can be a product, for example Y1 , which also consists of 2 other products (Z1 and Z2). One more level of complexity.

I have no idea how to convert this most important query:

Code: Select all

SELECT "PRODUCTS"."ID" "PROD_ID", COALESCE ( "ASSEMBLIES"."CID", "PRODUCTS_PARTS"."PART_ID" ) "PART_ID", COALESCE ( "ASSEMBLIES"."CNT" * "PRODUCTS_PARTS"."CNT", "PRODUCTS_PARTS"."CNT" ) "CNT" FROM { oj "ASSEMBLIES" RIGHT OUTER JOIN "PRODUCTS_PARTS" ON "ASSEMBLIES"."PID" = "PRODUCTS_PARTS"."PART_ID" } JOIN "PRODUCTS" ON "PRODUCTS_PARTS"."PROD_ID" = "PRODUCTS"."ID"

because the products of the lowest level in hierarchy (Z1, Z2) also would like to have in the table PARTS.

[eremmel]

Two possible solutions:
1: When a PRODUCT has PRODUCTS_PARTS that are by them selves also PRODUCTS you need to generalize these two tables into one table and add a marker-field to indicate if a PRODUCT is a end product or not.
2: Replace the PRODUCT_PARTS in this query with a view that UNIONs data from PRODUCT and PRODUCT_PARTS.

P.S.
Note that you need recursive SQL with CTE so solve this kind of problems for variable nesting levels. It is part of ANSI SQL and supported by HSQLDB, PostgreSQL, H2, Firebird, DB2, Microsoft SQL Server, Oracle 11.2+. MySQL needs a different approach.