Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

BOM (Bill of material) how to Design database

Posted on 2004-04-21
10
Medium Priority
?
5,625 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 500 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

722 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