BOM (Bill of material) how to Design database

I am trying to design a Database in Access of SQL that will allow me to import Multi-Level Bills of Material and keep it organized so I can do cost rolls and other analysis. I would also like a way to display the multi-level BOMs in a tree like structure using VB.NET , but I need a good table design first.  Does anyone out there have any experience in working with BOMs manipulation in Access, and it can interact with whole ERP system outside

Your help is appreciated.
Who is Participating?
Jeffr0Connect With a Mentor Commented:
The table structure for a tree could be done like this:

tblMaterials would contain your Materials list-- including all of your parts.  You need an autoincrementing MaterialID.  The table would contain things like a Cables, a Trackballs, a MotherBoards, etc.

tblKits would contain the "Headers" for your BOMs.  You need an autoincrementing KitlID, and a KitName column to name the grouping.  This table would contain the Item Groups-- like Computer Package A, Package B, etc.

tblBOMs would have the following columns:  KitID, MaterialID, BOMID, and ParentID.  All of these columns are integers and BOMID would autoincrement.  ParentID could be left null if it's a root item for the BOM.  If you want to add nodes to the BOM, ParentID would reference the BOMID that the node is a child of.

Doing it this way, you would have a Kit called "Computer" that has root BOM records (ParentID=Null) for "Monitor", "Tower", "Keyboard", etc.  Each of these records would have additional children records in tblBOMs to account for the parts that make up each of these components.

There are many ways to do this-- kits could be handled in a different way for example-- but I'm just explaining this one to get the idea of the data structure for the tree across.

I support an accounting package that has these features and is implemented in Access.  If you'd like for me to look at how they structured this, just say.
mrvanhieuAuthor Commented:
Thanks Jeffr0,
I've read you comment and design a database base on your structure like this:

      isProduct: it defines a Material is an assembly or not (it's not a leaf in BOM tree structure)

tblProducts: (All Assembly Items)
      ProductDesc: Description of Product
      BomID (PK) (references to ProductID)
      Level (redundant field - i'll explain later)
      ItemID (PK) (references to MaterialID)
      ParentID (PK) (references to MaterialID)

In this structure i define BOM as a tree, it can inherit other BOM. For example
      A has 2 children: B, C
      B has 1 child:  D
      D has 2 children: E,F
So, i define D as a new BOM with 2 childen. after that, B is defined as a new BOM also. Finally, A just define that it has 2 children B and C, it does not attach D,E,F in its BOM (this call inherit). So, field Level always =1 ===> redundant

- Do you have any ideas about my structrue?
- How can i display all Item in any Level when i dont have information about BOM's level?
- Do you have any advance structure about this? and its document?
- I've searching many days in the Internet, but i could not find any good document about BOM, so now i can not understand it cleary. Can you give me one?
- By the way, send me your structure that implemented in Access please, thank you
my Email:
I looked at our package.  It does this without the "tblProducts."  They have tblMMMaterials and tblBillOfMaterials.  tblBillOfMaterials has a subofID (to make the tree) and references a MaterialID.

I like what you've done with the isProduct boolean.  That way "BOMs" can be built with both single material items _and_ other  "BOMs."

The only changes I see that I would make are...

If the Material is a "product"... I'd want to know the ProductID.  So add that to tblMaterials.

And in tblBOMs, I'd Have ProductID be there and make the BomID an autoincrementing integer.

That gives us two entities: Materials and Products.  Products are a subset of Materials.  Products are made up of one or more Materials and/or Products.  The BOM tells us what makes up each product.

Note: there _is_ a way to do this to eliminate the tblProducts... but I like this way better-- it's a little more transparent.

** What do you mean by "How can i display all Item in any Level when i dont have information about BOM's level?"  Can you elaborate?

And regarding documentation... no we have none.  (Our package never was very well documented...!)  :/

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

PS  I believe the area of accounting that covers this is called "Cost Accounting."  It may also be called "Managerial Accounting."

I'll check my old text books tonight to see if there's any decent info there.
"Bill of Materials" does not show up in the glossary or the index of my text.

It is, however, mentioned in the section on "MRP"-- Materials requirements planning.

"MRP is a 'push-through' system that manufactures finished goods for inventory on the basis of demand forecasts.  MRP uses (1) demand forecasts for the final products; (2) a bill of materials outlining the materials, components, and subassemblies for each final product; and (3) the quantities of materials, components, finished products, and product inventories to predetermine the necessary outputs at each stage of production."

Any decent college accounting dept should have a Cost Accounting text with this stuff in it.

I also found this link which describes how one group implemented it:

You might find more interesting pages by Googling "mrp description bill of materials".

Let me know if you have any additional questions-- there's a lot of directions you can go with this!
mrvanhieuAuthor Commented:
Thanks for you help, Jeffr0
How's the BOM going?  Have you run in to any additional snags?
mrvanhieuAuthor Commented:
Thank you very much, Jeffr0, I'm appreciate about your help, i have had my own structure that suited to my project.

Thank you very much
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.