Solved

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

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

0
Comment
Question by:rpremo
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
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
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
 

Author Comment

by:rpremo
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:

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

Accepted Solution

by:
abaldwin earned 100 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 .

Andy
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 7

Expert Comment

by:Nosterdamus
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,

Nosterdamus

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

Author Comment

by:rpremo
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
0
 

Author Comment

by:rpremo
ID: 6206792
The information Andy provided was exactly what I was looking for.
0
 
LVL 4

Expert Comment

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

Andy
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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