Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
3,436 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
[X]
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
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 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.
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
LVL 33

Assisted Solution

by:shalomc
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.

ShalomC
0
 
LVL 4

Assisted Solution

by:mhci
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.
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 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.
0
 
LVL 5

Assisted Solution

by:Pontis
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:

  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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Read about achieving the basic levels of HRIS security in the workplace.
Let’s face it: one of the reasons your organization chose a SaaS solution (whether Microsoft Dynamics 365, Netsuite or SAP) is that it is subscription-based. The upkeep is done. Or so you think.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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