Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
Medium Priority
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?

Question by:winsupplychain

Author Comment

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

Author Comment

ID: 10793801
no comments yet?!?

Accepted Solution

JaniceLaw earned 400 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.
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

LVL 33

Assisted Solution

shalomc earned 400 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.


Assisted Solution

mhci earned 400 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.
LVL 11

Expert Comment

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

Assisted Solution

bvanderveen earned 400 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.

Assisted Solution

Pontis earned 400 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:


 This can give you n-level deep tree.

Hope this is what you are looking for.


Expert Comment

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.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Note: You must have administrative privileges in order to create/edit Sharing Rules. 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 an…
Read about achieving the basic levels of HRIS security in the workplace.
Integration Management Part 2
Loops Section Overview
Suggested Courses

571 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