Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Bill of Materials
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

14 Posts

Posted - 06/11/2010 :  16:54:53  Show Profile  Reply with Quote
I am trying to create a Bill of Materials (BOM) structure in a database and am having some trouble deciding on the best way to achieve it.

I've found this article but it does not seem to cater for parts linked to the bill at the top level and not be part of an assembly. It also does not deal with sub assemblies of assemblies.

I am sure I am not the first person to ask, indeed I have found may examples but none seem to address the main issue that a part could be associated with a machine, which is not part of an assembly but it could also be in an assembly!! How do you deal with the issue of sub assemblies of assemblies also?

Can anyone point me in the right direction towards a database structure that addresses these issues?


Most Valuable Yak

15732 Posts

Posted - 06/11/2010 :  17:01:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
does not seem to cater for parts linked to the bill at the top level and not be part of an assembly
For your root node (machine, topmost entity, etc.) these parts are children of that entity only.
It also does not deal with sub assemblies of assemblies.
Sure it does, each subassembly is a child of the topmost entity/machine. If there are additional subassemblies, they are children of their parent subassembly.

You'll have to provide a detailed example of your data that would demonstrate the problem you're describing.
Go to Top of Page

Starting Member

14 Posts

Posted - 06/14/2010 :  16:56:13  Show Profile  Reply with Quote
Hello all,

Sorry for the late reply but I thought I’d better spend a little time getting the wording right on this to try and avoid any ambiguity!!

As previously mentioned, I have found a structure of a database here but I am not sure how to deal with particular parts and assemblies.

In this example, the bill to which everything belongs is the bill table. Assemblies of parts are held in the BillPartList table and the parts within each assembly are in the PartList table. The details for each part are in the part table.

Bill Table
BillID: Descr:
Machine1 Widget Making Machine

BillPartList Table
PartListID: BillID:
Assy1 Machine1
Assy2 Machine1
Assy3 Machine1

PartList Table:
PartListID: PartID: Quantity:
Assy1 Part1 1
Assy1 Part2 2
Assy1 Part3 3
Assy2 Part4 4
Assy2 Part5 5
Assy3 Part6 6
Assy3 Part7 7

Part Table
PartID: Descr: Cost:
Part1 Square Widget 4.0
Part2 Round Widget 4.5
Part3 Normal Widget 5.0
Part4 Abnormal Widget 5.5
Part5 Purple Widget 6.0
Part6 Inverted Widget 6.5
Part7 Midget Widget 7.0

We can see from this:

1) Machine1 has three assemblies numbered 1,2 & 3.
2) Assy1 contains Parts1,2, & 3
3) Assy2 contains Parts4 & 5
4) Assy3 contains Parts6 & 7

Here are the bits I am unsure about:

1) Where do I put parts if I want to issue an individual part to a machine? If I put them under PartListID on the BillPartList table, I would be mixing parts and assemblies. Is this a problem other than the naming of the table columns?
2) How can I have a part that does not belong to an assembly? Do I put it in the PartList table and leave the PartListID blank? I would then have duplicate PartIDs – is this efficient use?
3) How can I have a sub assembly of an assembly? I could use the PartList table again with PartListID being the parent and use the PartID as the sub assembly name but this is mixing parts and assemblies - again is the efficient way of doing this?

What I need is shown here:

Hopefully my problems are described a little clearer now?


Go to Top of Page

Starting Member

1 Posts

Posted - 06/27/2014 :  02:52:01  Show Profile  Reply with Quote
go back and RTFM. If you look again at the examples in the MSDN article you referenced, you'll see that all of your ASSEMBLIES should be in fact rows in the BILL table, and that the hierarchical relationship between your top-level MACHINE and the ASSEMBLIES is represented by parentBillID: MACHINE 1 has a parentBillID of NULL, and the three ASSEMBLIES have a parentBillID of MACHINE1 (logically). If ASSEMBLY1 is itself comprised of other assemblies (say ASSEMBLY11 and ASSEMBLY12), those assemblies would have a parentBillID of ASSEMBLY1 (thus building your hierarchy).

Bills that are comprised only of other bills (assemblies) have no rows in BillPartList because they have no parts (their parts are contained within their children assemblies)... not as shown in your example.

download the sql scripts from the article and look at how the data gets populated in the tables (01_data.sql).
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000