1

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

hhh
  • 50,788
  • 62
  • 179
  • 282

1 Answers1

2

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Does hierarchical categories mean that also each category is hierarchical inside each column-category such as ProductCategoryID and ParentProductCategoryID? Or does it only mean hierarchical structure of ParentProductCategoryID over ProductCategoryID? Or can it be both or one of the combinations? Can a self-referring loop also mean something else for a table? – hhh May 17 '17 at 20:50
  • 1
    I don't understand your question. As I said, I believe it means that product categories are hierarchical and that the `ProductCategory` table can be self joined to obtain the product hierarchy. – Tim Biegeleisen May 17 '17 at 23:18