THE SQL Server Blog Spot on the Web

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

John Paul Cook

Hierarchyid data type and AdventureWorks2008 BillOfMaterials table

AdventureWorks2008 uses the hierarchyid data type in a updated version of the HumanResources.Employee table, but not in the Production.BillOfMaterials table. Michael Coles provides the code for a clever CTE to create Production.HierBillOfMaterials  from Production.BillOfMaterials in his book Pro T-SQL 2008 Programmer’s Guide (which I recommend). Another way to create the table is by using the brute force approach of running the 8,827 insert statements I provided in the attached zip file. The reason I call attention to size of the table is that the original Production.BillOfMaterials table is 2,679 rows. The value in looking at the same data implemented two different ways is to compare and contrast the differences. Once you can see and understand the differences, then you can choose which approach best suits your needs.

I’m using the Production.HierBillOfMaterials  table in my SQL PASS 2009 presentation next week. It stores the bill of materials data using hierarchyid instead of an adjacency list pattern. Another good book is Paul Nielsen’s SQL Server 2008 Bible, which covers hierarchies in a chapter you can download for free.

The original Production.BillOfMaterials table has StartDate and EndDate columns to store historical data about a bill of materials. The Production.HierBillOfMaterials table uses the hierarchyid data type but does not have StartDate and EndDate columns because it doesn’t maintain history. Supporting history in a bill of materials isn’t a technical problem to be solved. It has significant business and even potential legal implications. If a customer is using widget X and orders another widget X a few years later after some of its subassemblies have changed, the customer might not be happy to see that widget X has changed. I met with people from one manufacturing company who told me they think it is better to define a bill of materials as immutable. If the bill of materials must be changed, they just create a new bill instead of creating historical versions of the same bill. Different customers will reach different conclusions on how to handle history.

After working with the same bill of materials implemented with hierarchyid and an adjacency list pattern, I prefer the adjacency list pattern for a bill of materials.

Published Sunday, November 1, 2009 12:07 AM 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


No Comments

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in 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. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement