Binary Tree Using PHP + MySQL

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 ) 

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!


This blog might help you with managing hierarchy data

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

Need Your Help

Dynamic generation of select clear other elements in javascript

javascript jquery dynamically-generated

I have a for loop that creates as many as I write in an input field. first time I write a number in the imput all is ok... he generates for example 3 fields. When I delete 3 and write 5, he add two