Database design for website (CMS) consideration & suggestion for database driven menus

For a generic CMS there should be a separate Menus table and a related MenuItems table.

There can be different kinds of menus - top/main menu, inner left menu, site footer etc... you should define them in the menus table.

Than you should have a recursive MenuItems table which can have an infinite number of sub-menu items.

Menus table should at least have these columns:

• MenuID (int)
• Description (nvarchar(...))
• CreateDate, CreatedBy, ModifyDate.... - Logging columns...

MenuItems table should have at least these columns:

• MenuItemID (int)
• MenuID (int) - Related to which menu???
• Caption (nvarchar(...))
• LanguageID (int or whatever)
• ParentMenuItemID (int) for recursion
• Link (nvarchar(...)) whatever it is linking to (page, outer site, nothing,...). At the end everything has to come down to a link...
• IsExternalLink (usually for external links you might disable S.E.O. crawling and open on another tab - So knowing it might be good)
• SortOrder (int) the ordering
• CreateDate, CreatedBy, ModifyDate.... - Logging columns...

Hope it helps...

Your task is to map your site instances onto menu instances. This can be easily done with View.

So, for example, create viewMenu with the following columns: MenuItemId, MenuItemName, MenuItemLevel, MenuItemParent, MenuItemUrl. You can manipulate these properties to create your menu in your code. Also you can manipulate data from your current database structure or any future structure using SQL query creating initial view.

As for now, you may union results of three different queries to your three tables. In the future you can add functionality to your menu changing view. As well as add new items to your menu when adding new tables to your database.