Managing hierarchical data in mysql l.jpg
1 / 34

Managing Hierarchical Data in MySQL.


53 views
Uploaded on:
Category: Animals / Pets
Description
Managing Hierarchical Data in MySQL. Mike Hillyer - MySQL AB PHP Quebec 2005. About Me. Mike Hillyer, BSc. Member of the MySQL AB Documentation Team MySQL Core and Pro Certified Top MySQL Expert at www.experts-exchange.com Resident MySQL Expert at SearchDatabase.com
Transcripts
Slide 1

Overseeing Hierarchical Data in MySQL Mike Hillyer - MySQL AB PHP Quebec 2005

Slide 2

About Me Mike Hillyer, BSc Member of the MySQL AB Documentation Team MySQL Core and Pro Certified Top MySQL Expert at www.experts-exchange.com Resident MySQL Expert at SearchDatabase.com http://www.openwin.org/mike/aboutme.php

Slide 3

About You what number of you… Currently utilize MySQL? Another RDBMS? Oversee various leveled information? Will oversee various leveled information? Know what various leveled information is?

Slide 4

About This Session http://www.openwin.org/mike/presentations/http://www.vbmysql.com/articles/overseeing progressive information in-mysql.html What is various leveled information? The Adjacency List Model The Nested Set Model Querying the Nested Set Model Adding New Nodes Deleting Nodes Additional Resources

Slide 5

What is Hierarchical Data? Information that keeps up a guardian youngster relationship Nodes have zero or one guardian, zero to numerous kids Only the root hub has no guardian Various sorts of information Mailing list/gathering strings Organizational outlines Content administration classifications Product classes

Slide 6

Product Categories

Slide 7

The Adjacency List Model + - + - - + - + | category_id | name | guardian | + - + - - + - + | 1 | ELECTRONICS | NULL | 2 | TELEVISIONS | 1 | 3 | TUBE | 2 | 4 | LCD | 2 | 5 | PLASMA | 2 | 6 | PORTABLE ELECTRONICS | 1 | 7 | MP3 PLAYERS | 6 | 8 | FLASH | 7 | 9 | CD PLAYERS | 6 | 10 | 2 WAY RADIOS | 6 | + - + - - + - +

Slide 8

Full Tree SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM classification AS t1 LEFT JOIN classification AS t2 ON t2.parent = t1.category_id LEFT JOIN class AS t3 ON t3.parent = t2.category_id LEFT JOIN class AS t4 ON t4.parent = t3.category_id WHERE t1.name = 'Hardware'; + - + - - + - + - + | lev1 | lev2 | lev3 | lev4 | + - + - - + - + - + | ELECTRONICS | TELEVISIONS | TUBE | NULL | ELECTRONICS | TELEVISIONS | LCD | NULL | ELECTRONICS | TELEVISIONS | PLASMA | NULL | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL | + - + - - + - + - + 6 columns in set (0.00 sec)

Slide 9

Leaf Nodes SELECT t1.name FROM class AS t1 LEFT JOIN classification as t2 ON t1.category_id = t2.parent WHERE t2.category_id IS NULL; + - + | name | + - + | TUBE | LCD | PLASMA | FLASH | CD PLAYERS | 2 WAY RADIOS | + - +

Slide 10

Single Path SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4 FROM class AS t1 LEFT JOIN classification AS t2 ON t2.parent = t1.category_id LEFT JOIN classification AS t3 ON t3.parent = t2.category_id LEFT JOIN class AS t4 ON t4.parent = t3.category_id WHERE t1.name = "Gadgets" AND t4.name = 'Blaze'; + - + - - + - + - + | lev1 | lev2 | lev3 | lev4 | + - + - - + - + - + | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | + - + - - + - + - + 1 line in set (0.01 sec)

Slide 11

Limitations of Adjacency Lists Adjacency list model not standardized. Erasing hubs can vagrant youngsters. Questioning ways and trees requires various self-joins. Procedural code required for most utilize cases.

Slide 12

Nested Sets

Slide 13

Nested Set Table + - + - - + - + - + | category_id | name | lft | rgt | + - + - - + - + - + | 1 | ELECTRONICS | 1 | 20 | 2 | TELEVISIONS | 2 | 9 | 3 | TUBE | 3 | 4 | 4 | LCD | 5 | 6 | 5 | PLASMA | 7 | 8 | 6 | PORTABLE ELECTRONICS | 10 | 19 | 7 | MP3 PLAYERS | 11 | 14 | 8 | FLASH | 12 | 13 | 9 | CD PLAYERS | 15 | 16 | 10 | 2 WAY RADIOS | 17 | 18 | + - + - - + - + - +

Slide 14

Numbered Sets

Slide 15

Numbered Tree

Slide 16

Full Tree SELECT node.name FROM nested_category AS hub, nested_category AS guardian WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = "Hardware" ORDER BY node.lft; + - - + | name | + - - + | ELECTRONICS | TELEVISIONS | TUBE | LCD | PLASMA | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | CD PLAYERS | 2 WAY RADIOS | + - - +

Slide 17

Leaf Nodes SELECT name FROM nested_category WHERE rgt = lft + 1; + - + | name | + - + | TUBE | LCD | PLASMA | FLASH | CD PLAYERS | 2 WAY RADIOS | + - +

Slide 18

Node Depth SELECT node.name, (COUNT(parent.name) - 1) AS profundity FROM nested_category AS hub, nested_category AS guardian WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft; + - - + - + | name | profundity | + - - + - + | ELECTRONICS | 0 | TELEVISIONS | 1 | TUBE | 2 | LCD | 2 | PLASMA | 2 | PORTABLE ELECTRONICS | 1 | MP3 PLAYERS | 2 | FLASH | 3 | CD PLAYERS | 2 | 2 WAY RADIOS | 2 | + - - + - +

Slide 19

Indented Nodes SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name FROM nested_category AS hub, nested_category AS guardian WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft; + - - + | name | + - - + | ELECTRONICS | TELEVISIONS | TUBE | LCD | PLASMA | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH | CD PLAYERS | 2 WAY RADIOS | + - - +

Slide 20

Depth of a Sub-Tree SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS profundity FROM nested_category AS hub, nested_category AS guardian, nested_category AS sub_parent, ( SELECT node.name, (COUNT(parent.name) - 1) AS profundity FROM nested_category AS hub, nested_category AS guardian WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name = 'Versatile ELECTRONICS' GROUP BY node.name ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.name = sub_tree.name GROUP BY node.name ORDER BY node.lft;

Slide 21

Depth of a Sub-Tree - II + - - + - + | name | profundity | + - - + - + | PORTABLE ELECTRONICS | 0 | MP3 PLAYERS | 1 | FLASH | 2 | CD PLAYERS | 1 | 2 WAY RADIOS | 1 | + - - + - +

Slide 22

Immediate Subordinates GROUP BY node.name HAVING profundity <= 1 ORDER BY node.lft; + - - + - + | name | profundity | + - - + - + | PORTABLE ELECTRONICS | 0 | MP3 PLAYERS | 1 | CD PLAYERS | 1 | 2 WAY RADIOS | 1 | + - - + - +

Slide 23

Aggregate Functions SELECT * FROM item; + - + - - + - + | product_id | name | category_id | + - + - - + - + | 1 | 20" TV | 3 | 2 | 36" TV | 3 | 3 | Super-LCD 42" | 4 | 4 | Ultra-Plasma 62" | 5 | 5 | Value Plasma 38" | 5 | 6 | Power-MP3 128mb | 7 | 7 | Super-Shuffle 1gb | 8 | 8 | Porta CD | 9 | 9 | CD To go! | 9 | 10 | Family Talk 360 | 10 | + - + - - + - +

Slide 24

Aggregate Functions - II SELECT parent.name, COUNT(product.name) FROM nested_category AS hub, nested_category AS guardian, item WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.category_id = product.category_id GROUP BY parent.name ORDER BY node.lft; + - - + - - + | name | COUNT(product.name) | + - - + - - + | ELECTRONICS | 10 | TELEVISIONS | 5 | TUBE | 2 | LCD | 1 | PLASMA | 2 | PORTABLE ELECTRONICS | 5 | MP3 PLAYERS | 2 | FLASH | 1 | CD PLAYERS