Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    sa
    Posts
    4

    Question Unanswered: 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
    Ahm

  2. #2
    Join Date
    Mar 2004
    Posts
    80

    Re: counting a pyramid db

    assuming 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

  3. #3
    Join Date
    Mar 2004
    Location
    sa
    Posts
    4
    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

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    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

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have you considered switching to the nested sets 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

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2004
    Location
    sa
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •