Solved

Best way to store a multi-level Bill of Mat'ls in a RDMS? In Access and SQL Server, or other easy way

Posted on 2004-04-01
12
3,390 Views
Last Modified: 2013-11-23
There seems to be an evolution of data storage

Flat file
Rational Database
??? (OORDMS, I think)


It would be nice to have a database format that supports n-Tables deep

What Do You Think?

0
Comment
Question by:winsupplychain
12 Comments
 

Author Comment

by:winsupplychain
ID: 10738135
The data storage should be clean, standardized, simple
0
 

Author Comment

by:winsupplychain
ID: 10793801
no comments yet?!?
0
 
LVL 2

Accepted Solution

by:
JaniceLaw earned 100 total points
ID: 10805380
Can you clairify the question? When you say multi-level bill of materials of n-level deep, is there a requirement of what type of DBMS this have to be implemented on?

Anyway as an example, in a RDBMS you can have a ParentID field in the bill of materials master table and build it as a single linked tree structure. However, I don't think you can normalize a database with n-level structures unless you put a cap on the number of levels.
0
 
LVL 32

Assisted Solution

by:shalomc
shalomc earned 100 total points
ID: 10814307
The following simple database design has proven itself in a manufacturing, MRP and supply chain planning environment.

Father, Son, Quantity

Of course, you may need a lot more database columns to facilitate engineering, manufacturing, advanced planning etc.

A database layout of an actual BOM file may look like this

Record Id.              
Item Number              
BOM Warehouse/Facility  
Mfg Method Material Code
Sequence Number          
Item Number-Child        
BOM Quantity Required    
Eftective Date          
Discontinue Date        
Item Class (parent)      
Item Class (child)      
BOM Scrap Factor        
Bubble Number            
Operation Number        
Lower Lot Number              
Upper Lot Number              
BOM Days Offset              
Co/By Product Cost Percentage
Must Issue Override          
Component Usage Code          
Reuse Operation              
Bounded Disposition Code      
Original/Order Facility      
Order Type                    
Engineering Change Number    
ECN Order Line Number        
Reference Designators Flag    
Future-Component Facility    
Last Maintenance Date        
Last Maintenance Time        
Last Maintenance By User Prf
Creation Date                
Creation Time                
Creation User                
Effective Time              
Effective Time Zone          
Discontinue Time            
Discontinue Time Zone        
Time Zone Created            
Time Zone Last Changed      
Last Program Maint          
Expiration Component        

On one location, I implemented a 9 level BOM using this system.
I have yet to see a normal manufacturing plant which really needs more than 5 levels.

ShalomC
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 4

Assisted Solution

by:mhci
mhci earned 100 total points
ID: 10853579
A Relational Database system can retrieve and store records more efficiently than a single text file. To decide what relational database system to use, you should consider the following:

1. Size of the database
2. Number of Users
3. Type of Application
4. Integrated database or distributed database
5. Cost vs Efficiency
6. Programming language to be used
7. Operating System to be used

If the number of users is 5-10, then, Access is a good enough database but severely lacking in security. MS SQL Server is another Microsoft product like Access and it can serve a larger number of clients.

I am particularly fond of MySQL database (www.mysql.com). It is open source and performs better than MS SQL Server. It can be used with Visual basic and ASP and with other opensource programming scripts such as PHP. It can run on both Linux and Windows. It is free for non commercial applications and costs less than Access for commercial ones.

Oracle is the most powerful database server in my view but I wouldn't recommend it for a small application. You can check it out at technet.oracle.com

Something like BOM should be stored in a RDBMS. ShalomC has given the field names for the same. You can refer those for making your own table.
0
 
LVL 11

Expert Comment

by:turn123
ID: 10906662
winsupplychain, Please respond to the comments from the Experts.
See:  http://www.experts-exchange.com/help.jsp#hi49 Thank you, turn123 (s)
0
 
LVL 7

Assisted Solution

by:bvanderveen
bvanderveen earned 100 total points
ID: 10991262
The ERP system I develop in uses the Oracle database for the BOM.  As each row can have a child row, there can be BOMs many levels deep, (including subassemblies).   The issue you will run into is that, unless you know how many levels deep, you cannot query with simple SQL (self-joins will be n-1 levels deep).  The typical ways around this are dynamic SQL, or a stored procedure that outputs denormalized data to a temp workspace.  (the temp workspace seems to be the most common and effecient solution).

As to mysql, it is OK to learn on, or for simple uses, but it does lack a lot of features, such as subqueries, that are pretty important.  On the other hand, it is free, and a great place to learn basic SQL.
0
 
LVL 5

Assisted Solution

by:Pontis
Pontis earned 100 total points
ID: 11110442
Well, if we start talking about DBMS specific implementation, you can use Oracle and use CONNECT BY selects to get you hierarchical view of your BOM form a single table.
  Here is the first that I found on Google giving you example of this:

  http://www.adp-gmbh.ch/ora/sql/connect_by.html

 This can give you n-level deep tree.

Hope this is what you are looking for.

 
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11306468
This is an opinion question, rather than one with a "correct" answer or approach, so  I think the points should be divided among those who answered.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Supply Chain Event Management Supply chain visibility is the ability of a company to track its products and business processes in supply chain towards building operational excellence. The goal of supply chain visibility is to improve and strength…
Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 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

12 Experts available now in Live!

Get 1:1 Help Now