Solved

BOM (Bill of material) how to Design database

Posted on 2004-04-21
10
4,639 Views
Last Modified: 2010-08-05
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.
0
Comment
Question by:mrvanhieu
  • 5
  • 3
10 Comments
 
LVL 2

Accepted Solution

by:
Jeffr0 earned 125 total points
ID: 10879261
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.
0
 

Author Comment

by:mrvanhieu
ID: 10880423
Thanks Jeffr0,
I've read you comment and design a database base on your structure like this:

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

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

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: mrvanhieu@yahoo.com
0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10881289
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...!)  :/

0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10881386
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.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Expert Comment

by:Jeffr0
ID: 10892282
"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:

http://www.pcmrp.com/manual/wmbom.htm

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!
0
 

Author Comment

by:mrvanhieu
ID: 10895133
Thanks for you help, Jeffr0
0
 
LVL 2

Expert Comment

by:Jeffr0
ID: 10942839
How's the BOM going?  Have you run in to any additional snags?
0
 

Author Comment

by:mrvanhieu
ID: 10967199
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
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now