Also: inserts are practically free compared to the linked article, where they require updating the left and right numbering for every following node!
Another nice property: if you make entries in the materialized tree column constant-width (e.g. by zero-padding), an alphabetical sort by that column will give you a depth-first dump of the tree -- the exact order you'd like for, say, a comment thread or a table of contents.
When I've implemented materialized paths in the past, I have run into issues with the maximum allowed length of indexable string types (which limits the tree depth), but this was in the long ago late 90s. :) I think it's a very nice albeit imperfect way of storing certain types of trees, especially ones that are mostly insert+query-only.
As I said above, if you are using the materialized path for the type of problem it's best at solving, the speed differences won't matter so much. But that's primarily because the tree's aren't particularly deeply nested and/or that tree table itself isn't overly large. So in essence you are trading computational complexity for ease of use on smaller sets of data. In many cases that's exactly what's needed. On the other hand, if you will be modeling very large trees, or will have a huge number of them, nested sets are more efficient in terms of encoding and storage, as well as lookups and retrievals. The down side is that nested sets are more complex to setup and work with, and make understanding the structure of your trees more difficult.
IMHO, it's important not to fall into the trap that one technology/tool/solution/data structure will solve all of your problems. It's good to know the pro's and con's of different solutions and which problems they are most efficient at solving.
For short trees and in-memory management another option would be:
electronics
/televisions
//tube
//lcd
//plasma
/portable electronics
//mp3 players
///flash
//cd players
//2 way radios
Where depth levels are represented by slashes only.Replace with indexes and you have a shorter version.
Let's see what else we can come up with just for fun...
Manage and sort in-memory and store already ordered when done (no matter if it is physically unordered).
That's why I said, for small trees and in-memory management.
In the materialized path strategy, each node/row has its full path in the tree stored in a column and that path is "materialized" or "derived" from a depth-first traversal to that node. In conjunction with the adjacency list approach this could yield much better results in most situations than a nested set model.
Taking the tree used in the article, the paths would look like (using '/' for path delimiter but you can use whatever you like):
Alternatively it would make more sense to base the materialized path off of an immutable field such as the PKey of the record (in the same order as the paths above, from the article): This has the following advantages: Downsides: Hope this helps someone!Cheers, -Zach