Bill of materials database model

Question

I'm currently working on a project which should help us with our inventory control as well as our purchases to assembly our final product.

We're in the stage of modeling our database and one of the requirements is to generate a BOM (Bill of materials).

I've read this thread and found an example data model for BOM:

conceptual data model and physical data model

but i'm not sure I fully understand.

Our final product consists of a couple of sub-assemblies, so each sub-assembly is a row in the product_hierarchytable, and the final product also a row in that table. Each sub-assembly is made out of seperate (atomic) parts and each part is identified in a table tpart (each part has manufacturer field, minimum reorder quantity and other specific fields).

When generating a BOM all separate parts should also be included, so it's not fully clear to me how to model our database:

1. a seperate part is a row in product_hierarchy which will never be one's 'parent' (the table tpart is no longer needed)
2. an N:M relationship between product_hierarchy and tpart: each unit has several parts; each part can belong to several units

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

Any input is appreciated! Thanks!

Answer 1

The models you linked fail to address some major properties BOMs normally have:

• Parts and sub-assemblies can be reused. For example, it is common for a same kind of bolt to be used in many assemblies.
• There needs to be a BOM-specific quantity. For example, it's important to know that one assembly needs (say) 50 bolts, but the other assembly might only need 30 of the same kind of bolt.

Here is a simple model that addresses these concerns:

The PART table is either a top-assembly or a sub-assembly or a leaf part. It uses a publicly known "part number" to identify its rows, which is not actually a number at all and can contain non-numeric characters.

The BOM table models many-to-many relationship of PART by itself. It's really no different from any other junction table, except both "endpoint" tables are actually the same table. This way, one sub-assembly or part can be reused in multiple parent assemblies.

On top of this model, you can fairly naturally add things like "drawing position" or "unit of measure" (e.g. a paint can be part of BOM but is measured in "kilograms" instead of "pieces").

---

There are more things you might want to do in reality, but are beyond the scope of a simple StackOverflow post like this.

For example:

• How do you handle change? Do you have part versioning? Do you version the BOM itself?
• Different suppliers might use different part numbers for the essentially same part.
• You might want to keep track of "sites" (warehouses or factories) where parts are stored or assembled/produced. A "same" assembly might even have slightly different BOM for different sites.
• You might want to differentiate between "made" and "purchased" parts.
• Do you have a lifecycle workflow (approve/release/obsolete)?
• You might want to store user-defined attributes. Attributes typically includes things such as mass, volume and material, but there may be many others that cannot be foreseen in advance.
• You might want to connect the physical CAD models to the data in the database.
• You might want to disallow certain users from doing certain changes to the database (e.g. procurement department shouldn't be able to change the assembly structure, at least not without supervision).
• Etc, etc...

These are some of the reasons why real PDM systems tend to be complex. If you actually need all that functionality, you should probably consider using a commercial product instead of trying to re-implement it yourself...