Database design for Magazine website with flexible horizontal menus

Question

I need to design a CMS based website using asp.net web form (c#) and MS SQL Server as backend database for a magazine website which will have weekly issues.

Sample Data

MagazinePages Table
PageID  PageName    LangID  PagePositionNo  PageURL     PageInheritance //PageID 
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           Media           1         30        Page.aspx       0
6           Video       1         10        Videos.aspx     5
8           News        1         40        News.aspx        0
9           Archive     1         50        #               0
10          Publication 1         60        Page.aspx       0
11          SpanishHome 2         10        Default.aspx        0
12          SpanisAboutUs 2       20        Page.aspx   0
------------------------------------------------------------------------------
Magazine
MagazineID  MagazineIssueCode   LangID  MagazineTitle   MagazineLiveIssue(CurrentIssue)
1       1           1   Mag Title       0
2       2           1   Mag Title       1
3       1           2   SpanisgMag Title    0
4       2           2   Mag Title       1
------------------------------------------------------------------------------

News Table
NewsID  NewsTitle   NewsCatID   MagazineID  Language    
1   News one    100     1       1
2   News two    100     1       1
3   news three  200     1       1
4   News four   300     1       1
5   News Five   100     2       1
6   News Six    300     2       1
7   News seven  200     2       2
------------------------------------------------------------------------------

Problem with above approach is that I can create sub menus only if all the records are in MagazinePages based on above example I can create sub menus for “About Us” & Multimedia how can I design my database so that I can pull data from different table such as news by category (Politics, Culture...) & Issue from Magazine Table (Issue 101, Issue 102, Issue 103....)

I am planning to use ASP Menu control for this which may not be very flexible, since I will have over 100 issue of magazine how or which multi column menu I can use with asp.net

My database may have many flows, I would appreciate help in this regard so that I can use this database for this CMS system . Please feel free to ask question if any regarding this.

Added: If I need to pull all the menu name from MagazinePages Table then it is easy but I have been asked to have a menu structure as shown in the example. Problem with this is I can generate menus for AboutUs & Multimedia from MagazinePages Table but I don't have any pages like Politics, Economic, ... Issue 101, 102, 103.. in this Table as that information is stored in different table like News Category in News Table & Issue in Magazine table. I would rather change my table design & make it flexible to read menu information from separate table but I am not sure how

News Table is not show in this Schema.

What I have done is that I have created ps_Pages Table for CMS Pages like, Home, Aboutus, Contact, MediaCentre .....

I store Pages related to Magazine (Which actually are article with different category or tags like culture, politics, sports, people ...) in art_Article table

Answer 1

Why not have a single page deal with the content?

Just call Page.aspx?Issue=4&Page=4

Then in your code you know it's the 4th Issue and they want Page 4, then you can have code in the Page.aspx (.vb or .cs) which translates that and then decides on the layout.

So E.g.

Page.aspx?Issue=4&Style=Article&Content=5

So in the code you could then go, Okay it's issue 4 get the database entries for Issue 4, okay they want Content ID 5 from Issue 4, then put in the style of an Article.

This means you don't have to add extra pages into the database type you can simply just add content as you wish and the item which generates the URLs to access the content just need to show all the content.

Answer 2

I think you need to re-evaluate your database schema. For example, I would have a table called "ParentMenuItems"

This table would include all Top Menu Items (Home, About Us, Issues, etc), and have the text for child menus. Then you need to have a ChildMenu table that has relations to your Parent Menu items.

ParentMenuItems:
==============================================================
ID       | LinkText      | LangID     | Other Properties
==============================================================
1        | Home.aspx     | 1          | blah blah blah
2        | AboutUs.aspx  | 2          | blah blah blah

Then, you could have another table called "ChildMenuItems" like this:

ChildMenuItems:
===============================================================
ID   | LinkText       | LangID |  ParentID  | Other Properties...
===============================================================
1    | PageOne.aspx   | 1      | 2          | blah blah blah
2    | PageTwo.aspx   | 2      | 2          | blah blah blah

The code could work like this:

SELECT * FROM ParentMenuItems - //SQL to get Items

Then, write some foreach code to enumerate the SQL results

foreach(var ParentMenuItem in ParentMenuItems)
{ 
    //Get ParentMenuItem ID, run SQL select on child menu items, Example:
    //SELECT * from ChildMenuItems where ParentID = ID.FROM.PARENT.RESULT
    // Now you have all the child menu items, foreach those and add to repeater control
}

I hope this helps. Let me know if you have questions.

TIP: Entity Framework will make very light work of this.

Answer 3

Create a table for menu's

CREATE TABLE [dbo].[tblMenuMaster](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](100) NULL,
[DisplayOrder] [int] NULL,
PRIMARY KEY CLUSTERED 
(
[MenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Create another table for sub menu's

CREATE TABLE [dbo].[tblSubMenuMaster](
[SubMenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuID] [int] NULL,
[SubMenuName] [varchar](100) NULL,
[MainMenuDisplayOrder] [int] NULL,
[DisplayOrder] [int] NULL,
[SubMenuUrl] [varchar](500) NULL,
[VisibleInMenu] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
[SubMenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Now go to master page .. HTML code is:

   <div class="menubar">
        <%--<ul class="tabs">--%>
        <asp:Literal ID="ltMenus" runat="server"></asp:Literal>
        <%--</ul>--%>
    </div>

Code behind code is :

private void GenerateMenus()
{
    clsMenu obj = new clsMenu();
    System.Data.DataSet ds = new System.Data.DataSet();
    String PageName = "";
    PageName = Path.GetFileName(Page.AppRelativeVirtualPath);
    ds = obj.GetMenusByRole(GetRoleId(), PageName);

    StringBuilder sb = new StringBuilder("<ul class='tabs'>");

    foreach (System.Data.DataRow row in ds.Tables[0].Rows)
    {
        sb.Append(String.Format("<li class='{0}'><a rel='{1}' href='{1}' > {2} </a> ", Convert.ToString(row["css"]), ResolveUrl(Convert.ToString(row["PagePath"])), Convert.ToString(row["MenuName"])));
        //sb.Append(String.Format("<li '><a rel='{0}' href='{0}' > {1} </a> ", ResolveUrl(Convert.ToString(row["PagePath"])), Convert.ToString(row["MenuName"])));

        System.Data.DataTable t = CCMMUtility.GetFilterDataforIntColumn("MenuID", Convert.ToString(row["MenuID"]), ds.Tables[1]);
        if (t.Rows.Count > 0)
        {
            sb.Append("<ul>");

            for (int i = 0; i < t.Rows.Count; i++)
            {
                sb.Append(String.Format("<li><a href='{0}' class='dir' style='cursor: pointer;'>{1}</a></li>", ResolveUrl(Convert.ToString(t.Rows[i]["PagePath"])), Convert.ToString(t.Rows[i]["PageAliasName"])));
            }

            sb.Append("</ul>");
        }
        sb.Append("</li>");
    }

    sb.Append("</ul>");


    ltMenus.Text = sb.ToString();

}

it needs stored proc to call menu's dynamic according to Role id like below

CREATE PROCEDURE [dbo].[proc_GetMenusByRole]
(
@RoleId int,  
@PageName varchar(100)
)
AS
SET NOCOUNT ON;
SELECT mm.MenuID, mm.MenuName,dbo.Extract_CssNameForMenuByMenuIDAndPageName(mm.MenuID, @PageName) as css
,dbo.proc_Extract_MenuPageByRoleIDAndMenuID(@RoleId, mm.MenuID)
as PagePath , mm.DisplayOrder   FROM tblMenuMaster mm WHERE mm.MenuID IN (SELECT s.MenuID from tblSiteRolePermissions p INNER JOIN
tblSitePages s ON p.fkSitePageId = s.pkSitePageId
WHERE (p.fkRoleId = @RoleId and p.ViewOnly=1))   
Union All   
select 0 as menuid ,'Change Password' as MenuName,  
case @pagename   
when 'ChangePassword.aspx' then 'active'  
else ''  
end  as css,'~/User/ChangePassword.aspx' as PagePath, 10000 as Displayorder  
ORDER BY DisplayOrder     
SELECT s.MenuID, s.pkSitePageId, s.PageAliasName, s.SitePageName,s.pagepath from tblSiteRolePermissions p 
INNER JOIN tblSitePages s ON p.fkSitePageId = s.pkSitePageId  WHERE (p.fkRoleId =@RoleId and p.ViewOnly=1) ORDER BY s.pkSitePageId  

//new sp starts here

CREATE function [dbo].[Extract_CssNameForMenuByMenuIDAndPageName](@MenuID int, 
PageName varchar(100))
returns nvarchar(50)
as begin      
declare @result nvarchar(50) 
set @result = ''    
IF EXISTS (SELECT pkSitePageId FROM tblsitepages WHERE (MenuID = @MenuID) AND (UPPER(SitePageName) = @PageName)) 
 BEGIN    
  SET @result = 'active'    
 END    
return @result    
end  

// another sp used is

CREATE function [dbo].[proc_Extract_MenuPageByRoleIDAndMenuID]
(@RoleId int, @MenuID int)
returns nvarchar(500) 
as begin          
declare @result nvarchar(500)
SELECT top 1 @result = s.pagepath FROM tblSitePages AS s INNER JOIN tblSiteRolePermissions AS p ON s.pkSitePageId = p.fkSitePageId
WHERE (p.fkRoleId = @RoleId) AND (s.MenuID = @MenuID)  and p.ViewOnly=1
ORDER BY s.pkSitePageId 
return  @result
end  

Its just a way to do this you can modify this according to your requirement.........

proc_Extract_MenuPageByRoleIDAndMenuID sp is used to get Page name and its path,

Extract_CssNameForMenuByMenuIDAndPageName sp is used to set active class to the first li means first menu. Hop this will help you..... Its the working code..

Answer 4

I'm not sure I understood what you need, but I'll assume you want to create a menu for the whole system, and not a menu for each magazine, right?

If so, I think you are looking to the wrong problem: The problem is not the database schema, but the concept that your menu should be automatically filled by N different tables that have no predefined standard.

I would create a menu table - and make magazinepages and menu two distinct tables: If you are creating an CMS-like system, you'll probably have a page where I can edit categories, issues and - why not - menu entries, completely apart from any other tables.

Also, I'd create a "tags" system, where all my news and issues could be "tagged" instead of put in a exclusive category. It would make possible to have a news that talks about politics and culture at the same time. Then, instead of directing user to lots of different pages.aspx, you'd use a content.aspx?tag=politics. When creating tag, I'd have the option to add this tag to menu table, under "news", "issues", etc. Same for category and issues.

If it doesn't fit your needs, you can try those options - but all other solutions than a specific table for menu entries trows a "warning, future problems detected" in my brain:

1 - create a menu table, a stored procedure that fills this table by following specific rules and then triggers on tables like "category" that would truncate and call the stored procedure, rewriting menu every time content changes (sounds like a quick fix to me)

2 - Add a "submenu_table", "submenu_field", "submenu_condition" etc to magazinepages and use dynamic sql to select data (something like Set @SQL='Select '+ submenu_field + ' from '+ submenu_table + ' where ' + submenu_condition; Exec(@SQL)) (another quick fix, heavy and probably slow query)

(edit:) 3 - there's also hierachyId field in sql 2008 and above ([http://blogs.msdn.com/b/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx])1