Site Navigation in a Database Table

[Glowball]

Hi all, I'm really stumped here and I'm hoping some discussion will help. I need to represent our site navigation in a table in our database (I'm writing a small CMS for our site).

There will be a maximum of 5 tiers off of root but that's the only predictable thing. Each item will have an ID, name (to show in the navigation area), and URL.

I started off with these columns in addition to a parent_id column holding the ID of the item's parent in the hierarchy. That seems logical but I'm having a hard time figuring out how to work with it. Utimately I'd like to build an array or XML object with the query results in it.

Does anyone have any pointers or thoughts about this? I'm stumped. Thanks!

 

[carp]

What RDBMS are you using? That may dictate which options are available to you.
What format are you going to use to display the structure, and what would you like it to do? That will help us figure out what it is you are trying to achieve.

 

[r937]

i've got a scenario almost identical to yours -- maximum 5 levels, although my top level is site (i store multiple sites in the same cms tables)

here's my nav query:

select coalesce(sub3.id
               ,sub2.id
               ,sub.id
               ,nav.id
               ,site.id)   as this_id
     , coalesce(sub3.name
               ,sub2.name
               ,sub.name
               ,nav.name
               ,site.name) as this_name
     , coalesce(sub3.url
               ,sub2.url
               ,sub.url
               ,nav.url
               ,site.url)  as this_url
     , site.id    as site_id   
     , site.seq   as site_seq  
     , site.name  as site_name 
     , site.url   as site_url  
     , nav.id     as nav_id    
     , nav.seq    as nav_seq   
     , nav.name   as nav_name  
     , nav.url    as nav_url   
     , sub.id     as sub_id    
     , sub.seq    as sub_seq   
     , sub.name   as sub_name  
     , sub.url    as sub_url   
     , sub2.id    as sub2_id   
     , sub2.seq   as sub2_seq  
     , sub2.name  as sub2_name 
     , sub2.url   as sub2_url  
     , sub3.id    as sub3_id   
     , sub3.seq   as sub3_seq  
     , sub3.name  as sub3_name
     , sub3.url   as sub3_url 
     , concat(             site.name
      ,coalesce(concat(',',nav.name),'')
      ,coalesce(concat(',',sub.name),'')
      ,coalesce(concat(',',sub2.name),'')
      ,coalesce(concat(',',sub3.name),'')
             )             as breadcrumb_names
     , concat(             site.url
      ,coalesce(concat(',',nav.url),'')
      ,coalesce(concat(',',sub.url),'')
      ,coalesce(concat(',',sub2.url),'')
      ,coalesce(concat(',',sub3.url),'')
             )             as breadcrumb_urls
  from entries as site
left outer
  join entries as nav
    on nav.parentid = site.id
left outer
  join entries as sub
    on sub.parentid = nav.id
left outer
  join entries as sub2
    on sub2.parentid = sub.id
left outer
  join entries as sub3
    on sub3.parentid = sub2.id
 where site.parentid is null  
order
    by site.seq              
     , nav.seq           
     , sub.seq        
     , sub2.seq     
     , sub3.seq

this query returns the entire navigation tree (note the WHERE clause selects only root nodes)

any questions about it, please ask


r937.com | rudy.ca

 

[Glowball]

I'm using SQL Server 2000 and ColdFusion MX 7. It's for a Content Management System so I'll be using it for a few things:

- to hold ID numbers of pages in the system so I know where the content blocks go in the site.
- to put a label on a piece of content that users are working with, so they don't have to know it by its ID.
- to record page hits.
- to display a site map.

I've been reading about it at

http://www.sitepoint.com/article/hierarchical-data-database

and

http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html

and it seems like the best way would be to send separate queries for each node on the navigation tree. That seems so crazy, so I was trying to figure out another way so I could grab the info with one query and sort it all out in the code.

Our navigation isn't huge, so maybe I'm worrying about nothing.

 

[Glowball]

Thanks, r937, I see what you mean! Let me mess with it and I'll post back in a bit.

 

[Glowball]

I messed with your query and that looks good, but I'm going to need to work with the structure a lot in pages where the user gets to work with navigation. What I ended up doing is one query to get everything, then ColdFusion stores that in memory and I can "query" the query there instead of in the database. I'm doing that over and over to build the 5-level hierarchy, then putting that in an array I can work with on any of the pages.

It's not as pretty as I would have liked, but at least it's flexible. Thanks for everyone's input, having people to bounce ideas back and forth with is always good.

 

[r937]

you're querying the table over and over in a query-of-query to build the hierarchy? and you're sticking it into an array?

my single query builds the hierarchy in a simple query object, and since a query object is an array...

i don't know what you could've thought was missing

:sniff: :-(

r937.com | rudy.ca

 

[Glowball]

I admit that I changed your table and column names to mine and ran it and got some strange results but then I didn't mess with it much more. I'm now ending up with array items like

navtree.tier1[1].tier2[3].tier3[2].url

that equals "blah/blah/index.cfm" for example. I'd prefer one query but I guess I'm not sure about what kind of recordset you're ending up with. I will rarely need a list of everything. Most of the time I'll need one node, for example I might need just navtree.tier1[1].tier2 returned.

Everything I have been reading says that the approach I'm doing is just about as good as it gets, even though it's slow if you have a lot of trips to the database (and I'll only have one). I actually followed the array layout used by the Macromedia CMS (Spectra). I'm going to cache it, too, so that should really help.

I'll take a look at yours again, though, thanks! Yours is definitely simpler.

 

[r937]

as i said, my query returns the entire hierarchy

for a web site, this nav, or part of it, would appear on every page, yes?

okay, you wouldn't necessarily display all branches on every page, but you would handle this in your CFOUTPUT logic, and not by re-querying the hierarchcy differently on every different page

using CACHEDWITHIN, my single query is retrieved once per day, not once per page per visitor

if, as you say, you're making only one trip to the database, is that one trip per page, but different trips for different pages? that all adds to overhead, doesn't it

i think CACHEDWITHIN set to once per day is even better ;-)

r937.com | rudy.ca

 

[Glowball]

It's stupid but we won't be using this for the actual site -- this list is just for the administration side of it. So the only ones doing the query are the staff members who have access to the content. I don't see it being used very often after the novelty wears off. CACHEDWITHIN is how I'll be caching the query (sorry, didn't know anyone here knew ColdFusion).

I'll try to get your query to work with my data. It looks like we have the same columns and everything.