Nathan Wallace writes:

This is one of the more interesting problems to solve using a relational
database...<g>

If you can get a hold of "SQL for Smarties" by Joe Celko it has a great
chapter on solving this problem.  The problem with the common solution
of just keeping a pointer to the parent is that you can end up needing
recursive queries.

The solution is one of those "very simple but I would never have thought
of it" approaches.  Basically you have to think of the folders as sets. 
So, Linux would be a big set that contained smaller sets like Red Hat
and Mandrake.  Each of those sets can also contain smaller sets and so
on down your hierarchy.

You then lay these sets out flat and give each one a left and right
index.

     3--RPM--4 5--GIMP-6

   2------RedHat---------7  8-----------Mandrake----------9

 1---------------------Linux--------------------------------10

Notice how the indexes for linux completely contain all the other
indices.

You can do really cool queries with this structure like show me all
parents for the folder called $id:

    select *
    from   folders as parents, folders as children
    where  parents.leftindex < children.leftindex
    and    parents.rightindex > children.rightindex
    and    children.id = $id

or show me all the leaves of the tree:

    select *
    from   folders
    where  leftindex = rightindex - 1;

and so on...

The inserts get pretty hairy though as you have to update all the
indices...

BTW, databases like Oracle have a heap of SQL extensions to let you do
hierarchical stuff much more easily...

Don't you wish you were using an object database...<g>