Database Design for navigation that could have multiple levels

flamesburn · #1 (**permalink**) 08-24-06, 16:33

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.

blindman · #2 (**permalink**) 08-24-06, 17:20

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.

flamesburn · #3 (**permalink**) 08-24-06, 17:44

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

r937 · #4 (**permalink**) 08-24-06, 17:54

see if this helps -- http://sqllessons.com/categories.html

flamesburn · #5 (**permalink**) 08-24-06, 18:25

great thanks for the help

blindman · #6 (**permalink**) 08-26-06, 00:37

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.

AndyJay · #7 (**permalink**) 01-25-12, 11:35

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

r937 · #8 (**permalink**) 01-25-12, 14:03

Quote:

Originally Posted by AndyJay

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

AndyJay · #9 (**permalink**) 01-26-12, 04:08

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