My problem is very complex and related to database design, search and update.
I need to know how can I update multiple records in a table depending on some condition and how to perform depth first search and also what database design should be used in this case.
Here a tree like structure of members is created. This is/was a business scheme used by most web sites to earn.
I have to create a database where members' weekly commission calculation and updation has to be done.
In this case each member will bring two new members, which will be his children. One member can have only two children(right and left leg). Each member below first will have some parent Whenever a new member comes he must have been referred by some other member(parent). So other(old) member becomes new member's parent. Here a tree like structure is created where tree starts from each parent.
Whenever new member is added his parent gets some % of commission. So this commission has to be added to parent members total and weekly commission. It doesn't end here. Parent's parent and then his parent...up to 10 levels above new member will also get some commission. For example new member's parent will get 5%--his parent will get 2.0%--his parent will get 1.9% and so on up to ten levels above or if there are less than 10 levels, up to last level. So here at least 10 records have to be updated for total commission updation and 10 new records(to some table) have to be added because of weekly commission calculation of each member's commission whenever a new member is added. Maybe one more record have to be added for spill over calculation (discussed below).
Second part is to find a parent for the new member in case of spill over(when old member who brought new member already has two children).For example member 1 has two child member2 and member3.
..................(left leg)......Member1..........(right leg)
....................................|
.....................--------------------------------
....................|................................|
..................Member2........................Member3
....................|
................--------------
...............|..............|
............Member4..........Member5
...........-----------......---------
..........|...........|....|.........|
.......Member6
Member1 cannot add new member under him (as child) because his both legs are already occupied by Member2 and Member3. But because of spill over when a new member7 is referenced (introduced) by Member1 he can be added somewhere down under Member1's tree. So these steps have to be performed when a new member7 is introduced by member1.
1. We'll first check member1's left and right leg; because these are occupied by member2 and member3 respectively
2. We'll check to see if member 2's left or right leg are free. Because these are occupied by member4 and Member5
3. We'll check member4's left and right leg. Left leg of member4 is occupied by member6, but because right leg of member4 is free it can be added below member4. So it will look like this:
..................(left leg)......Member1..........(right leg)
....................................|
....................--------------------------------
...................|................................|
..................Member2........................Member3
....................|
................--------------
...............|..............|
............Member4..........Member5
...........-----------......---------
..........|...........|....|.........|
.......Member6.....Member7
Each parent above new Member7 (member4--member2 --member1--his parent...and up to 10 levels or last level if 10 levels haven't been yet created, which is the case in this tree) will get some % commission.
Member 1 will also get spill over benefit along with his regular commission because new member is added below his tree. When designing db one more field has to be added to some table because we also want to keep track of each member's spill overs.(This results in one more addition to total records/fields to be updated when new member is added)
So, problem when adding a new member is finding a free slot under Member1 i.e how to transverse the tree to find that place under member1's tree is free or not. First search will be performed under Member1's left leg(member2's left and right, then member4's left and right...) up to 10 levels below him.
If we are unable to find a free slot below member1's left leg up to level 10 then it'll start again from Member1's right leg and search will continue but this time right to left. For example if there were memberA and MemberB under Member3 like this:
..................(left leg)......Member1..........(right leg)
....................................|
.....................--------------------------------
...................|................................|
..................Member2........................Member3
....................|.......................... ....|....
................--------------.................------------
...............|..............|...............|............|
............Member4..........Member5........MemberA.....MemberB
...........-----------................................--------
..........|..........|........................................|........|
.....Member6....Member7...................................Member8
and a new Member 8 is again introduced by Member1. Supposing slot below Member1's left leg up to level 10 is not free. It'll search Member3's right leg(occupied by MemberB) and then Member3's left leg(Occupied by MemberA). Because no free slot was found it'll check MemberB's right (occupied by memberC)and MemberB's left leg (free). So Member8 will be add under MemberB's right leg.
This is the story. How this thing can be achieved using SQL Server 7.0 or Oracle etc. So, problem is:
1. Designing a good database (which tables, how many tables taking into consideration member info, his total and weekly commission, spill over commission, no of spill overs and under whom spilled over member was added etc).
2. Updating Multiple rows in one go.
3. In case of spill over to find a free slot, starting at level 0's left leg(level 1), searching level 1's left and right leg and moving down at level 1+1, searching left and right leg moving down again at level 1+1+1 ...up to 10 level
If no free slot found under Level 0's left leg then move to level 0 again, start at level 0's right leg this time(level 1) searching level 1's right and then left leg, moving down to level 1+1, searching right and then left leg moving down again at level 1+1+1 ...up to 10.
Example of sites where things like these are used is and skynet.com and 100's more. Where every person can view his tree and view his commission etc.
I know stored procedures and triggers have to be used but I need some expert advice about how this can be done efficiently? If you can please suggest a way to search this tree like structure can be managed.
I need to know how can I update multiple records in a table depending on some condition and how to perform depth first search and also what database design should be used in this case.
Here a tree like structure of members is created. This is/was a business scheme used by most web sites to earn.
I have to create a database where members' weekly commission calculation and updation has to be done.
In this case each member will bring two new members, which will be his children. One member can have only two children(right and left leg). Each member below first will have some parent Whenever a new member comes he must have been referred by some other member(parent). So other(old) member becomes new member's parent. Here a tree like structure is created where tree starts from each parent.
Whenever new member is added his parent gets some % of commission. So this commission has to be added to parent members total and weekly commission. It doesn't end here. Parent's parent and then his parent...up to 10 levels above new member will also get some commission. For example new member's parent will get 5%--his parent will get 2.0%--his parent will get 1.9% and so on up to ten levels above or if there are less than 10 levels, up to last level. So here at least 10 records have to be updated for total commission updation and 10 new records(to some table) have to be added because of weekly commission calculation of each member's commission whenever a new member is added. Maybe one more record have to be added for spill over calculation (discussed below).
Second part is to find a parent for the new member in case of spill over(when old member who brought new member already has two children).For example member 1 has two child member2 and member3.
..................(left leg)......Member1..........(right leg)
....................................|
.....................--------------------------------
....................|................................|
..................Member2........................Member3
....................|
................--------------
...............|..............|
............Member4..........Member5
...........-----------......---------
..........|...........|....|.........|
.......Member6
Member1 cannot add new member under him (as child) because his both legs are already occupied by Member2 and Member3. But because of spill over when a new member7 is referenced (introduced) by Member1 he can be added somewhere down under Member1's tree. So these steps have to be performed when a new member7 is introduced by member1.
1. We'll first check member1's left and right leg; because these are occupied by member2 and member3 respectively
2. We'll check to see if member 2's left or right leg are free. Because these are occupied by member4 and Member5
3. We'll check member4's left and right leg. Left leg of member4 is occupied by member6, but because right leg of member4 is free it can be added below member4. So it will look like this:
..................(left leg)......Member1..........(right leg)
....................................|
....................--------------------------------
...................|................................|
..................Member2........................Member3
....................|
................--------------
...............|..............|
............Member4..........Member5
...........-----------......---------
..........|...........|....|.........|
.......Member6.....Member7
Each parent above new Member7 (member4--member2 --member1--his parent...and up to 10 levels or last level if 10 levels haven't been yet created, which is the case in this tree) will get some % commission.
Member 1 will also get spill over benefit along with his regular commission because new member is added below his tree. When designing db one more field has to be added to some table because we also want to keep track of each member's spill overs.(This results in one more addition to total records/fields to be updated when new member is added)
So, problem when adding a new member is finding a free slot under Member1 i.e how to transverse the tree to find that place under member1's tree is free or not. First search will be performed under Member1's left leg(member2's left and right, then member4's left and right...) up to 10 levels below him.
If we are unable to find a free slot below member1's left leg up to level 10 then it'll start again from Member1's right leg and search will continue but this time right to left. For example if there were memberA and MemberB under Member3 like this:
..................(left leg)......Member1..........(right leg)
....................................|
.....................--------------------------------
...................|................................|
..................Member2........................Member3
....................|.......................... ....|....
................--------------.................------------
...............|..............|...............|............|
............Member4..........Member5........MemberA.....MemberB
...........-----------................................--------
..........|..........|........................................|........|
.....Member6....Member7...................................Member8
and a new Member 8 is again introduced by Member1. Supposing slot below Member1's left leg up to level 10 is not free. It'll search Member3's right leg(occupied by MemberB) and then Member3's left leg(Occupied by MemberA). Because no free slot was found it'll check MemberB's right (occupied by memberC)and MemberB's left leg (free). So Member8 will be add under MemberB's right leg.
This is the story. How this thing can be achieved using SQL Server 7.0 or Oracle etc. So, problem is:
1. Designing a good database (which tables, how many tables taking into consideration member info, his total and weekly commission, spill over commission, no of spill overs and under whom spilled over member was added etc).
2. Updating Multiple rows in one go.
3. In case of spill over to find a free slot, starting at level 0's left leg(level 1), searching level 1's left and right leg and moving down at level 1+1, searching left and right leg moving down again at level 1+1+1 ...up to 10 level
If no free slot found under Level 0's left leg then move to level 0 again, start at level 0's right leg this time(level 1) searching level 1's right and then left leg, moving down to level 1+1, searching right and then left leg moving down again at level 1+1+1 ...up to 10.
Example of sites where things like these are used is and skynet.com and 100's more. Where every person can view his tree and view his commission etc.
I know stored procedures and triggers have to be used but I need some expert advice about how this can be done efficiently? If you can please suggest a way to search this tree like structure can be managed.