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

    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

    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



    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
    San Sebastian, Spain
    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)
    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