Designing Database Table for Navigation Menu

Question

Hi i have been working on a online shopping project for gifts . My Navigation menu has 3 Levels like this : Parent Menu ==> Child Menu ==> Child Sub Menu . Reference Image Attached. http://tryourself.com/m1.png i have to get all these values from the database table in a hierarchy, i was thinking to design the database table in such a way that request time for fetching menu data from table is minimal. my idea is to design a table name navigation_menu with following parameters : id(as menu id), Menu_Name , Parent_id id(Parent Menu id) Menu_Name Parent_id 0 BIRTHDAY - 1 OCASSIONS - 2 GIFTS - 3 SPECIAL - 4 CAKES 0 5 OFFERS 1 6 GIFTS 0 7 SPECIAL CAKE 4 8 PHOTO CAKE 4 Or can i design the database in a better way to fetch the data more faster (Suggestions required) .

Upendra Pratap Shahi · Answer

Hi, It depends how you are getting the data, Suppose you have your data in DataTable where each row is having Id and Name and in addition to this if there are any child items for the menu items then have a ParentId field also, If this is the case you can try with ASP.Net Menu control and write following code to bind it dynamically: For Parent Menu items: //From teh Datatable you have filter all rows with ParentId NULL and Loop on that Rows and perform foolwing foreach(var row in Rows) { MenuItem newMenuItem = new MenuItem(row["Name"].ToString(), row["Id"].ToString()); .Items.Add(newMenuItem); //Now from here you can call a new method code given below to add child items for this particular menu item } For Child Items: //Here filter out all the rows from datatable where ParentId is mapped to the Id which is passed from the above code. foreach (var row in rows) { MenuItem newMenuItem = new MenuItem(row["Name"].ToString(), row["Id"].ToString()); parentMenuItem.ChildItems.Add(newMenuItem); //You can also call this code recursively if there is one or more level of hierarchy } and also Go through below link- http://www.codeproject.com/Tips/526004/Database-Driven-Dynamic-Menu-Control http://www.aspsnippets.com/Articles/Database-driven-ASPNet-Menu-control-Populating-Menu-items-from-the-Database-in-ASPNet-using-C-and-VBNet.aspx http://www.c-sharpcorner.com/UploadFile/rohatash/creating-dynamic-menu-from-database-sql-server-in-Asp-Net/ http://www.aspnettutorials.com/tutorials/controls/sqldb-driven-menu-csharp/

Jaipal Reddy · Answer

http://www.codeproject.com/Tips/526004/Database-Driven-Dynamic-Menu-Control

id(Parent Menu id)	Menu_Name	Parent_id
0	BIRTHDAY	-
1	OCASSIONS	-
2	GIFTS	-
3	SPECIAL	-
4	CAKES	0
5	OFFERS	1
6	GIFTS	0
7	SPECIAL CAKE	4
8	PHOTO CAKE	4