Bill of materials database model -

i'm working on project should our inventory control our purchases assembly our final product.

we're in stage of modeling our database , 1 of requirements generate bom (bill of materials).

i've read this thread , found example data model bom:

conceptual data model , physical data model

but i'm not sure understand.

our final product consists of couple of sub-assemblies, each sub-assembly row in product_hierarchytable, , final product row in table. each sub-assembly made out of seperate (atomic) parts , each part identified in table tpart (each part has manufacturer field, minimum reorder quantity , other specific fields).

when generating bom separate parts should included, it's not clear me how model our database:

1. a seperate part row in product_hierarchy never one's 'parent' (the table tpart no longer needed)
2. an n:m relationship between product_hierarchy , tpart: each unit has several parts; each part can belong several units

i'm leaning towards second alternative, since part total different entity (has price, several possible suppliers, ...) whereas assemblied entity has no external (as in: outside our company) properties.

any input appreciated! thanks!

the models linked fail address major properties boms have:

• parts , sub-assemblies can reused. example, common same kind of bolt used in many assemblies.
• there needs bom-specific quantity. example, it's important know 1 assembly needs (say) 50 bolts, other assembly might need 30 of same kind of bolt.

here simple model addresses these concerns:

the part table either top-assembly or sub-assembly or leaf part. uses publicly known "part number" identify rows, not number @ , can contain non-numeric characters.

the bom table models many-to-many relationship of part itself. it's no different other junction table, except both "endpoint" tables same table. way, 1 sub-assembly or part can reused in multiple parent assemblies.

on top of model, can naturally add things "drawing position" or "unit of measure" (e.g. paint can part of bom measured in "kilograms" instead of "pieces").

---

there more things might want in reality, beyond scope of simple stackoverflow post this.

for example:

• how handle change? have part versioning? version bom itself?
• different suppliers might use different part numbers same part.
• you might want keep track of "sites" (warehouses or factories) parts stored or assembled/produced. "same" assembly might have different bom different sites.
• you might want differentiate between "made" , "purchased" parts.
• do have lifecycle workflow (approve/release/obsolete)?
• you might want store user-defined attributes. attributes typically includes things such mass, volume , material, there may many others cannot foreseen in advance.
• you might want connect physical cad models data in database.
• you might want disallow users doing changes database (e.g. procurement department shouldn't able change assembly structure, @ least not without supervision).
• etc, etc...

these of reasons why real pdm systems tend complex. if need functionality, should consider using commercial product instead of trying re-implement yourself...