title: Binary Tree Using PHP + MySQL
body: <p>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: </p>
  
  <ol>
  <li><p>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)</p></li>
  <li><p>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)</p></li>
  <li><p>another constraint is each day any node can not have more than 100 points.</p></li>
  <li><p>I also need to construct (display in a webpage) the tree. Only 4-5 levels are to be shown.</p></li>
  <li><p>The database is likely to have 100000 nodes</p></li>
  </ol>
  
  <p>I have found mainly 4 models for implemmenting hieararchical data in MySQL,PHP</p>
  
  <ol>
  <li>Adjacency list </li>
  <li>Path enumeration </li>
  <li>Nested sets </li>
  <li>Closure table</li>
  </ol>
  
  <p>So I would like to find a solution that will reduce the insertion overhead and successfully update the points for all nodes applicable.</p>
  
  <p>I have tried the adjacency List solution. </p>
  
  <pre><code>node ( id, parentid, leftChildId,rightChildId,leftCount,rightCount ) 
  userStat(id,sdate,pairs,mlmIncome)
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Though the rate of constructing tree is more than insertion . And nested set is better in constructing trees..  </p>
  
  <p>Am i in the right direction?? Please help !</p>
  
  <p>Thnx in Advance!</p>