Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Database Design for navigation that could have multiple levels

    Hello, I have never had to design a database with the following and am having problems thinking it out.

    Basically I have a table called chapters, it is related to a table called sections. a section could be as low as it goes or it could go infinately down into sub sections.

    I.e.

    chapter 2, section 1 would be 2.1
    chapter 2 section 1, subsection 1 would be 2.1.1
    etc.

    Is there a basic design I could do to minimize the amount of tables used. I.e. setting up just a section and chapter table, or will I have to add a subsection table as well. I hope this makes sense any help would be appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Have a table for chapters.

    Have a second table for sections. This table should have a SectionID, as well as a column for ParentSectionID. This is known as the adjacency model, is pretty easy to implement once you understand it.

    Your other option is the nested set model, but I wouldn't recommend that to new data modelers.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    Ok, I think I get it

    so if there is no sub category a section of ID 1, would I still put ParentSectionID = 1 or would I leave it null?

    Thanks for the help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Posts
    3
    great thanks for the help

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by flamesburn
    Ok, I think I get it

    so if there is no sub category a section of ID 1, would I still put ParentSectionID = 1 or would I leave it null?

    Thanks for the help
    Either method allows you to identify the top-level records. They will result in slightly different code to handle them, so it is a matter of programming preference.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2008
    Posts
    120
    I came accross this old post and was very please to find the link to
    Categories and Subcategories

    I want to use this method for my product navigation on my site.
    The only problem is that the sub menu (down1_name) categories are showing up in the (root_name) and well as the sub menu

    Hope that makes sense!

    Andy

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    The only problem is that the sub menu (down1_name) categories are showing up in the (root_name) and well as the sub menu
    please show the exact query you ran
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2008
    Posts
    120
    Hi r937 (to the rescue again :-)

    The query im using is:
    Code:
    SELECT     TOP (100) PERCENT root.Category AS root_name, down1.Category AS down1_name, down2.Category AS down2_name, 
                          down3.Category AS down3_name, root.Section
    FROM         dbo.Categories AS root LEFT OUTER JOIN
                          dbo.Categories AS down1 ON down1.ParentID = root.CategoryID LEFT OUTER JOIN
                          dbo.Categories AS down2 ON down2.ParentID = down1.CategoryID LEFT OUTER JOIN
                          dbo.Categories AS down3 ON down3.ParentID = down2.CategoryID
    WHERE     (root.Section = 1)
    ORDER BY root_name, down1_name, down2_name, down3_name
    I've attached two screen grabs to illustrate the tables and results more clearly.

    Thanks again for your time....

    Andy
    Attached Thumbnails Attached Thumbnails table_view.jpg   results.jpg  

  10. #10
    Join Date
    Feb 2008
    Posts
    120
    I'm adding a screen grab of the categories table
    Attached Thumbnails Attached Thumbnails table.jpg  

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    WHERE root.parentid=0 AND root.section=1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2008
    Posts
    120
    Perferct r937

    You're a star

    Thank You - brill brill brill :-)

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •