MySQL operating hierarchical data

Question

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',
  `name` varchar(255) NOT NULL DEFAULT '',
  `is_working` tinyint(1) unsigned NOT NULL DEFAULT '1',
);

which helds hierarchical data with relations of id and parent_id

I have 5 levels depth tree, like:

CATEGORY LEVEL 1
  SUBCAT LEVEL 2
    SUBCAT LEVEL 3
      SUBCAT LEVEL 4
        SUBCAT LEVEL 5

I need (the question): if I'm setting is_working = 0 for some category or subcategory, is_working is being set to 0 for all of it's subcategories.

You'd need to use a trigger to dig down into the tree to update all the child records, unless you wan to do it dynamically at retrieval time. — Marc B, Jul 05 '11 at 14:01
If you can change your table design, a closure table might be a good alternative. Have a look at [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree#192462), and also see [Bill Karwin's](http://stackoverflow.com/users/20860/bill-karwin) slides on [Models for hierarchical data](http://www.slideshare.net/billkarwin/models-for-hierarchical-data). — Mike, Jul 23 '11 at 20:08
I've just created a simple example of a closure table for [another question](http://stackoverflow.com/questions/6802539/hierarchical-tree-database-for-directories-in-filesystem/6802879#6802879). — Mike, Jul 23 '11 at 20:09

Tomas · Answer 1 · 2014-01-18T11:51:29.267

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:

CREATE TABLE `taxons` (
  `TaxonId` smallint(6) NOT NULL default '0',
  `ClassId` smallint(6) default NULL,
  `OrderId` smallint(6) default NULL,
  `FamilyId` smallint(6) default NULL,
  `GenusId` smallint(6) default NULL,
  `Name` varchar(150) NOT NULL default ''
);

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.

Do you have any literature to support such a solution? I personally like it (for a simple hierarchy that I'm building) and would like to have some arguments for and against it. — mapto, Jul 18 '18 at 13:48

fdaines · Answer 2 · 2011-07-05T15:25:24.327

1

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 //
CREATE TRIGGER categoriesUpdateTrg BEFORE UPDATE ON categories
    FOR EACH ROW BEGIN
    IF (NEW.is_working<>OLD.is_working) THEN
        UPDATE 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).

edited Jul 05 '11 at 15:25

answered Jul 05 '11 at 14:04

fdaines

1,216
10
12

look at the new example code. "For Each Row" statement execute the containing code for each updated row. – fdaines Jul 05 '11 at 15:26
I've tried your example, but got an error: `#1442 - Can't update table 'categories' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.` – bob Jul 05 '11 at 16:39
oh, I didn't try this code, but is comprehensive if this was restricted because can create an infinite loop, sorry I have not another idea. – fdaines Jul 05 '11 at 18:29

MySQL operating hierarchical data

2 Answers2

Linked