Bill of Materials - SQL Server Forums

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

All Forums

General SQL Server Forums

Database Design and Application Architecture

Bill of Materials

Author

Topic

cosmarchy
Starting Member

14 Posts

Posted - 2010-06-11 : 16:54:53

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 [url]http://msdn.microsoft.com/en-us/magazine/cc794278.aspx[/url] 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?

Thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-11 : 17:01:00

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.

quote:
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.

cosmarchy
Starting Member

14 Posts

Posted - 2010-06-14 : 16:56:13

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 http://msdn.microsoft.com/en-us/magazine/cc794278.aspx 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?

Thanks

ikjeft01
Starting Member

1 Post

Posted - 2014-06-27 : 02:52:01

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).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources