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

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

rpremoAsked:
Who is Participating?
 
abaldwinConnect With a Mentor Commented:
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 .

Andy
0
 
NosterdamusCommented:
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
CustomerName
CustomerDescription
.
.
and so on

Table Manufacturers
===================
ManufacturerID        'Set as primary key
ManufacturerName
ManufacturerAddress
.
.
and so on

Table Materials
===============
MaterialID            'Set as primary key
MaterialSN
ManufacturerID        'link to ManufacturerID in Table Manufacturers
MaterialPackPrice
QauntityPerPack
.
.
and so on

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

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

This way you will not keep duplicates of data.

Hope this helps,

Nosterdamus
0
 
rpremoAuthor Commented:
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:

Contains
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.  
 
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
NosterdamusCommented:
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,

Nosterdamus

P.S. Sorry that I gave you more questions then answers...;-)
0
 
rpremoAuthor Commented:
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
0
 
rpremoAuthor Commented:
The information Andy provided was exactly what I was looking for.
0
 
abaldwinCommented:
What is the name of your manufacturing software package?

Andy
0
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.