Table structure for menus in mySQL

Question

I need advices for a table structure.

I need to create menus dynamically.

There will be more than 3 menus.

Some of menus will have multi-level structures.

What is the best way to strucure the menu tables?

1. All the menus are in one table
2. Each table should have one table
3. Any other??

Please give me your experience or common practices for dynamically generated menus please.

Answer 1

assuming your menu tree is not very large, the so-called adjacency list would be the simplest option

id   
parent_id
title
other fields

for top level menus parent_id = 0

see Managing Hierarchical Data in MySQL for further discussion and other options

Answer 2

You could use a structure like this:

table name: menu id - primary key menu_id - reference to the parent menu item title - title of the menu (any other data you need)

this way you can have unlimited levels ... off course this is not the only way ...

Answer 3

Using parent_id should be enough for most structures, however if you get stuck on performance (with regards to deep recursion), you should also research Modified Preorder Tree Traversal.

Answer 4

http://www.codeproject.com/Tips/354696/Dynamically-populating-menu-items-from-the-databas

this will be helpful, Dynamically populating menu items from the database in ASP.NET