I am facing a peculiar problem (due to bad database design, I assume) and need some help in logic building.
We are making a web application where the menu's to be dislayed on screen are generated dynamically based on the menu details stored in a table "MENU". The fields in this table are menu id (primary key, we are using timestamp), menu level and parent id. Menu ID is the primary key and is generated whenever a new menu is created. We are using DB2 UDB's TIMESTAMP for this. Menu Level describes what level of menu is it. For top level menu its value is '0' (zero). For 1st level submenu, its value will be '1' and soon. (Example: We have a menu called Master Tables, which is a list of all master tables, hence its menu level is '0'. This master table has 3 submenu's "User", "Admin" & "Client", so these sub menus have menu level '1'. If we add a sub menu for "User" master, it will become a level '2'.
The parent id field has the menu id of its corresponding parent. For menu level '0' this field is null as it is the top level menu. Foe menu level '1' this field will have the menu id of its parent menu ie one of the '0' level menu's. Similarly, a menu level'2' will have the corresponding menu level '1' id in its parent id field.
I need to display this menu in the proper parent-child hierarchy.
ie, 0 level - its corresponing 1 level - its corresponing 2 level... and so on.. this should repeat for all 0 levels having their respective tree of 1 level's and 2 levels inside (can go upto any level)...
I need a solution for this problem either programatically or at the database level. I strongly believe programatically this requires some kind of recursive loop where it should iterate through the entire list and find all childrens of a top level menu, again and again to generate the sorted list of menus in the order in which it has to be displayed...
Hmm, that is ugly. Given carte blanc, I'd rewrite the schema. I can't imagine that table is easy to maintain. However, I assume you probably won't be able to do this.
What I'd do then, presuming menus don't change that often, is write some code to cache the menu table contents as an XML document. That way the intensive work needed to recurse through the table and build the structure is done once - from then on you need only get the correct Node with an XPath query, and the contents of the menu are brought back as its children.
If the menu structures change too often to allow this, then a schema redesign becomes even more of a good idea. It looks like you have already got the best idea how to programatically handle the table - read the whole thing into the app, and recurse till you have sorted out the menus. Good luck.
Seconding the XML document idea. In fact, if there are no concurrent updates to the menu structure then consider storing the whole menu structure as an XML document in the first place - either in a database text/CLOB field or (if it's changed as part of development only) on the filesystem. Why bother marshalling and unmarshalling XML if you don't need to?
With XSLT taking the id of the current page as a parameter, rendering a menu structure, breadcrumb trail, document title and so on into HTML or whatever is an absolute doddle. Stylesheets are made for this kind of stuff.
Another reasonable alternative could be XML serialization of an Java object model representing the menu.
I will definitely try that out as it looks quite an interesting way and I have not explored the XML option too much before...
Right now I am having a time crunch, hence in need of a quick fix for this problem. I am more inclined towards finding a solution at database design level, that makes building the menu tree simpler while coding..
Ah, but can you afford not to do the simplest thing that can possibly work? In any case, if you wish I can give you a sample navigation xml file and stylesheets that should provide a battle tested starting point. "Battle tested" in this case means 8-digit hits/month - they do navigation rendering on the F.A. Premier League site... just e-mail me.
- Peter [ June 07, 2004: Message edited by: Peter den Haan ]