Having not known about your topic, I found other resources:
I needed a way to show a hierarchical structure and found a few informative sites:
* http://www.delphi3000.com/articles/article_2740.asp?SK
* http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/index.html
And what most were saying, was the, Modified Preorder Tree Traversal Algorithm, was a preferred way of doing hierarchical sets.
This is the Create for the tables (I am using both MySQL version 5 and Elevate DB version 2)
--
-- Definition of table `tblcategories`
--
DROP TABLE IF EXISTS `tblcategories`;
CREATE TABLE `tblcategories` (
`idtblcategories` int(10) unsigned NOT NULL auto_increment,
`Description` varchar(45) NOT NULL,
`lft` int(10) unsigned NOT NULL,
`rgt` int(10) unsigned NOT NULL,
PRIMARY KEY USING BTREE (`idtblcategories`),
UNIQUE KEY `ndxDescription` (`Description`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1;
-- some working records
/*!40000 ALTER TABLE `tblcategories` DISABLE KEYS */;
INSERT INTO `tblcategories` (`idtblcategories`,`Description`,`lft`,`rgt`) VALUES
(11,'Food',1,18),
(12,'Fruit',2,11),
(13,'Red',3,6),
(14,'Cherry',4,5),
(15,'Yellow',7,10),
(16,'Banana',8,9),
(17,'Meat',12,17),
(18,'Beef',13,14),
(19,'Pork',15,16);
/*!40000 ALTER TABLE `tblcategories` ENABLE KEYS */;
To add a new node, say at Yellow, first begin a transaction, then update the existing entries:
UPDATE tblcategories SET rgt = rgt + 2
WHERE rgt > 7;
UPDATE tblcategories SET lft = lft + 2
WHERE lft > 10;
Now, insert your row and commit:
INSERT INTO tblcategories VALUES ('New Node', 8, 9);
To remove a node, subtract instead of add 2 in the UPDDATE statement, then, delete your node.
Happy hierarchy.