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.
Continue reading “Binary tree in MySQL for MLM”