I am implementing an MLM tree for a website using PHP (CodeIgniter) and MySQL. I need a binary tree implementation in the database. The followings things are to be considered:
For each node , Minimum of the number of children/nodes in left subtree and the number of children/nodes in right subtree is called a pair. For each pair one nodes gets 1 point - which should be stored in database (nodes represent users)
When a new node is created (wherever), it is possible that many of the nodes' pair is incremented. So whenever a node is created, every node's point should be updated(incremented by one when applicable)
another constraint is each day any node can not have more than 100 points.
I also need to construct (display in a webpage) the tree. Only 4-5 levels are to be shown.
The database is likely to have 100000 nodes
I have found mainly 4 models for implemmenting hieararchical data in MySQL,PHP
- Adjacency list
- Path enumeration
- Nested sets
- Closure table
So I would like to find a solution that will reduce the insertion overhead and successfully update the points for all nodes applicable.
I have tried the adjacency List solution.
node ( id, parentid, leftChildId,rightChildId,leftCount,rightCount )
userStat(id,sdate,pairs,mlmIncome)
each time one node is inserted,I go upward and keep incrementing the child counts .If new pair is made then I increment that also and increment the point.. I am doing these with stored procedures.
The reason why i chose this solution over Nested Set is : for each node inserted , the number of nodes to be updated for Nested Set Is always more than adjacency list.
Though the rate of constructing tree is more than insertion . And nested set is better in constructing trees..
Am i in the right direction?? Please help !
Thnx in Advance!
