What is the correct data model for creating a Bill of Material

Posted on 2001-06-19
Medium Priority
Last Modified: 2008-10-06
What is the correct data model for creating a multi level Bill of Material?

Question by:rpremo
  • 3
  • 2
  • 2

Expert Comment

ID: 6206423
Hi rpremo,

In order for us to give you a specific answer, you have to give specific needs.

In general, the concept is to maintain unique table for every data that you deal with.

For example:

Table Customers
CustomerID              'Set as primary key
and so on

Table Manufacturers
ManufacturerID        'Set as primary key
and so on

Table Materials
MaterialID            'Set as primary key
ManufacturerID        'link to ManufacturerID in Table Manufacturers
and so on

Table Orders
OrderID               'Set as primary key
CustomerID            'link to CustomerID in Table Customers
MaterialID            'Link to MaterialID in Table Materials
and so on

Table Invoices
InvoiceID             'Set as primary key
OrderID               'Link to OrderID in Table Orders
MaterialID            'Link to MaterialID in Table
Price                 'calculated QuantityShipped * MetrialID(Price)
and so on

This way you will not keep duplicates of data.

Hope this helps,


Author Comment

ID: 6206529
I guess my original question was not very detailed.  Let me explain exactly what I am after.

I work for a manufacturing company where just about evrything is driven by a Bill of Material (BOM).  A BOM is simply a list of parts that make up an assembly.  The catch is that an assembly can be made up of other assemblies.  This would make the product structure multiple levels deep.  Therefore the BOM is referred to as a Multi-level BOM.  Most MRP, ERP and PDM have the capability of handling multi level BOM structures, but they are not very flexible and it takes an act of GOD to get anything configured or changed.  That is why I am looking to develop a simple BOM table in Access or SQL that I have control over.  The structure that I have so far is as follows:

1.  Parts Master Table:

Part Number (key)
Part Description
Other part Attributes

2.  Parent Child Table:

Index (key)
Parent Part Number - Must be in the parts master table to be a record.
Child Part Number - Must be in the parts master table to be a record.

I can then create queries that will give me a single level BOM, but I don't know where to go from here.  

Accepted Solution

abaldwin earned 400 total points
ID: 6206655
I live in a manufacturing environment as well.  The dreaded BOM is the holy grail of manufacturing.  

Our manufacturing/accounting system is where the bom's are created and can be looked at online.  Of course their reports and display abilities are lacking so it was up to me to create something more presentable.

One of the keys here is to know how many levels you must go down in the structure.  We have a max of 11 levels down per part.

Our system has the same table structure you have.

1 Master table.
2.  Structure table containing the following
   ParentItem Number
   Sequence Number
   Componenet(child) number
   Qty per parent

To get an indented costed bill you are going to have to write a large piece of code.

Basically do the following.  (I do this in the front end of a two-part access database).  
Use a temp table to hold the structure.
When the user puts in a part number then you check to see if the part exist as a parent in the structure table.  If it does NOT then dispaly a message of no structure.

If it does exist then  Write the parent part and description and what not into the temp table.

use a recordset to return the parts that have this part as the parent.

Loop through that recordset to get the structure for each of the componenents.

I can probably send you some sample code.  Posting it here would probably not be beneficial to anybody.  

My code eventually generates an indented - costed BOM .

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Expert Comment

ID: 6206675
Hi rpremo,

Since you are going to develope your own BOM, then I think that it whould be wize to define:

1. The Goels
What your customer wants to accomplish with your app (e.g. automation, fllow control, damage control, notifications upon errors in the work fllow, etc.).

2. General Questions regarding the scope of your app
a. Do you have to consider the inventory when you process the BOM, and if you do, how should BOMs with unavailable materials be delt?
b. How the materials get to the assembly line (Back-To-Back, Inventory, Other assembly lines - as you mentioned, etc.).
c. What are the inputs and outputs of the Assembly process?
d. What is the Assembly process?
e. Do you have to consider package (which should be a part of the BOM) and shippment?
f. Are there any milestones in the assembly process which you have to monitor and alert upon.
g. What are the entities that your app should take care of?

and so on....

After we'll know all of that, it will be allmost a stright-forward process to specify the table, relationships and other things needed for developing the app.

One more thing that I'd consider, is the number of users that will eventualy use your app. If there will be 5 or less users, then MS Access is the right tool. Otherwize, I suggest that you'll use MS Access as your front-end, and some SQL server as your back-end.

Hope this helps,


P.S. Sorry that I gave you more questions then answers...;-)

Author Comment

ID: 6206679
I would really appreciate it if I could take a look at the code you used.  It sounds to me like you have all ready accomplished what I am looking for...

my e-mail is rpremo@wabtec.com

Author Comment

ID: 6206792
The information Andy provided was exactly what I was looking for.

Expert Comment

ID: 6206802
What is the name of your manufacturing software package?


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

624 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