Jan 12, 2003, 7:53 AM
Post #1 of 8
mysql and perl - stumped
I am working on creating a script for a multi-level-marketing(MLM) company and I ran into a problem I did not even consider.
First off, the MLM is going to be a "forced matrix" 3x4.
It's going to be like a pyramid. The rules of "forced matrix" means there will be spillover if a level is filled. The 3x4 means the pyramid will be 4 levels deep, and each person in the pyramid can have no more than 3 people directly under them.
Spillover - Spillover is what happens when someone goes to the members page and signs up, but the user already has 3 people under him/her, that new signup should be placed in the next available spot (closest to the top) in the pyramid.
Example: Joe signed up. He referred mary, beth, and ann. Another person goes to joe's unique signup page and signs up. Since joe already has 3 people in his first level, the new signup is placed under mary. Now if mary already had 3 people also, then the new member would be under beth. This would keep going until the script found a member without 3 people in there first level. It could in fact go further than 4 levels, because the pyramid is really endless in an admin point of view.
I I I level1 "3"
I I I I I I I I I level2 "9"
III III III III III III III III III level3 "27"
IIIIIIIIIIIIII IIIIIIIIIIII IIIIIIIIIII level4 "81"
If I illustrated a level5 it would have 243 people on it, and everybody on it would be the level1 of someone on level4.
From an admin point of view this pyramid will be of endless levels, because only one person can be at the top. Each "I" is a new person in the pyramid. From the users point of view, (when viewing there downline) they will see themselves at the top of "there" pyramid, but in reality, many people will already be part of the pyramid that is above them(which they can not see). Example: Someone on Level4 refers there first new signup... It would appear to the person who referred them that the signup is in there first level, but to the person at the very top of the pyramid, the person would actually be in level5 of the pyramid, (but that person can only see 4 levels deep, so he would never know)
Now for my problem
I have created all the members area, and admin area. The users can login, view there downline (4 levels down), view there upline(4 levels up). Now I am working on the actual signup part, and I have found it quite hard to imagine a way to keep the server load down yet place a member in the next open spot in the pyramid. The first few levels I could do fine, but let's say this pyramid get's HUGE.
Let's say Joe is the guy at the top of the pyramid.
He has all his levels filled with other people who signed up. Now he signs up yet another person, the script would have to check each person under joe, and keep going through till it finds someone without 3 people. Let's say it starts checking people in level4... that's 81 different users the script would have to go through to see if they each had 3 members in there personal first level.
Let's say everyone on the 4th level already each had 3 people also. Then the script would have to check everybody on the next level (243 people) to see if they all have 3 members.
As you can see, this could become a massive server load if it had to keep checking all these people individually.
Here is how the database is setup now.
username, password, IDNumber, upline1, upline2, upline3, upline4, and other fields that pertain to personal information.
Let's look at joes row in the database:
upline1=tom321 #the person who referred joe
upline2=mary983 #the person who referred tom
upline3=vince454 #the person who referred mary
upline4=master423 #the person who referred vince
The reason I set it up this way is to easily show Joe how many people are in his downline. Here's how viewing the downline works:
$query = "SELECT COUNT(*) FROM mlm WHERE upline1 = Joe123";
$query = "SELECT COUNT(*) FROM mlm WHERE upline2 = Joe123";
This would show Joe how many people are in each level of his downline.
When I first set this up, I was thinking, ok all I have to do is a SELECT COUNT for joe's id from upline1, if there is less than 3 then the person signing up could go in joes downline... If it was full...I could just store an array of idnumbers of each person in joe's first level, and then do the same check on all them.
Obviously I did not think it all the way through, because I never imagined an array with 243 idnumbers, and checking each one to see if they have 3 people. Heck, depending on how good this company does, the script could be checking 1000's of people just for one simple signup. As you can see that is exactly where my problem is. I don't want a signup to take 20 mins because the user is waiting for a response from the script, I don't want the script bringing the server to it's knees.
So basically what I need are some ideas
(they don't have to be actual code(unless you are really nice(or bored))). I already know I am going to be starting from scratch, but that is ok, because I have not really put much time into the programming aspect of this, except for the time thinking about a solution.
What I need from you are ideas about the format of the database, and ideas on how to keep the server load low, but still keep this a forced matrix, and make sure the person signing up is put in the right spot in the pyramid.
Thanks for your time,