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.