How do I go about link web content in a database with a nested set model?

Question

My nested set table is as follows.

 create table depts (
  id int identity(0, 1) primary key
  , lft int
  , rgt int
  , name nvarchar(60)
        , abbrv nvarchar(20)
 );

Test departments.

insert into depts (lft, rgt, name, abbrv) values (1, 14, 'root', 'r');
insert into depts (lft, rgt, name, abbrv) values (2, 3, 'department 1', 'd1');
insert into depts (lft, rgt, name, abbrv) values (4, 5, 'department 2', 'd2');
insert into depts (lft, rgt, name, abbrv) values (6, 13, 'department 3', 'd3');
insert into depts (lft, rgt, name, abbrv) values (7, 8, 'sub department 3.1', 'd3.1');
insert into depts (lft, rgt, name, abbrv) values (9, 12, 'sub department 3.2', 'd3.2');
insert into depts (lft, rgt, name, abbrv) values (10, 11, 'sub sub department 3.2.1', 'd3.2.1');

My web content table is as follows.

 create table content (
  id int identity(0, 1)
  , dept_id int
  , page_name nvarchar(60)
  , content ntext
 );

Test content.

insert into content (dept_id, page_name, content) 
 values (3, 'index', '<h2>welcome to department 3!</h2>');
insert into content (dept_id, page_name, content) 
 values (4, 'index', '<h2>welcome to department 3.1!</h2>');
insert into content (dept_id, page_name, content) 
 values (6, 'index', '<h2>welcome to department 3.2.1!</h2>');
insert into content (dept_id, page_name, content) 
 values (2, 'what-doing', '<h2>what is department 2 doing?/h2>');

I'm trying to query the correct page content (from the content table) based on the url given. I can easily accomplish this task with a root department. However, querying a department with multiple depths is proving to be a little harder. For example:

http://localhost/departments.asp?d3/ (Should return <h2>welcome to department 3!</h2>)
http://localhost/departments.asp?d2/what-doing (Should return <h2>what is department 2 doing?</h2>)

I'm not sure if this can be create in one query or if there will need to be a recursive function of some sort. Also, if there is nothing after the last / then assume we want the index page.

This would basically be the back end of a small CMS. So my thought was that you could create new pages with a unique name associated to a category. Which in my case, would be departments. I have looked around and there aren't very many choices in terms of CMS for ASP Classic.

How can this be accomplished? Comments and suggestions also welcomed.

Thank you.

Answer 1

I did something very similar to this (except with PHP/MySQL). I don't have the code in front of me, but I would be happy to supply it to you if you think it might help:

I believe the main difference in my approach was that I didn't have a separate table with the nested set, I just had content and lft/rgt together in one table. The content management allowed the user to create a content hierarchy with as much depth as they dared, so the site could turn out something like:

http://site/tier1-page
http://site/tier1-page/tier2-pageA
http://site/tier1-page/tier2-pageB
http://site/tier1-page/tier2-pageB/tier3-pageI
http://site/tier1-page/tier2-pageB/tier3-pageII

...and so on.