SQL relationship for nested groups
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
mysql hierarchy
edited Mar 7 '17 at 20:29
McNets
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
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
mysql hierarchy
edited Mar 7 '17 at 20:29
McNets
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28
add a comment |
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
mysql hierarchy
edited Mar 7 '17 at 20:29
McNets
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
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
mysql hierarchy
mysql hierarchy
edited Mar 7 '17 at 20:29
McNets
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
edited Mar 7 '17 at 20:29
McNets
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
edited Mar 7 '17 at 20:29
McNets
15.9k42061
edited Mar 7 '17 at 20:29
McNets
15.9k42061
edited Mar 7 '17 at 20:29
McNets
15.9k42061
15.9k42061
asked Mar 7 '17 at 20:09
A_BA_B
1034
asked Mar 7 '17 at 20:09
A_BA_B
1034
asked Mar 7 '17 at 20:09
A_BA_B
1034
1034
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28
add a comment |
1
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28
1
1
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28
add a comment |
1 Answer
1
active
oldest
votes
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 SimplTasks. 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.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f166483%2fsql-relationship-for-nested-groups%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 SimplTasks. 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.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
add a comment |
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 SimplTasks. 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.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
add a comment |
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 SimplTasks. 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.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
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 SimplTasks. 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.
edited Apr 13 '17 at 12:42
Community♦
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
answered Mar 8 '17 at 11:04
Michael GreenMichael Green
14.7k83060
14.7k83060
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f166483%2fsql-relationship-for-nested-groups%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
And what is exactly the problem? Do you think this do not cover what you need? It is not working? It is a traditional hierarchical table.
– McNets
Mar 7 '17 at 20:28