MySQL, управляющий иерархическими данными

Question

У меня есть структура таблицы MySQL:

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',
);

который содержит иерархические данные с отношениями id а также parent_id

У меня есть 5 уровней глубины дерева, как:

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

Мне нужно (вопрос): если я устанавливаю is_working = 0 для некоторой категории или подкатегории, is_working устанавливается на 0 для всех его подкатегорий.

Answer 1

То, что я использую, - это другой дизайн, и хотя у него есть ограничения, если вы можете их вынести, это очень просто и очень эффективно.

Вот пример таксономического дерева птиц, поэтому иерархия имеет класс / порядок / семейство / род / вид - вид - самый низкий уровень, 1 ряд = 1 вид:

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 ''
);

и пример данных:

+---------+---------+---------+----------+---------+-------------------------------+
| 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                   |

Это замечательно, потому что таким образом вы выполняете все необходимые операции очень простым способом, пока категории не изменяют свой уровень в дереве.

Answer 2

Вы можете использовать триггеры для выполнения рекурсивного обновления.

посмотрите на это: http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Это просто идея, я не пробую этот код.

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 ;

триггер выполняется при каждом обновлении над таблицей "категории". Для каждой обновленной строки задается вопрос, был ли изменен столбец is_working. Если условие истинно, обновите все дочерние категории (рекурсивные).