2

We would like to store a simple web navigation menu in SQL Server 2012. This will be done for multiple clients, which is why we need it stored. The menu items also need to have a sequence, so they can be ordered how the client wants them. I have been reading up on SQL Server's HierarchyId data type, but almost all the tutorials I find are using the example of an employee or corporate hierarchy, with one root node at the top. After doing hours of reading and testing, I am struck with the feeling that HierarchyId may not the best tool for a simple navigation menu of links. Am I off with this feeling?

The main thing I have noticed about HierarchyId that worries me is that you can only have one root node with HierarchyId. But with a navigation menu, there are obviously multiple top-level "root nodes" that can have children. And since root nodes have no order (they are just "/"), our clients wouldn't be able to move around their top-level menu links.

So the obvious choice would be to have a dummy root node and have all top-level menu links be under that root node. But according to this SO question, marc_s and Jeremy make it seem as if it's unusual (or not according to the normal usage of HierarchyId) to create an artifical "über-root" node, just to have multiple first-level nodes. And by doing this "über-root" node, am I not also throwing off the GetLevel() function of SQL server, since the "top-level" nodes will now display as level 1 instead of 0?

I'm considering just going the route of storing a ParentId in each row and using recursion in C# to build the menu hierarchy. Would I be wrong in doing that? Is the HierarchyId data type really meant for this type of situation, and I am just missing something?

Community
  • 1
  • 1
John Washam
  • 4,073
  • 4
  • 32
  • 43

1 Answers1

4

I don't think I would worry about having a "dummy" root node - you obviously don't have to display that (and in some approaches, you don't even have to store the root node in the database if you are careful with your queries).

Any rooted tree is going to require a single root, and any rooted tree can be broken up into separate rooted trees, i.e.

          A                
         / \
        B   C       =>   B        C
       / \   \          / \        \
      D   E   F        D   E        F

So in other words, you can always combine multiple trees by introducing a common root.

I believe the heart of your question is how to store hierarchical data in a database.

There are three "classic" ways of doing so:

  1. The "Adjacency List" (which I believe is what you are hinting at when you mention "using recursion in C#"
  2. The "Hierarchical Path" (which is what you get with sql server hierarchy id)
  3. The "Nested Sets" (which embraces the set based approach of sql)

Any of these are suitable for storing hierarchical data - they come with different trade-offs in the performance of different types of queries, and in the ease of representation.

References:

  1. Trees in SQL (by Joe Celko)
  2. Hierarchies: Convert Adjacency List to Nested Sets
  3. Improve hierarchy performance using nested sets
  4. Nested set model
Nathan
  • 10,593
  • 10
  • 63
  • 87
  • If you haven't found out about them yet, look into recursive Common Table Expressions (CTEs). They can make adjacency lists a bit more tractable (only a bit--they're still a pain). – Philip Kelley Apr 22 '14 at 22:22