Solved

BOM (Bill of material) how to Design database

Posted on 2004-04-21
10
5,402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

630 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