I spotted a great piece of information from a link in Mike Clark's post on the ActiveRecord library used in Rails.

That piece of information was an article at SitePoint.com about something called modified preorder tree traversal. It's essentially a fast algorithm for query operations on a hierarchical set of data stored in a relational database -- a problem we usually tackle with an adjacency list, i.e. tacking a "parent_id" column on each record and perform a series of database queries in order to fetch a (sub)tree of records.

The modified preorder tree traversal algorithm is a funky one. I won't explain it in detail here (go read the SitePoint article for an excellent description!) but it's basically a way to structure your hierarchical data in a relational database in a way that let's you fetch an arbitrary subtree from the database using just two (2) SQL queries. The first one is for fetching details for the root node of the subtree and the second one is to fetch all the children and grandchildren of the root node.

The trade-off is two-fold:
1) This solution is less intuitive than the adjacency list approach because of its algorithmic flavour
2) This solution makes updating/deleting/inserting records a bit expensive because we need to update the index (the index is what makes the SELECT operations lightning fast)

Check it out if you haven't heard of it already. I have to admit that I've never been good at algorithms nor specifically interested in them. Yet, I'm excited to learn new ones that are actually useful!

Thanks, Mike!