mySQL: Designing a TreeView Structure

Databases
1

Hello Everyone…

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

Kindly Help.

Thanks

2

mandatory reading: http://www.sitepoint.com/hierarchical-data-database/

the adjacency model is the easiest to understand and manipulate, but to retrieve unlimited sublevels with a single query is impossible without recursion (note: see http://sqllessons.com/categories.html if your interface will be showing only a limited number of sublevels in a single display)

you’re probably going to want to use the nested set model

3

Hi

So for example if I want upto 6 sub-levels, will it still be hard to build a single query for it?

Thanks

4

6 levels? pièce de gateaux :slight_smile:

see the sqllessons article, it has a sample query

5

Hello

I have gone thru that article that you mentioned. I ahve managed to build a 6 level treeview.

So next step is how do I iterate through the list of records and build a tree view using PHP.

Perhaps this threads needs to move to the PHP section now or can someone help me with the concept here itself?

Thanks for your help so far.

6

well, i can’t help you with the php, but it should be really trivial once you’ve got your query working

can i see your query?