I am trying to create a multi-layer menu with no restriction on how far it can branch out - e.g. Food->Fruits->Round Shape->color etc...

I am not sure how to represent the relationship in SQL and also am not sure how to query the data.

I thought about doing:

MainMenu: ID:ChildID:Name:URL:PermissionID:etc.

SubMenu: PID:ChildID:Name:URL:PermissionID:etc.

I think if the submenu has additional submenus, its children would be another item in the SubMenu table, and that should work? The PermissionID is used to determine if the Menu item can be accessed by a UserGroup.

I am not sure about the SQL query since i am a little green on it. I know in the end I am feeding UserGroup's PermissionIDs to the SQL to get all the Menu and Submenu from the tables.

1 Answers

Community On

Storing hierachical data in RDBMS is not a good idea. Either you can use document based frameworks like Elastic https://www.elastic.co/ which stores data in JSON document format or you can use hierachical JSON in varchar column in RDBMS.