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?
Copyright Notice：Content Author:「John Washam」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/17934299/sql-server-hierarchyid-and-storing-simple-web-navigation-menu