Designing a table to track Downline and Upline levels
Posted on 2006-05-17
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