I am looking for designing a database structure for a treeview plugin.
The nodes should be populated from the database. The root node can have unlimited level of child nodes and each child node can further have unlimited level of sub-child nodes.
Can someone please suggest me the database structure and possibly the query to fetch each nodes/child nodes.
This is a structure I have thought of so far but I am not sure if this is the proper way i should be desining the table.
CREATE TABLE `masters` (
`master_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`master_title` VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (`master_id`)
) ENGINE=MYISAM AUTO_INCREMENT=208 DEFAULT CHARSET=latin1
CREATE TABLE `mapping` (
`mapping_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`master_id_1` INT(10) DEFAULT NULL,
`master_id_2` INT(10) DEFAULT NULL,
`master_level_number` INT(10) DEFAULT NULL,
`master_level_sequence` INT(10) DEFAULT NULL,
PRIMARY KEY (`mapping_id`),
KEY `id_index` (`master_id_1`)
) ENGINE=MYISAM AUTO_INCREMENT=151 DEFAULT CHARSET=latin1