Mysql – SQL relationship for nested groups

hierarchyMySQL

I'm working on a creating a database schema for a legacy app that currently saves its data to XML files.

I'm having trouble representing this relationship:

Task Group 1 contains
|-- Simple Task 1
|-- Simple task 2
|-- Task Group 1a contains
    |-- Simple Task 3
    |-- Simple Task 4
    |-- Task Group 1b contains
        |--Simple task 5

Basically, a task group may contain one or more tasks. These may be simple tasks or other task groups. So the nested groups is what I'm having trouble with.

This is what I have so far:

CREATE TABLE `task_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `processing_type` varchar(45) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_parent_idx` (`parent_id`),
  CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `task_group` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Best Answer

It may help to separate the hierarchy from the task-like objects, and implement them as inherited types. There would be four tables.

TaskTrees
  ParentTaskId   references Tasks.TaskId
  ChildTaskId    references Tasks.TaskId

Tasks
  TaskId
  TaskType      simple or group

GroupTasks
  TaskId   references Tasks.TaskId
  <group specific columns>

SimpleTasks
  TaskId   references Tasks.TaskId
  <other specific columns>

This way the Trees become simple hierarchies of "things" and don't much care what those things are. There are other ways to implement trees than this.

The domain-specific information is held in GroupTasks and SimpleTasks. This is where the values required to implement each is held. There are other ways to implement inheritance. I'm not sufficiently versed in MySQL to recommend one over another.

Integrity between the tables must be maintained through the application. Ensuring a simple task does not have children, for example, is not guaranteed through the data model alone.

Related Question