Search code examples
sqlschemassmsdbvisualizer

Analysis of SQL Schema: what is the purpose of loop in ProductCategory of AdventureWorks?

I want to understand the purpose of the loop in the ProductCategory table. AdventureWorks is a sample database here provided by Microsoft. Why is there a loop in the ProductCategory table of AdventureWorks?

Diagram from Microsoft SQL Server Management Studio where loop is on the right

enter image description here

Diagram (now called Reference Diagram, in Hirarchy mode, generated by Db Vis) where loop is on the bottom-right

enter image description here

Solution

  • I would assume that the cycle exists to indicate that product categories are hierarchical. If you look at the table definition, you will see the following two fields which sort of tip us off to this:

    ProductCategoryID
ParentProductCateogoryID

    If I understand correctly, the lines generally represent parent-foreign key relationships. These relationships also indicate places where we could join two tables together. In the case of the ProductCategory table, we can actually join this table to itself using a self join. This is how we might query out category hierarchies.