Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: Problem with tree database design and traversal

    Hi all,
    I've to develop a multilevel marketing web app. In this Im facing an issue, please help me in this issue

    MemberX
    \/
    child1 child2 child3 child4 child5 child6 child7
    \/
    child11 child22 child33 child44 child55 child66 child77

    each child can have 7 childs and upto 7 levels.

    my table design is as following

    tree
    -----

    id
    parent_id
    position
    level


    id | parent_id | position | level
    ---------------------------------
    2 | 1 | 0 | 1
    3 | 1 | 1 | 1
    4 | 1 | 2 | 1
    5 | 1 | 3 | 1
    6 | 2 | 1 | 1


    My problem is how to find a vacant position for given parent.

    eg: when member 2 (id=2) is adding a new member i've to add the new member 7 in to a vacant position
    under him or under any of his descendants.
    Is there any way find a vacacnt position ? Or any alternate database design for this purpose ?
    Pls help...

    Thanks & regards

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    There are several ways of handling this. If you know that you will have a max of 7 under each parent then you could add something as follows:

    Parent: A
    Children of A: AA, AB, AC, AD, AE, AF, AG
    Child of AE: AEA, AEB, AEC, AED, AEF, AEG

    and so on.
    If you have more than 7 and this can be any number then there are other techniques which require an update of each record in the hierarchy giving it a unique number. I would stick to the easier option above if you are sure with the max 7.

    Finding the MAX(id) FROM table WHERE parentid = {id_you_are_searching}
    The next entry is the available one for selection.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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