Solved

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

Posted on 2001-06-19
7
852 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
  • 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

914 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now