• Status: Solved
• Priority: Medium
• Security: Public
• Views: 423

# Designing a table to track Downline and Upline levels

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:

Gold
Silver
Bronze

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.
Lesley
0
lesleyag
• 6
• 5
1 Solution

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

i think you basically have a

grandparent
parent
child

relationship

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

MemberID
IntroducerID
introducedDate
expireddate
...

e.g.

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

0

Author Commented:
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?
0

Author Commented:
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.
0

Commented:
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...

e.g.

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?"

hth
0

Author Commented:
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"
0

Author Commented:
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?

0

Commented:
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...
0

Author Commented:
so would the table structure I have suggested above, be workable for this system
0

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

The table structure would be:

Tbl_user (member)
------------------------
UserID
IntroducerID
DateJoined
expireddate
...

tbl_Introducer
---------------
IntroducerID
StatusID
Name...........
...............

tbl_Status
--------------
StatusID
Status
MthlyRemuneration

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

Commented:
>>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

MemberTable

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...

0

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

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

hth

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

## Featured Post

• 6
• 5
Tackle projects and never again get stuck behind a technical roadblock.