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

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 2

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 http://dev.mysql.com/tech-resources/articles/hierarchical-data.html for further discussion and other options

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.