@LoztInSpace said:With apologies to the "I hate Oracle" members, check out Oracle's
CONNECT BY
ORDER SIBLINGS BY
START WITH
and LEVEL
A thing of beauty for this type of thing.
I agree here... Long time Oracle hater... But I know and love these functions, even as a SQL-Server guy (And Oracle 10 even has some new ones, so you can retrieve the root element better... I think it was called "connectByRoot")
But I dont see these functions as a big showstopper for any other dbms. Even on SQL Server 2000 it was not hard to perform recursive querries, and still keep using a set based aproach. The suggested nested sets offer a great performance boost, since you are able to retrieve the data you need from a single index scan, but it was already mentioned that those solutions require a constant "upkeep" so you can keep your data in shape. And since I am a fierce "hater of Triggers" (There are some valid uses, but I never encountered a trigger that was doing something "valid"), I dont trust them doing something reliable... ;)
So, if you have static data, then you can consider a nested set. But what happens if you have a record, and you need to move, or delete it with a precomputed nested set? Say from position 17? This means you have to renumber a whole lot of records if the set contains 10.000.354.123 rows...
The important thing is that you need to know what you want to do with the data. Small trees, which contain only product groups, or geographical location might be a valid candidate for those. For big trees, that "wont fit in memory", a nested set might be a problem, which will only show once you got more records, then you most likely have in your test DB...