Binary Tree Using PHP + MySQL

Question

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:

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

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

3. another constraint is each day any node can not have more than 100 points.

4. I also need to construct (display in a webpage) the tree. Only 4-5 levels are to be shown.

5. The database is likely to have 100000 nodes

I have found mainly 4 models for implemmenting hieararchical data in MySQL,PHP

1. Adjacency list
2. Path enumeration
3. Nested sets
4. 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!

Answer 1

This blog might help you with managing hierarchy data

The one that sounds most familiar to your question is possibly Modified Preorder Tree Traversal