Neat tricks with Oracle

The task: provide a neat hierarchical tree as a web interface to a project management system where any project might have a variable number of sub-projects. A tree structure in other words. So I ended up with this:

ID Project Name Parent ID
1 The Mega Project
2 First Sub-Project 1
3 Second Sub-Project 1
4 Another Sub-Project 2

Nice and neat… the only problem is that on the face of it, relational databases aren’t very good at handling trees, and after a bit of experimentation I was ready to write a whole lot of procedural code in ASP to take a recordset and build a hierarchy from it. But of course I googled first. The initial responses included Joe Celko’s (in)famous nested-set tree structure which was far too complicated for my feeble mind to grasp and didn’t look as if it would provide a simple interface for users to add new projects.

And then I came across Philip Greenspun’s interpretation of the classic problem of an employee table in which some employees are managed by others who in turn are managed by others, all the way up to the big boss. He used the Oracle-specific CONNECT BY PRIOR which I’d come across elsewhere, but explained it in a way I could actually understand. Just the ticket! So with a single SQL statement I now had my neat hierarchical recordset.

Next: how to present it. Serendipitously, I happened to be looking at Matt Kruse’s Javascript Toolbox for another reason and noticed a DHTML Tree. I like his stuff, so I had a look. Magic! A simple bulleted list is transformed into an expanding and collapsing tree with a few lines of CSS and a sprinkling of Javascript. The nice thing is that in non-CSS and non-Javascript browsers it degrades to a simple list so it’s accessible too. My brain steamed lightly as I wrestled with the logic to get the opening and closing list tags in the right places as I read through the recordset, but a couple of hours’ work had given me an ideal solution and in another half hour I had a simple form for adding new projects at any level in the structure. So humble thanks to Philip and Matt for being far cleverer than I will ever be!

This entry was posted in Databases, Javascript, PHP, Web development and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>