Currently I need to build a structure for a MLM company. The structure they wanted is something like a ternary tree structure, which have one parent node links to 3 child node.
But for the top most node (root), it can have more than 3 child nodes while all other node will stick to the limit of three. Currently my idea for the structure was storing one ancestor parent node (the parent of the parent node) and also 3 child node as a row of record in DB. The method of keeping track is to store the depth and also node number of the user currently in. The node number will start from 1, left to right on new depth.
But the problem arise when i tried to insert, as I have no optimize solution to identify the root user from other user when inserting to available node (unused node number) as I only keeping track the nodes of depth one, I will need to loop the query to select the next depth level to get the list of empty node (and the loop goes on if no available node found). Currently every info is stored in a single table, 1 to 1 relation. I'd like to know if there is any other effective way of doing this ?
Currently coding is done in php and MySql.
