That was nice and vague, Rudy. lol.
One other disadvantage i didnt mention about NSM is level-retrieval. Retrieving all the members of a certain level of the tree gets pretty nasty in NSM.
yeah, sorry, i was responding to the post above yours, i didn't see that you posted a couple of minutes before i did
my "wow" reaction was that after about six dozen posts of you trying to teach him the nested set model, the light suddenly comes on and he embraces the adjacency model
@Rudy yes I finally got the adjacency very straight forward running out of the Monster NSM but not 100% if it will be suitable for this case.
@starlion I have to say that SELECTING will be as Big as INSERTING and UPDATING in this case, and using Information about parent red to display it through red could be of used, which from my understanding about what you guys have talk I think NSM is more convinience for that. INSERTING is a key here. and as I was thinking this yesterday what about if red would not have a parent fruit then how the relationship of a grandfather red would be possible in between red and FOOD? can that be accomplished in adjacency?
for that I was thinking about creating another field that would serve as a reference, besides the actual name Red and Food besides the father Fruit just in case fruit weren't there.
I found this link very interesting Sql Antipatterns Strike Back it talks about the different kind of trees after slide 48, one of the trees that will make everything ease is forming a closure table, this table sounds very interesting where it builds a relationship with each descendant, making a child not dependent of a parent to form a relationship with other descendent. I think that way it's better because for red to establish a relationship in between beef red won't depend on fruit to establish that relationship. is it possible to establish a relationship in between red and beef if fruit is not there through adjacency?
The only thing I see is a lot of database space usage, tons of rows I am not sure if that would slow down the database in the long run, doubt it.
Well the way you originally described your query, you wanted to retrieve a chain of nodes - which said NSM to me.
Then you changed your mind and said you wanted to pull single-level breadth queries, which does change the model preference.
I cant look at the slides in question because apparantly at work they block that sort of thing, but i'll take a look when i get home tonight.