8

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 enter image description here

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.

Community
  • 1
  • 1
Learning
  • 19,469
  • 39
  • 180
  • 373
  • 1
    I would not try to model a "menu" in a data model. The only thing you will ever do with the information is display the menus. You will make your life a lot easier if you just serialize the menu objects (with json.net for example) and store them. – Filip De Vos Oct 17 '12 at 09:50
  • @Filip, Do you mean i should create a separate `Menu` table with fields fields like, MenuID, MenuName, PageID etc.. Can you please give me an example if i am wrong – Learning Oct 17 '12 at 09:59
  • 1
    I would make a file in your web application menu.json and dump the whole structure in there. – Filip De Vos Oct 17 '12 at 10:09
  • @Filip, My Menus can change from time to time for example when new issue of Magazine is create. Do i need to create this file everytime then than it reads & creates from database table directly. Sorry for being dumb on this one.. – Learning Oct 17 '12 at 10:23
  • I would look at the database schema of Liferay for "inspiration" – Neil McGuigan Oct 17 '12 at 19:40
  • 1
    You can use page Id field in Magazine and Article Table as foreign Key of Page Table. Link the tables,while fetching the menus. – MahaSwetha Jan 02 '13 at 08:58
  • @MahaSwetha, It seems you have a point but i am not sure how i will write query to childMenu from `pg_Pages` and Other sub menus from table like `Magazine` & `Articles`.. I have to think about it – Learning Jan 02 '13 at 09:44
  • 1
    You can have one more field named "Actve" to the tables,set this field value to 1 for active menus and set it to 0,when that menu not required.Combine both ideas and make a try. – MahaSwetha Jan 02 '13 at 09:51

2 Answers2

4

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

erichste
  • 759
  • 4
  • 16
3

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.

Serg
  • 2,346
  • 3
  • 29
  • 38