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
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Author Comment

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

Author Comment

ID: 10793801
no comments yet?!?

Accepted Solution

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.
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

LVL 33

Assisted Solution

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.


Assisted Solution

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 ( 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

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: Thank you, turn123 (s)

Assisted Solution

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.

Assisted Solution

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:

 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
Read about achieving the basic levels of HRIS security in the workplace.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

724 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