SQL relationship for nested groupsUnexplained InnoDB timeoutsAre two indexes needed?Finding rows for a...
EM Vs PM Speaker
How do I purchase a drop bar bike that will be converted to flat bar?
Sed-Grep-Awk operations
How to deal with an underperforming subordinate?
bash aliases do not expand even with shopt expand_aliases
Was the Spartan by Mimic Systems a real product?
How to know you are over-explaining and oversimplifying a subject?
Including proofs of known theorems in master's thesis
Is there a configuration of the 8-puzzle where locking a tile makes it harder?
How do I fight with Heavy Armor as a Wizard with Tenser's Transformation?
Does しかたない imply disappointment?
Is the percentage symbol a constant?
Integer but not Laurent sequences
Multiple null checks in Java 8
Do the speed limit reductions due to pollution also apply to electric cars in France?
What does @ mean in a hostname in DNS configuration?
Algebraic proof that two statements of the fundamental theorem of algebra are equivalent
What does "don't have a baby" imply or mean in this sentence?
How can changes in personality/values of a person who turned into a vampire be explained?
Partial derivative with respect to three variables
Will the duration of traveling to Ceres using the same tech developed for going to Mars be proportional to the distance to go to Mars or not?
How bad is a Computer Science course that doesn't teach Design Patterns?
Cartoon in which kids compete by fighting as monsters or creatures with special powers in a virtual world
How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?
SQL relationship for nested groups
Unexplained InnoDB timeoutsAre two indexes needed?Finding rows for a specified date rangeSimple query is slow on 4M-rows tableOptimizing a simple query on a large tableNeed help improving sql query performanceHow to improve query count execution with mySql replicate?MySQL query taking too longError in creating table in MySQL v5.5.45 - Invalild default value for columnNo 'Copying to tmp table' state in profiling a mysql query
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♦ 2 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♦ 2 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♦ 2 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♦ 2 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