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.
chapter 2, section 1 would be 2.1
chapter 2 section 1, subsection 1 would be 2.1.1
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.
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.