Mysql Operating Hierarchical Data

I have MySQL table structure: CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned NOT NULL DEFAULT '0', `nam

Solution 1:

What I use is a different design, and though it has limitations, if you can bear them, it's very simple and very efficient.

Here is an example of taxonomic tree of birds so the hierarchy is Class/Order/Family/Genus/Species - species is the lowest level, 1 row = 1 species:

CREATETABLE `taxons` (
  `TaxonId` smallint(6) NOTNULLdefault'0',
  `ClassId` smallint(6) defaultNULL,
  `OrderId` smallint(6) defaultNULL,
  `FamilyId` smallint(6) defaultNULL,
  `GenusId` smallint(6) defaultNULL,
  `Name` varchar(150) NOTNULLdefault''
);

and the example of the data:

+---------+---------+---------+----------+---------+-------------------------------+
| TaxonId | ClassId | OrderId | FamilyId | GenusId | Name                          |
+---------+---------+---------+----------+---------+-------------------------------+
|     254 |       0 |       0 |        0 |       0 | Aves                          |
|     255 |     254 |       0 |        0 |       0 | Gaviiformes                   |
|     256 |     254 |     255 |        0 |       0 | Gaviidae                      |
|     257 |     254 |     255 |      256 |       0 | Gavia                         |
|     258 |     254 |     255 |      256 |     257 | Gavia stellata                |
|     259 |     254 |     255 |      256 |     257 | Gavia arctica                 |
|     260 |     254 |     255 |      256 |     257 | Gavia immer                   |
|     261 |     254 |     255 |      256 |     257 | Gavia adamsii                 |
|     262 |     254 |       0 |        0 |       0 | Podicipediformes              |
|     263 |     254 |     262 |        0 |       0 | Podicipedidae                 |
|     264 |     254 |     262 |      263 |       0 | Tachybaptus                   |

This is great because this way you accomplish all the needed operations in a very easy way, as long as the categories don't change their level in the tree.

Solution 2:

You can use triggers to execute the recursive update.

look at this: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

This is just an idea, i don't try this code.

delimiter //CREATETRIGGER categoriesUpdateTrg BEFORE UPDATEON categories
    FOREACHROWBEGIN
    IF (NEW.is_working<>OLD.is_working) THENUPDATE categories SET is_working=NEW.id_working WHERE parent_id=NEW.id;
        END IF;
    END;
//
delimiter ;

the trigger is executed on every update over the table "categories". For each updated row is asking if the is_working column was changed. If the condition is true, then update all the child categories (the recursive).