This morning I found this article on del.icio.us.
It details a way of storing tree structures within an SQL database that makes use of the strengths of SQL to speed up the manipulation of the tree.
Its very interesting, as I read it, everything in me rebelled against it. My initial feeling was that maintenance of the “left” and “right” values would be a problem. Using a classic procedural language this would have been the case, with SQL its a snap.
There is one problem, it is best to maintain both ways of relating the data.
One scenario is particularly difficult (in my eyes) to do with the left-right numbers.
Producing the list of direct children of a particular node.
Good article, definitely worth a read.