Solved

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

Posted on 2001-06-19
7
842 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 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