My solution to that: store representations of trees as simple paths like "/trunk/branch/leaf1/", "/trunk/branch/leaf2/", etc. And just rely on the application layer to parse it all out into a friendly IU. When you want a specific branch, just do "select * from table where signature like '/trunk/branch/%'
In my experience, that is obscenely slow..
Not practical whatsoever.
not if you cache your data structures intelligently... probably not a good solution for something like PHP or Rails where you lose your application state across requests
Or do you mean that using "like" is slow? i would think that it would not be as long as that column is indexed.
It depends on how fast you need to do it. In Rails it's as easy as person.apartment.landlord.house.city.state.country.continent.planet.name.
Perhaps I should have said querying recursive data structures. E.g. you can easily represent a family tree in a RDB, but it's not so easy to write a query to get all ancestors of a specific person.
Good point. It's also not the end of the world in many cases to use 2 queries (or even more if each one is blazing fast). Just check your data first to make sure you don't have O(n) where n can actually be 5,10,20+.