All the leaves go down...
Saturday May 25, 2002
I've been for a walk...
As I walked across the Humber River bridge on Dundas the other day, near where we used to live in 1954 when Hurricane Hazel hammered Toronto, trying (unsuccessfully, it turns out) to get that song out of my head, I thought about the design of database tables which would implement a web site hierarchy. (I do some of my best thinking while out walking, or cycling. My rule is: if you can't see the design in your head, you don't know it well enough -- go outside!)
A web site hierarchy can be implemented in a database as a reflexive or recursive relationship. This is actually a dead simple design, called the adjacency model. There's one record type, which relates to itself. A recursive relationship is often called a tree. To traverse a tree, you start at the root, which is at the top (go figure) and go down from there, one level at a time. You could also "land" on the tree anywhere, and then either go up or down, as the case may be.
By way of illustration, consider file folders in Windows. Each folder is a tree, although traversing nested folders is tedious, because you have to keep clicking on the nested folders to open them up. In "classic" mode, there's a cute little plus sign you can click on, but at least in classic you can see an expanded hierarchy (after you've gone down a whole bunch of levels and not found what you were looking for). Wouldn't it be nice to open all nested folders, to go down all the leaves, all at once?
Anyway, the path you take traversing a tree starting at the root is called a breadcrumb trail. It is common for web sites to display a breadcrumb trail at the top of each page, to orient you to where you are in their site hierarchy. If you overlay all possible breadcrumb trails, you end up with the site map.
A site map is just the web site's hierarchy, fully exploded. So if you have the site hierarchy properly stored in the database, the SiteMap page can be coded once and done. Just go down all the levels of the tree. Breadcrumbs, and in fact navigation bars, can be reduced to simple "include" modules on each page, such that the module logic simply looks at where the page is in the tree. Easy, if the site hierarchy is stored in the database, and not coded on each page.
Also, you'd want an admin screen to Add/Change/Delete a tree level. This is the simplest admin function there is -- remember, it needs to maintain just the one record type, which relates to itself.
So add a new tree level, and poof! -- the site's nav bars, breadcrumbs, and site map have all been updated.
Okay, I've designed the database. Now I can start coding.
Update July 2006
I've written up a more detailed explanation of how to write queries to produce breadcrumbs, nav bars, and site maps in Categories and Subcategories.
