Traversing tree-like data in a relational database using SQL
Celko's book is a good resource - if a bit overly "academic" at times.

I also have really found  this method, known as 'closure tables' to work fairly well.

If you're using a database that allows recursive CTEs (such as PostgreSQL 8.4 or newer, or SQL Server 2005 or newer), they're really the best way to go. If you're on Oracle, there's always the venerable "connect by".

It is my experience that it's far more common to be handed a set of tables in a "naive tree" schema, and have to figure out how to extract the correct tree from that storage, than it is to have an opportunity to create the cleaner "closure tables" structure.