1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

mySQL: Designing a TreeView Structure.

Discussion in 'MySQL' started by theextelligence, Feb 16, 2012.

  1. #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
    
    
    Code (markup):



    Kindly Help.

    Thanks
     
    theextelligence, Feb 16, 2012 IP
  2. EricBruggema

    EricBruggema Well-Known Member

    Messages:
    1,672
    Likes Received:
    22
    Best Answers:
    12
    Trophy Points:
    115
    #2
    EricBruggema, Feb 18, 2012 IP
  3. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,883
    Likes Received:
    34
    Best Answers:
    51
    Trophy Points:
    100
    #3
    Turn your problem upside down. Each child node has a pointer to its parent. You don't keep a record (in the record) of the children, just of the one parent.
     
    Rukbat, Feb 25, 2012 IP