Can be used in Multilevel Marketing with binary tree (can be modified accordingly to be used for n Tree)

Introduction

We are now and then faced with the problem with storing data corresponding to Binary Tree and querying it. We generally rely on Recursion (hence costly) to query tree structure. Here is a simple data structure change in order query tree structure using simple SQL statement - no Recursion required.

Background

There are situations when we want to store Nodes of binary tree in database with reference to its parent together with position (in this case, Left Node or Right Node of Parent). The problem arises when we want to query database to get upline node or downline Node w.r.t. a certain node, or we want to get the count of nodes present at each level in the b-tree.

You can think of an implementation of this code sample in creating data structure to keep members with their hierarchy for Multi-Level Marketing company.

Using the Code

Note: The field naming convention used below is a little awkward but it goes like - first 5 chars in field name correspond to table name, next 2 chars define data type, next 2 chars define Constraint, renaming letters are the name of field.

Could u please help me on this that How to calculate the total number of pair under particular parent node according to pattern 1:2 or 2:1,1:1 ratio day to day maximum up to 5 pair capping daily and daily commission for every pair.

by using below stored procedure I can count the total number of pairs under particular node in 2:1,1:1 ratio means first pair is completed when two node added to the left side of given parent node and one node added right side of given parent node after that all pairs are completed when one node added left side and one node added right side of parent node (1:1 ratio)

example if i execute my stored procedure as follows it would return following.

EXEC count_pairs 'Ramesh123'

3

so there is 3 pairs as shown in my figure.

when we execute my stored procedure for ParentID 'sonu' it would return following.

EXEC count_pairs 'sonu'

2

so there is 2 pairs as shown in my figure.

My problem is to find the query which can return the total number of pair under particular node any given node node. day by day maximum 5 pairs in a day please any one can suggest us

Very good article. I am trying to figure out how to insert a node in the tree. Can you provide me with a query so that either added node is on the right or left side, but the tree should remain balanced.

I have wasted a ton of time in Binary trees and in my view they are not to be used in serious/heavy tasks, so you are welcome to see a skeleton of one heavy-duty approach: hashing as first level and millions of b-trees as second in order to blur-the-objects around.

There B-tree of order 3 is implemented in plain C, without delete operation. Talking of speed performance: it screams.

Dear Anup Kumar Yadav, can we use the same data structure for 3 nodes under one parent? i means L R and C (center node). will it be suitable to use by modifying little things in queries ? can you guide me which queries will need to modify to achieve that target ?

Select * From tbl_BinaryTree_Hierarchy where (Hiera_IN_FK_ParentCode=1 or
(Hiera_IN_FK_ParentCode is Null And Hiera_IN_FK_ChildCode=1))

Or

Select * From tbl_GraphPosition_GrPos
Left outer join tbl_BinaryTree_Hierarchy on GrPos_IN_xx_Position = Hiera_IN_xx_TreePosition
and (Hiera_IN_FK_ParentCode=1 or (Hiera_IN_FK_ParentCode is Null And Hiera_IN_FK_ChildCode=1))

Hello sir, i m totally new in this MLM project.
well i have done the placement of the nodes correctly.

well my requirement is the user can registered as a 1 head, 3 heads, 7 heads and 11 headers

and can place where he wants.
and the payouts like if header 1 or 3 then (total new pairs * 500) and for
7 or 11 ( (total new pairs * 1000) and update their field.
*how i can get the total new pairs also...??

*i have to do daily calculation from the root at 11:55 pm
I would like to know how can i do daily binary calculation ?
or any suggestion that how can i get to know about how many nodes has been added from each

node and how much commission has to be paid them after calculate the pairs.

More is it is good approach to use auto increment column name-> Nodes_IN_PK_Code in

What do you mean by 1/3/7/11 Head ? You need to add a Timestamp or DateTime Column to keep the joining date and query the data base with Date range in Where Clause to get joining for the specified period.

With CTE as (
Select B.Nodes_IN_PK_Code as PK, B.Nodes_VC_xx_NodeData as NodeData,

Hiera_IN_xx_NodeLevel as NodeLevel, Hiera_CH_xx_Position as Position, '' as IsBalanced,
Power(2,Hiera_IN_xx_NodeLevel)-2 as TotalNodeForBalanceTree,
(Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where

Hiera_CH_xx_Position = 'L' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as

TotalLeftChild,
(Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where

Hiera_CH_xx_Position = 'R' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as

TotalRightChild
From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ParentCode = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode is NOT NULL and H.Hiera_IN_FK_ChildCode = ?PKofNewnode?

'Do Not Pay' End )
When Position ='R' then (Case When TotalLeftChild>=TotalRightChild Then 'PAY' Else

'Do Not Pay' End )
End As ShouldIPay
From CTE
----------------------------------------------------------------------------------------------

and about 1, 3, 7, 11 its a node type e.g if i joined 3 then i can get three node e.g
1(R) (L)2 (R))3, if i joined 7 then 1(R) (L)2 (R)3 (L)4(under 2) (R)5(under 3) and so on...

i have to calculate it binary calculation according to type
like if some joined 1 or 3 type then he will get the money per node is 500 and if 7 or 11 then 1000..hope you understand what i want to achieve and let me know ur suggestion too.
with regards
vik

Way 1 - by adding 4 column in tbl_BinaryTree_Nodes
Fields - PaidLeft , PaidRight, UnPaidLeft, UnpaidRight
Step A : Update Column UnPaidLeft & UnpaidRight -- Use trigger to increment the values when any new entry is made under this node upto level which is payable (if unlimited depth then always)
Step B : Now when you calculate payout then update column PaidLeft , PaidRight by decrementing Column UnPaidLeft & UnpaidRight by the numbers you are going to pay.

Way 2 - By adding Paid Column in tbl_BinaryTree_Hierarchy indicating that payout against this node has been paid or not.

Way 3 - Use data from the payout table to calculate & distribute payout.

In both way you need to add DateTime Column either in tbl_BinaryTree_Nodes or in tbl_BinaryTree_Hierarchy to query acc. to joining date.

how can i update the left and right column of the table using below CTE Query ..?

------------------------------------------------------------------------------------------
With CTE as (
Select B.Nodes_IN_PK_Code as PK, B.Nodes_VC_xx_NodeData as NodeData, Hiera_IN_xx_NodeLevel as NodeLevel, Hiera_CH_xx_Position as Position, '' as IsBalanced,
Power(2,Hiera_IN_xx_NodeLevel)-2 as TotalNodeForBalanceTree,
(Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where Hiera_CH_xx_Position = 'L' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as TotalLeftChild,
(Select Count(Hiera_IN_PK_Code) From tbl_BinaryTree_Hierarchy Where Hiera_CH_xx_Position = 'R' and Hiera_IN_FK_ParentCode=B.Nodes_IN_PK_Code) as TotalRightChild
From tbl_BinaryTree_Hierarchy H
Left Outer Join tbl_BinaryTree_Nodes B on H.Hiera_IN_FK_ParentCode = B.Nodes_IN_PK_Code
Where H.Hiera_IN_FK_ParentCode is NOT NULL and H.Hiera_IN_FK_ChildCode = ?PKofNewnode? -- (Primary key of Newly Added Node)
)
Select CTE.PK, CTE.NodeData, CTE.NodeLevel, CTE.Position,
Case When ((CTE.TotalNodeForBalanceTree / 2 = TotalLeftChild) and (CTE.TotalNodeForBalanceTree / 2 = TotalRightChild)) then 'Yes' Else' No' End As IsBalanced,
CTE.TotalNodeForBalanceTree , CTE.TotalLeftChild , CTE.TotalRightChild,
Case When Position ='L' then (Case When TotalLeftChild<=TotalRightChild Then 'PAY' Else 'Do Not Pay' End )
When Position ='R' then (Case When TotalLeftChild>=TotalRightChild Then 'PAY' Else 'Do Not Pay' End )
End As ShouldIPay
From CTE

Actually I am developing an MLM website in Asp.net(3.5) and C#. I have used your data structure for binary tree, I am able to show the hierarchy in Grid View using your querry but my client want that I should show it in Graphical repersentation form.

Could u please help me on this that how could I perform this, I am unable to do it on my own. Any help would highly appreciated.

If You are using Asp.net then first decide the level of genealogy that you want to show, you can use Label/Link Label control to display names, Image to display image of member either depending on package , For this what you have to do is write a query to get the immediate child in left/right side & display it in label.

See, Displaying the tree need some SQL query & Some sort of Code in Webpage And its up to you how yo do it. For Your Reference you can check this image at http://www.anupkumaryadav.com/tree.gif[^] to get a flavor of the tree.

Alternatively , We can Add One More column in tbl_BinaryTree_Hierarchy , to keep Node placement in 1-Dimensional Array of the Newly Inserted nodde w.r.t Parent Node. Which can be updated using the same trigger, at the time of node insertion thus saving us from using Computed Table to calcualte location during runtime thus resulting in lighter overhead to Server in the Query.

It shows error Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int. and when I run without Order By Clause It shows all the rows not limiting to 15 rows. Kindly resolve this issue I would be higly thankful to you.

I think this approach is needlessly complex and hard to use. Triggers and recursive CTEs are procedural code.

Look up a binary heap in an book on data structure. The idea is that if a parent node has an array position of (n), the the left and right children are at (2n) and (2n+1). I have a short discussion, with artwork at

Your Idea is very basic that in binary Tree if parent node has an array position of (n), the the left and right children are at (2n) and (2n+1) - similarly if the tree is tertiary then same parent node at n will have children at position (3n-1) , (3n) , (3n+1) - on this very idea if a tree in suppose m-ary (that is having m child) then a parent at position (n) will have Left most node at (m*n)-(m-2) , and right most node is (m*n) + 1 and the other nodes are in between them.

Infact, there are multiple ways to tackle the same problem - (each solution has some benefit over the other) - I had just shown one of the way to deal with the problem.

Moreover This procedure is not too complex at most - there are 2 Table & 1 Trigger for all, All the basic Query is being done using Simple Select statement , The Query which has been written using CTE can also be written using Select Statement,
for example consider the query A .How To find the Balanced node due to new node entry (with reference to Tree -2 )

According to Fig.2 how can insert into right side ?
how can we insert into left most(AL3(L) from the root at left side...?

hope u can understand what i need..?

*
Actually i would like to know how can add the node at the left end most and at the right end most?
if there is no node in the right side from the Root Node then how can we detect that there is no node in the right side from the ROOT NODE ? if there is node then how to detect the right end most and add the node after it.?

Hi, with reference to your query & Tree above, what I conclude is that when *DR* is added then BR & AR gets paid - but how much is not clear - According to general MLM trend I can figure out that BR will get benefit of 1 Pair, and AR will get benefit of 1 pair.

Let us assume that you want to give benefit of 1 unit pair to BR and also to AR the benefit of 1 unit pair ( as you might had been already given the benefit of 1 unit pair when DL was added.)

So you need to find out the list of parent Nodes w.r.t the newly added node who will get benefit.

Let us assume the Primary Key of the newly added node (*DR*) is 123456.
Now to get the list of nodes who needs to be paid is as follows..

The SQL Query below will give you the list of all the parents with last column ShouldIPay (Pay or DoNotPay) indicating which parent node needs to be paid.
(for the sake of keeping the sql similar to the previous one I had just added the column "Hiera_CH_xx_Position" in the CTE & added a calculated column in the result set "ShouldIPay")