• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3469
  • Last Modified:

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

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?

5 Solutions
winsupplychainAuthor Commented:
The data storage should be clean, standardized, simple
winsupplychainAuthor Commented:
no comments yet?!?
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.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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.

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.
winsupplychain, Please respond to the comments from the Experts.
See:  http://www.experts-exchange.com/help.jsp#hi49 Thank you, turn123 (s)
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.
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.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now