Designing a table to track Downline and Upline levels

Posted on 2006-05-17
Last Modified: 2012-08-13
Hopefully this is in the right section - if not please direct me to the correct section to post this question.

I have a system almost ready to market, via  a network, however I want to achieve two things:

1.  Limit the amount paid from each sale
2.  Ensure that the people doing the work are rewarded for their efforts

I want to have three levels:


where Gold introduces people to be on the "silver level" and Silver introduces people to be on the "bronze level".  Bronze will get paid $1 for each aggregated sale made per month i.e. if they make 10 sales per week in the first month, then 20 sales per week in the second month, then by the end of the second month, they would have made 120 sales and receiving $120 per month.  Silver will receive 75c for each sale made by members of hte "bronze level" so in this example, if Silver had 3 x Bronze members making 10 sales / week for the first month, and 20 sales/week for the second month, then Silver's sales would be 3 * 120 = 360 * .75 = $270 per month.  Gold will receive 50c per sale made by "silver" so if the Gold member had 3 silver members with 3 bronze members each making the sales as described above, then Gold's sales would be 3*(3*120) = 1,080 * 50c = $540.

The brain teaser comes from the next part.  I want to encourage "bronze" to introduce people to make sales but rather than introduce another tier, do this.  When a "bronze" introduces people, then the "bronze" moves to the "silver" level, the "bronze's upline Silver" moves to "gold" and the 'previous' gold stays where they are except they do not receive income from the "new bronze" level.  In this example, a Bronze can introduce more people, and advance to Silver, and then encourage the new Bronze level to introduce more people and then advance to "Gold".  the highest level is "Gold" - and it is highly probable that a "bronze" could advance to "Gold" and be earning more than an original "gold" member.

I can make this system work on paper, however I need to make this work in a database and whilst I have a reasonable working knowledge of database design, I do not know how to solve this problem quickly or logically.

I surely hope someone can come up with a good answer for me.
Thanks in advance
Question by:lesleyag
    LVL 50

    Expert Comment

    i think you're viewing this in the wrong light...

    i think you basically have a



    where any individual can be at any of those specific levels dependant on the context....

    thus a parent can be a grandparent (gold) to a specifc child (Bronze)  but the grandparent can be a child to some other individual...

    I take it that oin the example where you have the Bronze recruit another member and becoming silver and his previous silver
    becoming GOLD you didn't mean for the  previous silvers GOLD to lose revenue?

    I think you want just a basic table  MemberHierarchy


    and you just restrict your navigation to 3 levels...


    Select A.memberID as Silver
      from MemberHierarchy as A
     Where IntroducerID = xxxx

    Select A.memberid as Bronze
       from MemberHierarchy as A
      Inner Join MemberHierarchy as B
          on A.Introducerid=b.memberid
      Where B.introducerID = xxx


    Author Comment

    thanks for this and such s quick response. I have just been doing some more playing on paper and wondered about the following structure. Instead of working from Gold to Bronze, work the other direction in that when a Bronze has 10 "sellers", then they advance to Silver. To advance to the Gold level, a minimum of 10 Bronze members need to have advanced to the Silver level.

    Does this make sense and if so, how would I track them?

    Author Comment

    Actually just re-reading your comments again, and I think you may have said what I have said above.  This being the case, will your structure you have suggested work in this scenario.
    LVL 50

    Expert Comment

    that would be accomplished by

    adding a "constraint"  to the revenue calculation
    such that

    a member "had" at least x other members pointing to them as Introducers...


      and exists (select Introducerid from memberhierarchy as x
                          where x.introducerid = A.introducerID
                         group by Introducerid
                         having count(*) = 10 )

    however that may mean that you do need to introduce a  "Status" code   to initially get the  "founding" gold and silver members
    working if you envisage them initially having < 10 "inductess?"


    Author Comment

    this is starting to make sense.  What i think would work is that all members start off as children, then they become Silver (parent) and finally Gold (Grand-parent) but I don't want to limit the number of children a parent has,.  However I wanted to allow a Child to become a Grandparent ahead of his/her parent if this makes sense.

    I would like to have the system so that when a "criteria" has been attained, then the "status" alters automatically from Bronze to Silver to Gold as I foresee this is a system which can snowball overnight so I need to ensure that the database can do its work whilst everyone else is "sleeping"

    Author Comment

    just to go over your suggested table:

    We currently have a User's table (holds our username and password etc).  In this table, would we then have another field for the IntroducerID?  IF so, do we then have another table called "Introducer" which then has a field called Status? and we alter this status when the criteria has been met?

    LVL 50

    Expert Comment

    you can probably have triggers on both a memberstatus table and the memberhierarchy table

    which would handle the statius changes..

    ie new member added  check for a status change requirement...

    then on memberstatus if a member moves from one level to another
    check to see if there introducer also gets an upgrade...

    should be able to run "real time" and not require an ovemight update... but either would work
    although an overnight status change would allow you more flexibility in maintaining status
    and the possibility of dealing with future introduce incentive schemes...  

    Author Comment

    so would the table structure I have suggested above, be workable for this system

    Author Comment

    so to clarify before I accept your answer and award the points.  

    The table structure would be:

    Tbl_user (member)



    Hope this all makes sense and thanks so far for your help, it is much appreciated
    LVL 50

    Accepted Solution

    >>We currently have a User's table (holds our username and password etc).  In this table, would we then have another field for the IntroducerID?  IF so, do we then have another table called "Introducer" which then has a field called Status? and we alter this status when the criteria has been met?

    this is the classic Normalisation/De-Normalisation (for performance)  scenario...

    you need to balance the fact that all the data relates to the User...
    but is used for different purposes and will change to differnt cycles/lifetimes...
    you also need to decide how you need to handle recording of changes (what needs to be audited, how do you store history,
    ... e.g. do you need to maintain a recently used password list to avoid re-use and simplistic changes, dependant on your companies
     security policy / legal requirements)

    i think i'd go for

    User Table
      USerid , MEMBERID, password, Name , Address , etc links

     MemberID , NAme address links , STATUS

    MemberHierarchy Table
    MemberID , IntroducerID , possibly some  dates
    PK MemberID
    and a clustered Index on IntroducerID
    An introducer id is just a MEMBERID

    I think you should look to expansion...
    and consider your members as potentially corporate entities
    so that each member  may itself has several users...



    LVL 50

    Expert Comment

    and as you have in your set of tables...

    a reference table
    for Status detailing the remuneration details...


    sorry I've got to go to work now...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now