db schema for web menus ?

[Anon]

Hi All,

I'm trying to design tables for building a navigation menu for a web app. I'm torn between doing it all in one table as a hierarchy, or in multiple tables using a "main category", "subcategory", "subsubcat" , etc. and relating them though a "menu_items" table saying which category and sub category, etc.

The thing is i need to send the menu to the web app in XML, so just trying to figure out which design would make the creation of the XML doc easiest.

Any suggestiosn would begreat...

Thanks
Keith

[[deleted]]

You don't know how many sub-cats you may get, so using seperate tables for sub, subsub,subsubsub etc is impossible.

Thus you should put it in one big table, and use columns to identify the hierarchy.
Like a parentid, which is the id of the menu that this selection is a submenu of.

This does mean you'll have to recursively parse the data to get certain menu's, but such is life.

[mp96brbj]

I agree with the great vincent's solution.
If you feel that the recursive stuff is claiming too much resources (ie it is used often), and the table isn't updated too much, you could consider making an index-like thing every time it is updated, storing extra info about the recursiving (probably mis-spelled that one). I did this by adding two columns:
"level", which is an integer defining how deep into the hierarchy the row is.
"display_order", which are unique integers, so I can easily sort the rows the way they should be displayed.

I've only done this on an experimental project, so there might be a better solution around, but this has the advantage that you can fetch the hierarchy you want with a single query.

[[deleted]]

That is an idea.
You could do the recursive method once to create include_files that contain the completed menu's.
It could save some time if your menu's are large.

[Anon]

Yeah I'll probably do the hierarchy for this. Here's my actual table design idea:

itemID int (PK)
parentID int
item_en
item_fr
item_.......

where the item_en is item in english, or french, or whatever. After a user has logged in, I'll know their language and append that language code to the item field so that they get the correct language of the dynamic menu.

Course there are access issues in that person A might only be able see a few items on the menu, while person B can see everything on the menu. So I have to also add in the itemID to a userItems type of table to relate userid with itemID. Whatcha Think

Thanks for the info....

Cheers,
K