14

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

enter image description here

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!

Community
  • 1
  • 1
user729103
  • 631
  • 2
  • 10
  • 24
  • My first thought is that maybe this lacks a level of abstraction. For instance, in a bathroom you might know that you're going to want to provide a bath, a basin, and a WC without necessarily specifying what those items are - and allowing the possibility that they might change. – Strawberry Jul 15 '13 at 09:59
  • Do you already know which RDBMS (Oracle, SQLServer, MySQL, etc) this is going to be implemented on? (There's an issue with BOM-style data in MySQL that is not such an issue with many other RDBMSs.) –  Jul 15 '13 at 10:12
  • @MarkBannister Most likely MySQL, what might that issue be then? – user729103 Jul 15 '13 at 11:09
  • 3
    The issue is that MySQL doesn't support recursive Common Table Expressions, or anything similar (such as Oracle's `connect by` syntax) - so if you try to model a hierarchical structure by having a parent product code in the same table as the product code (known as the http://en.wikipedia.org/wiki/Adjacency_list_model ), you won't be able to query the full table hierarchy in a single query. If you are committed to using MySQL then you should investigate the http://en.wikipedia.org/wiki/Nested_set_model , but I would strongly recommend considering another RDBMS, such as PostgreSQL. –  Jul 15 '13 at 12:10

2 Answers2

28

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:

enter image description here

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...

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Thanks for your answer. Concerning: 'The models you linked fail to address some major properties BOMs normally have', I thought so and had come up with the same idea of the 'BOM' table also, but I wasn't really sure that was the way to go. I can't immediately see however what's the advantage of using 'part' as both top-assembly, sub-assembly and leaf. We indeed already have 'part numbers', but not so for our assembly parts. As for your 'extra' remarks: Some of those will indeed be needed (like user privileges) and we are also considering using an existing (open source or not) solution. – user729103 Jul 15 '13 at 11:40
  • @user729103 Well, you need one table to make PART_NO unique - you can't (easily) make PART_NO unique across multiple tables. However, you can "inherit" specific "sub-classes" from the PART table if you wish... – Branko Dimitrijevic Jul 15 '13 at 17:21
  • @BrankoDimitrijevic The BOM structure here works for sub-assemblies and leafs, but I don't see how it works for the top-assembly. The top-assembly does not have a PARENT_PART_NO. We cannot set that field to NULL since it is in the PK. Do you have a proposed way of solving that? – Ben Sep 16 '15 at 18:46
  • 1
    @Ben You simply omit the entire row. To put it slightly more precisely: for the top assembly, you will have zero rows in the BOM table with CHILD_PART_NO equal to that assembly's PART_NO. – Branko Dimitrijevic Sep 16 '15 at 20:50
  • @BrankoDimitrijevic Thanks! I am using this in production now. – Ben Mar 31 '16 at 16:26
2

It sounds like you might have two kinds of products. One is the 'atomic' part and the other is the 'compound' end product. I would store those in two separate tables, since they both need different pieces of information.

The CompoundProduct table will need a child table as well that links the part to the end product.

If you like, you can still have an 'abstract' product table that contains all products: parts as well as end products. In this table you can store a code and the name, and it is convenient to have one table of products that can be bought and displayed on an order or invoice. Both the Part table as the CompoundProduct table can then have a product id that is a foreign key to the abstract product table, but is unique in Part and CompoundProduct as well.

So in general, this database scheme is powerful and flexible, but I think it is not complete enough, or maybe too flexible for your want.

Community
  • 1
  • 1
GolezTrol
  • 114,394
  • 18
  • 182
  • 210