Database Design for Website Review (CMS) and Suggestions for Database-Driven Menus

Question

This question relates to my previous question. Based on my requirements, I made some corrections to my database, but I'm still not sure how to create submenus (which need to be created from another table, and not from the main pg_Pages table.

I save page information in pg_Pages , from where I can create my menus. My problem is that I can easily create submenus for "About Us" and "Multimedia", since I store these pages in the pg_Pages table, but I need to create a submenu for News from the News Category table and associate them with the corresponding page handler . and Problem from the magazine table.

Menu example

Data examples

 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 project to be flexible enough to read menus from different tables. What approach / changes should be taken / made in order to do this correctly, and not create a separate table for all menus and link them to pages? Please suggest the best approach for this scenario.

I want the system to be very flexible and read menu information directly from the database, rather than create static links and run queries based on IssueID or another identifier.

News Menu mainly shows categories of articles. Next, I need a query that will only show categories in News , which has articles for this particular problem. Suppose that if issue 102 does not contain any article related to Culture , then the Culture submenu should not appear in the news.

Answer 1

For a common CMS, there should be a separate menu table and a corresponding MenuItems table.

There can be different types of menus - top / main menu , left inner menu , website footer , etc .... you must define them in the menu table.

Instead of having a recursive MenuItems table that can have an infinite number of submenu items .

The menu table must contain at least the following columns:

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

The MenuItems table must have at least the following columns:

• MenuItemID (int)
• MenuID (int) - associated with which menu ???
• Signature (nvarchar (...))
• LanguageID (int or something else)
• ParentMenuItemID (int) for recursion
• Link (nvarchar (...)) to which it refers (page, external site, nothing, ...). In the end, everything should come down to the link ...
• IsExternalLink (usually for external links you can turn off SEO crawl and open it in another tab - so knowing that this may be good)
• SortOrder (int) ordering
• CreateDate, CreatedBy, ModifyDate .... - Logging columns ...

Hope this helps ...

Answer 2

Your task is to match the instances of your site with the instances of the menu. This can be easily done using View.

So create a viewMenu with the following columns: MenuItemId , MenuItemName , MenuItemLevel , MenuItemParent , MenuItemUrl . You can manage these properties to create your own menu in your code. You can also manipulate data from the current database structure or any future structure using an SQL query that creates the original view.

Now you can combine the results of three different queries with three tables. In the future, you can add functionality to the menu by changing the look. Besides adding new items to your menu when adding new tables to your database.