counting a pyramid db

hiya all,
I got a table that is represented as a B-Tree, it has a one to
many relation with its self
----
| UserID |
----
|FatherID|
----

UserID = FatherID
it looks like this
o
/// \\\
ooo ooo
//\\\ ///\\\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.

I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.

regards
Ahmassuming this structure in your DB
UserID INT Primarykey
FatherID INT
Counter Int

You may go for a while loop in a trigger to update counters of father and forefathers.

Originally posted by elamor

UserID = FatherID
it looks like this
o
/// \\\
ooo ooo
//\\\ ///\\\
oo ooo ooo
I want to get all the nodes that has 256 children and grand children and each node is limited to have 6 nodes under it.
any ideas ? the DB isn't filled with data yet so we can add more fields
to the table if it helps.

I could write a trigger that increments a counter in each node but it will be a recursive trigger so it can go from the child to its father and the father to its father upating them and so on.

regards
Ahm|||theguru, thanks for the reply I know how to do that but I'm looking for
a better and an optimized why to do it. imagine having 1000 new inserted records and the trigger is fired to update all the parents
what could happen to the DB server ? :) I think you got the idea now

regards|||ok I too have simmilar problem.so i am looking forward for a better way.
I will give u the code i am currently using in my next reply.
thanks.

Originally posted by elamor
theguru, thanks for the reply I know how to do that but I'm looking for
a better and an optimized why to do it. imagine having 1000 new inserted records and the trigger is fired to update all the parents
what could happen to the DB server ? :) I think you got the idea now
regards|||one thing with recursive triggers is the absolute necesity to check for the recurse.
dont forget to test for @.@.nestlevel which will give you your countdown before 32 levels.|||Have you considered switching to the nested sets (http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci801943,00.html) model? It is a two-edged sword, in that it puts the processing time up front to construct the sets, but since queries are usually much more frequent than builds, that isn't a big problem.

-PatP|||I've created many recursive relationships in my databases, but I've found that recursive triggers in SQL are not nearly as efficient as accumulating changes in a table variable and then updating all the records in the b-tree with one join to the table variable.|||seems that it's not working at all I tried different way of designing the
db but with no luck , I'll get back when I find a good answer for this

and thanks for all of you who replied

regards
Ahm