Mike Hillyer wrote a very good arti­cle on Man­ag­ing Hier­ar­chi­cal data in MySQL, defen­itely worth a read;

Most users at one time or another have dealt with hier­ar­chi­cal data in a SQL data­base and no doubt learned that the man­age­ment of hier­ar­chi­cal data is not what a rela­tional data­base is intended for. The tables of a rela­tional data­base are not hier­ar­chi­cal (like XML), but are sim­ply a flat list. Hier­ar­chi­cal data has a parent-child rela­tion­ship that is not nat­u­rally rep­re­sented in a rela­tional data­base table.

For our pur­poses, hier­ar­chi­cal data is a col­lec­tion of data where each item has a sin­gle par­ent and zero or more chil­dren (with the excep­tion of the root item, which has no par­ent). Hier­ar­chi­cal data can be found in a vari­ety of data­base appli­ca­tions, includ­ing forum and mail­ing list threads, busi­ness orga­ni­za­tion charts, con­tent man­age­ment cat­e­gories, and prod­uct cat­e­gories. For our pur­poses we will use the fol­low­ing prod­uct cat­e­gory hier­ar­chy from an fic­tional elec­tron­ics store:

via MySQL :: Man­ag­ing Hier­ar­chi­cal Data in MySQL.

More From HackIX

• Zend Frame­work 1 and Doc­trine 2 Integration
• About using UTF-8 fields in MySQL
• Tuto­r­ial: Get­ting Started with Zend_Auth

Ask HackIX To Rec­om­mend Your Posts

Tags: Hierarchical, MySQL

This entry was posted on 4 March, 2010 at 04:49. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Posted in Databases Development MySQL System Administration by Danny Froberg No Comments Yet