I am implementing the MLM tree for a website using PHP (CodeIgniter) and MySQL. I need a binary tree implementation in a database. The following things to consider:
For each node, the minimum number of children / nodes in the left subtree and the number of child nodes / nodes in the right subtree is called a pair. For each pair, one node receives 1 point - which should be stored in the database (nodes represent users)
When a new node is created (everywhere), it is possible that many pairs of nodes increase. Therefore, whenever a node is created, each node point must be updated (if necessary, incremented by 1)
another limitation is that every node can have no more than 100 points every day.
I also need to build (display on a web page) a tree. Only 4-5 levels should be shown.
There will likely be 100,000 nodes in the database
I found basically 4 models for embedding heriarchical data in MySQL, PHP
- Contact List
- Enumeration path
- Nested Kits
- Closing table
So, I would like to find a solution that will reduce overhead and successfully update points for all nodes used.
I tried adjacency list solution.
node ( id, parentid, leftChildId,rightChildId,leftCount,rightCount ) userStat(id,sdate,pairs,mlmIncome)
every time a single node is inserted, I go up and keep increasing the number of children. If a new pair is completed, then I increase this value and increase the point. I do this with stored procedures.
The reason I chose this solution over the Nested Set: for each inserted node, the number of nodes to be updated for the nested set is always greater than the adjacency list.
Although the speed of building a tree is greater than the insert. And the nested set is better for building trees.
Am I in the right direction? Please, help!
Thnx in Advance!
Shahed khan
source share