Complex database design and Record Addition/Updation

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.

Because I am not able to align these lines properly even after trying so many times I can explain the hierarchy:

1. member2 and member3 are member1's children
2. member4 and member5 are member2's children
3. member6 and member7 are member4's children
4. memberA and memberB are member3's children


..................(left leg)......Member1..........(right leg)
....................................|
.....................--------------------------------
....................|............................. ............|
..................Member2........................M ember3
....................|
................--------------
...............|.................|
............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........................M ember3
....................|
................----------------------
...............|...........................|
............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.....................Memb er3
....................|.......................... ...................|....
................--------------............................------------
...............|..............|................... ..........|...............|
............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 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 http://www.bigbanyantree.com and http://ltwebcarrier.com, 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.

Main text edited

do you seriously expect someone to answer this? this is a developers forum, not a copy-paste rfom college textbook forum...

If you don't know the answer don't reply. It wasn't copied and pasted I have written everthing myself. Because I had to create a project like this for my tests. Anyway I don't think you are good enough to reply thats why you are posting such replies.
Let me see if others also think like you

What is this database for?? college?? or for work? or a web site your setting up?

5-6 months ago my friend who is a web desinger showed me this project and he wanted to develop this web site. But we were unable to achieve that at that time.

I am doing msc so my teacher has created a web site like this http://www.ltwebcarrier.com. He said that he has done this using ms access only and asked me to do it as a project.

So this is the story. I always wanted to do it but as no one was around to help I posted this to this forum.

I know if I can somehow understand how to start I'll try to do it myself. How multiple rows etc can be updated **efficiently** etc.

I thought about a structure like this:

Supposing we are just starting. Where there are two tables:

MemberInfo and member.

If I just forget about commission at this moment and concentrate only on search to find free slot.

In tblMembers I think there should be fields MemberId, ParentId, leftleg(free), rightLeg(free), referralid, level. This level may be important at the time of calculating commission.

When any leg is free its value will be 0 (or negative )otherwise it'll be actual chidldid. So when searching to find a free slot to add a record I can do something like this:

create procedure p as
declare c int
declare leftfree int
declare rightfree int
select count=0, leftfree=0,rightfree=0

--first 1'll check left and right leg of old member who is referring a new member

select leftleg into leftfree, rightleg into rightfree from members where id=referralid

if leftfree=0 than
insert into member (parentid, referralId, level) values (referralid, referralid, count) --count is level
update member set leftfree=newmemberid where id=referralid
else if rightree=0 then
insert into member (parentid, referralId, level) values (referralid, referralid, count)
update member set rightfree=newmemberid where id=referralid
end if

--if left and right were not free then get id of the leftchild of the referralid
--increase the level count to count+1
set count=count+1

declare lcid int,

select leftleg into lcid from members where id=referralid

select leftleg into leftfree, rightleg into rightfree from members where id=lcid

if leftfree=0 than
insert into member (parentid, referralId, level) values (lcid, referralid, count)
update member set leftfree=newmemberid where id=lcid
else if rightree=0 then
insert into member (parentid, refferalId, level) values(lcid, referralid, count)
update member set rightfree=newmemberid where id=lcid
end if

This has to go down to say 10 levels and then if count=10 then count=0 and then start from rightleg of the referralid id.

As you can see I cannot hard code 30-40 select update statements into the procedure thinking that levels' will be only 10.
It may be 100 or 1000s.

Can loops be used. Do you think it'll be a good solution. Problem is **efficiently** searching without hard coding select statements taking into consideration performance issues. If procedures can be eliminated than it will be better because dbs like mysql don't support them at this time.

I haven't done or seen any work where more than one or two select/update statements are used.

Simple one or two sql statements are used normally like:

slect * from tbl where column=keyword
or update tbl where column=column

This case is different. We'll think about commission updating etc. later when solution is found to searching.

I just can't imagine how this group of sql statements would look like.

i dont know the database system your trying to use, but basically you will only need one table,

in that table you need to contain the following information,

memeber_id
parent_id
parent_leg (either left or right)
parent_depth(how many parents from the top to the current member)
child_depth(how many children, grandchildren etc the member has)
current_commision

baisically something like that

that should cover everything you want, i think

But main problem here is finding a free slot.
I mean how can I search down to say 10-15 levels and then if no free slot is found then start again at 0 level right leg and go down to level 10-15 again.

I mean what sql script looks like when doing this kind of thing..Example I have provided above doesn't really look good. With stored procedures or without it because many db like mysql don't support support sps and even then sites like these are hosted using mysql.

Also when adding member updation / addtion has to be done to multiple records.