Recently for a student, I was asked to explain the design considerations of a Binary Tree which was to be used in an MLM solution. About 10 years back it was a nightmare, and in my career, I was lucky to get that privilage for more than a dozen times with varying schemes and structures. But now with MySQL having procedures, and functions, the tree design and related functions were a breze. Reproducing it here for future reference. This is in no way a complete solution, but just bits and pieces which may even be discarded as crap.
CREATE TABLE `binTree` ( `nodeid` int(10) unsigned NOT NULL auto_increment, `lnode` int(10) unsigned NOT NULL default '0', `rnode` int(10) unsigned NOT NULL default '0', `pnode` int(10) unsigned NOT NULL default '0', `pside` enum('l','r') NOT NULL default 'l', `tLevel` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`nodeid`), KEY `parent` (`pnode`), KEY `treelevel` (`tLevel`), KEY `lside` (`lnode`), KEY `rside` (`rnode`) ) ENGINE=MyISAM;
The above is the basic structure of the tree table, and some complementary functions and procedures are accompanied to make the usage simple, otherwise would be a herculian task for the developer to do the same.