0

I want to create a database driven menu with multiple levels, but I'm not sure what the best practice is in designing the database table(s). I've done some searching, but can't find much relevant information.

Initially, I had thought to create a table for each level. For instance, the parent level menu would simply be dbo.Menu with the immediate children menu items housed in dbo.SubMenu. With that said, I envisioned the following design for dbo.Menu:

enter image description here

However, once I began populating this table, I realized that the site will have some items with a depth of 3, and there is potential for adding more levels later.

Is there a best practice for designing a dynamic menu? Should I have one table containing all menu items or should I separate the menu items into multiple tables?

user2864740
  • 60,010
  • 15
  • 145
  • 220
  • 1
    I would *not* create a table for each level - that is using structure for multiplicity and it doesn't fit in with RA. It doesn't matter if it's tables or columns. Search for these terms: "parent-child relationship", "materialized path", "nested sets", and "hierarchy ID" to give some idea of how/where to start. – user2864740 Apr 18 '14 at 18:41
  • 1
    See http://stackoverflow.com/questions/38801/sql-how-to-store-and-navigate-hierarchies , http://stackoverflow.com/questions/544632/implementing-a-hierarchical-data-structure-in-a-database?lq=1 – user2864740 Apr 18 '14 at 18:45
  • 1
    FWIW (and since it looks like you're using SMSS/SQL Server), my current favorite approach is to use a [Hierarchy ID](http://msdn.microsoft.com/en-us/library/bb677290.aspx) (a form of "materialized path") built automatically off of an Adjacency List ("parent-child"). – user2864740 Apr 18 '14 at 18:54
  • Brilliant. I am creating the menu table using the Hierarchy ID data type and am amazed that I've never heard of it before. Very cool! For now, I'm manually inserting hierarchy IDs, like inserting `CAST('/1/2/' AS HierarchyID)`. I will have to look into building this relationship automatically from the Adjacency List. Thanks! –  Apr 18 '14 at 20:05

1 Answers1

4

Use a single table to hold all levels. You can do this by adding a ParentMenuId column (NULLABLE) which will reference the MenuId (of the same table). Top level menu items will have a ParentMenuId = NULL.

This type of implementation may seem complex in other contexts, but it really ideal here, where you will always access one single submenu at a time.

Frazz
  • 2,995
  • 2
  • 19
  • 33
  • 1
    Also known as an [Adjacency List](http://en.wikipedia.org/wiki/Adjacency_list) or "parent-child relation". Works best in databases that support `CONNECT BY` or recursive CTEs; without which "finding all children, recursive" can be an extremely degenerate operation. – user2864740 Apr 18 '14 at 18:48
  • 1
    Thanks for the link. I've doing these things for years... a lot of practice, but I lack on the theory side :) – Frazz Apr 18 '14 at 19:20
  • is there a way to have the ParentMenuId only allow values that are in the MenuId col? E.g., a foreign key constraint where the foreign key is a column on the same table? – Woodchuck Apr 17 '19 at 20:35