THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Bill of Materials – The Basics

After having several people at SQL PASS ask me to explain what a Bill of Materials is, it seemed like a good idea for a blog post. A Bill of Materials, otherwise known as a BOM (don’t read this aloud in an airport lounge), is a listing of all of the components (i.e., parts) needed to assemble (i.e., make) something. Let’s start with a picture of our inventory of components.

image

Figure 1. Inventory of parts. (note: the colors were chosen because they are visible to people with any type of color vision deficit)

The inventory consists of:

32   1x1 yellow bricks

  4   1x3 black bricks

  5   1x4 blue bricks

These components are assembled into what are called assemblies. There are enough components to make five assemblies. These assemblies form the letters E, F, H, I, and L. Here’s a picture of our five assemblies and the one leftover component.

image

Figure 2. Parts from Figure 1 assembled into five assemblies.

Several things should now be obvious. Although the inventory is the same in Figures 1 and 2, the entropy of Figure 2 is much lower. The components are subordinate to the assembly containing them – there’s a hierarchical relationship between an assembly and its components. If you focus on the letters H and I, you realize that another level can be added to the hierarchy. The two assemblies H and I can be used to assemble the word HI. When an assembly becomes a component in another assembly, it is called a subassembly. It doesn’t take much imagination to realize that letters can be assembled into words, words into sentences, sentences into paragraphs, paragraphs into chapters, and so on. The hierarchy can be made many, many levels deep.

There are multiple techniques for modeling a hierarchy. Organizational charts are hierarchical in nature and are implemented in the SQL Server sample databases Northwind, AdventureWorks, and AdventureWorks2008 and the Oracle sample schemas SCOTT and HR. I have read that a Bill of Materials and an organizational chart are similar. They are in the sense that they are both hierarchical. But they are also different. An employee table (e.g., Northwind Employee, AdventureWorks HumanResources.Employee, SCOTT.EMP, HR.EMPLOYEE) has one row per employee. Although a BOM will work with one row per component, it’s not just annoying, it’s a bad idea. Look at the two hierarchical displays shown below.

image

Figure 3. One row per part model on the left, part with quantity model on the right.

The list on the left shows what happens when one row is stored for each component. While it is necessary to know that the assembly E contains four 1x1 yellow bricks, it’s not helpful at all to see the 1x1 brick component description appear four times. Adding a quantity column to the data model results in a practical and useful BOM. Adding a quantity column to an employee table wouldn’t make sense, except when the employees are Imperial storm trooper clones.

The adjacency list pattern is the most common approach to modeling a hierarchy. It is used in the Production.BillOfMaterials table in AdventureWorks and AdventureWorks2008. A self-join is used to model the hierarchy.

image

Figure 4. The ParentPartId contains the PartId of the immediate parent (which is one level up in the hierarchy).

A top level assembly has a ParentPartId of NULL when an adjacency list pattern is used as the following output shows.

image

Figure 5. Each component (PartId values 17, 29) has a reference to the PartId 30, which is the PartId of the assembly named H.

When an assembly is used as a component of another assembly, it must have a ParentPartId instead of a NULL. The following output shows what happens when assembly H becomes a subassembly of assembly HI.

image

Figure 6. PartId 30 now has a ParentPartId of 47 instead of NULL.

When working with hierarchies, each item belongs to a specific level of the hierarchy. The parts lists shown in this post are examples of what is known as a Bill of Materials parts explosion. To format a parts explosion to show the hierarchical relationships, the level of each component must be known. If it isn’t stored in the table, it must be calculated from adjacency list references to the parents.

image

Figure 7. Parts explosion showing levels of components.

The levels of a hierarchy implemented using an adjacency list pattern are easily calculated with a recursive CTE.

;WITH BOMcte(PartId, Name, Color, Quantity, Lvl)
AS
(
    SELECT WQ.PartId,
    CAST(WQ.Name as nvarchar(50)),
    WQ.Color,
    WQ.Quantity,
    1 -- or use 0 if you prefer a zero-based hierarchy
    FROM AdjacencyList.WordQty AS WQ
    WHERE WQ.ParentPartId IS NULL
    AND WQ.PartId = 47
    UNION ALL
    SELECT WQ.PartId,
    CAST(REPLICATE ('| ', Lvl) + WQ.Name as nvarchar(50)),
    WQ.Color,
    WQ.Quantity,
    Lvl + 1
    FROM AdjacencyList.WordQty as WQ
    JOIN BOMcte AS cte
    ON WQ.ParentPartId = cte.PartId
)
SELECT Lvl, Name, Color, Quantity
FROM BOMcte;

Notice that the level is needed to indent the subordinate items according to their levels. The first level of the hierarchy is defined in the anchor query (hardcoded to 1 in this example) and the remaining levels are computed in the recursive query. The CAST function is required to prevent a run time error caused by the UNION ALL statement. The data types must be compatible between the two statements in a UNION. CAST is used to make the second column in the anchor query the same length as the second column in the recursive query. If they don’t match, the query fails with a Msg 240.

When the objective is to create nested results to represent the hierarchical relationships, a sort key needs to be computed.

;WITH BOMcte(PartId, Name, Color, Quantity, Lvl, ParentPartId, Sort)

AS

(

    SELECT WQ.PartId,

           CAST(WQ.Name as as nvarchar(50)),

           WQ.Color,

           WQ.Quantity,

           1,

           WQ.ParentPartId,

           CAST('\' + WQ.Name as nvarchar(50))

    FROM AdjacencyList.WordQty AS WQ

    WHERE WQ.ParentPartId IS NULL

    AND WQ.PartId = 47

    UNION ALL

    SELECT WQ.PartId,

           CAST(REPLICATE('|    ', Lvl) + WQ.Name as nvarchar(50)),

           WQ.Color,

           WQ.Quantity,

           Lvl + 1,

           WQ.ParentPartId,

           CAST(cte.Sort + '\' + WQ.Name as nvarchar(50))

    FROM AdjacencyList.WordQty as WQ

    JOIN BOMcte AS cte

    ON WQ.ParentPartId = cte.PartId

)

SELECT Lvl, Name, Color, Quantity, Sort

FROM BOMcte

ORDER BY Sort;

 

image

Figure 8. Parts explosion query with the computed sort key values shown.

As you can see, the sort key trick computes the full path from the root node to the node where the component is located. Ordering the output on the path (i.e., sort key) allows the indented results to appear in the desired order.

If you want to practice with AdventureWorks BOM queries yourself, I provided code samples for a parts explosion, a where used query, and an accumulated quantity query.

If you'd like to play with virtual Legos, download Lego Digital Designer from here. You can order what you built and Lego will ship you the parts.

Published Friday, November 06, 2009 6:23 PM by John Paul Cook
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Rob Esposito said:

THAT is AWESOME!

Great job.  Perfect Explanation.

Thank you.

November 4, 2011 4:53 PM
 

Max said:

Nice job!  Great explaination of a BOM!

November 15, 2011 2:58 AM
 

Vivek Thakar said:

Nice Work!

April 26, 2012 12:59 AM
 

Vivek Thakar said:

I am trying to implement the same in MySQL. As it turns out that MySQL dose not support WITH clause. Is there any way to implement this without WITH clause.

April 26, 2012 1:21 AM
 

KN said:

Thank you so much, especially for being so considerate towards people with vision problems :-)

July 17, 2012 1:35 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement