Complex Db design and Multiple Record Updation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Complex Db design and Multiple Record Updation

Author

Topic

ssruprai
Starting Member

5 Posts

Posted - 2002-04-24 : 21:14:42

Please Note that figured I have creted aren't displayd well by Preview I have tried many times.

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 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 whenever a new member is added.

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 because both legs are already occupied. But because of spill over when a new member7 is referenced (introduced) by 1 he can be added somewhere down in his 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 desiring db one more field has to be added to some table because we also want to keep track of each member's spill overs.(So, one more record/field 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.

If we are unable to find a free slot under 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 MemberC

Then 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 Member7 will be add under MemberB's left 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 considreation only member info, his total and weekly commission).

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

khalik
Constraint Violating Yak Guru

443 Posts

Posted - 2002-04-24 : 23:46:03

very big problem def.. can u make thing simpler to us
just show the small piture

and try this

(left leg) Member1 (right leg)
.............|..................
---------------------------------
...|.................... |
.Member2........... Member3
...|
---------------
|.............|
Member4 Member5
-----------
|........|
Member6

try giving some char do that it maintaint the format(. `) what ever

======================================
Ask to your self before u ask someone

ssruprai
Starting Member

5 Posts

Posted - 2002-04-26 : 18:31:32

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

ssruprai
Starting Member

5 Posts

Posted - 2002-04-26 : 19:34:23

Please ignore fifth line below third figure above which says
"MemberB's right (occupied by memberC)" because there is no memberC here so memberB's right leg is free. And because when searching right leg search is made right to left, so member8 will be added below MemberB's right leg.

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

ssruprai
Starting Member

5 Posts

Posted - 2002-04-30 : 20:39:35

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources