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

Question

This question is in reference to my previous question. Based on my requirements I have made some correction to my database, but I am not still sure how I can create sub-menus (Which need to be created from another table rather than the main pg_Pages table.

I keep Page information in pg_Pages from where I can create my menus. My problem is that I can easily create sub-menus for "About Us" and "Multimedia" since I store those pages in the pg_Pages table, but I need to create sub-menus for News from the News Category table and link them to the proper page Handler. & Issue from magazine table.

Menus Example

Sample Data

pg_Pages Table
PageID  PageName    LangID  PagePositionNo  PageURL     PageInheritance
1   Home        1   10      Default.aspx    0
2   About Us    1   20      Page.aspx   0
3   PageOne     1   10      Page.aspx   2
4   PageTwo     1   20      Page.aspx   2
5   Multimedia  1   30      Page.aspx   0
6   Video       1   10      Videos.aspx 5
7   PhotoGallery    1   20      Gallery.aspx    5
8   News        1   40      News.aspx   0
9   Issues      1   50      #       0
10  Publication 1   60      Page.aspx   0
11  SpanishHome 2   10      Default.aspx    0
12  SpanisAbout Us  2   20      Page.aspx   0
------------------------------------------------------------------------------
Magazine
MagazineID  MagazineIssueCode   LangID  MagazineTitle   MagazineLiveIssue(CurrentIssue)
1       101         1   Mag Title       0
2       102         1   Mag Title       1
3       101         2   SpanisgMag Title    0
4       102         2   Mag Title       1
------------------------------------------------------------------------------

art_Article Table
ArticleID   ArticleTitle    ArticleCatID    MagazineID  Language   TYPE 
1       Article one 100     1       1   Artile
2       Article two 100     1       1   Artile
3       Article three   200     1       1   Artile
4       Article four    300     1       1   Artile
5       Article Five    100     2       1   Artile
6       EditorMessage   300     2       1   EditorMessage
7       Article seven   200     2       2   Somthing
------------------------------------------------------------------------------

I want my design to be flexible enough to read menus from different tables. What approach/changes should I take/do to get it done properly rather than creating a separate table for all the Menus & link them to pages? Please suggest the best approach for this scenario.

I want system to be very flexible and read menu information directly from the database rather than creating static links and firing queries based on IssueID or some other ID.

News Menu basically show categories of article, Further i need a query which will only show categories under News menu which has articles for that particular issue. Suppose if issue 102 doesnt have any article related to Culture then Culture sub-menu should not show up under news.

Answer 1

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...

Answer 2

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.