Sfrgttk

Is the percentage symbol a constant?

What does "don't have a baby" imply or mean in this sentence?

Is it possible to detect 100% of SQLi with a simple regex?

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

Crack the bank account's password!

Sets which are both Sum-free and Product-free.

What really causes series inductance of capacitors?

Algebraic proof that two statements of the fundamental theorem of algebra are equivalent

Integer but not Laurent sequences

What is an efficient way to digitize a family photo collection?

Are all power cords made equal?

Short story about a man betting a group he could tell a story, and one of them would disappear and the others would not notice

What is the reward?

How many copper coins fit inside a cubic foot?

How do I purchase a drop bar bike that will be converted to flat bar?

Do the speed limit reductions due to pollution also apply to electric cars in France?

Have any astronauts or cosmonauts died in space?

Taking an academic pseudonym?

Is there a way to pause a running process on Linux systems and resume later?

Disk space full during insert, what happens?

How bad is a Computer Science course that doesn't teach Design Patterns?

In a post apocalypse world, with no power and few survivors, would Satnav still work?

Is Developer Console going to be deprecated?

Did ancient Germans take pride in leaving the land untouched?

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

0

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

share|improve this question

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♦ 3 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 | 

0

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

share|improve this question

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♦ 3 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

share|improve this question

edited Mar 7 '17 at 20:29

McNets

15.9k42061

asked Mar 7 '17 at 20:09

A_BA_B

1034

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

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

share|improve this question

edited Mar 7 '17 at 20:29

McNets

15.9k42061

asked Mar 7 '17 at 20:09

A_BA_B

1034

share|improve this question

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

asked Mar 7 '17 at 20:09

A_BA_B

1034

asked Mar 7 '17 at 20:09

A_BA_B

1034

1 Answer
1

active

oldest

votes

0

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.

share|improve this answer

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


}
});

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

0

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.

share|improve this answer

edited Apr 13 '17 at 12:42

Community♦

1

answered Mar 8 '17 at 11:04

Michael GreenMichael Green

14.7k83060

add a comment | 

0

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.

share|improve this answer

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.

share|improve this answer

edited Apr 13 '17 at 12:42

Community♦

1

answered Mar 8 '17 at 11:04

Michael GreenMichael Green

14.7k83060

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>

share|improve this answer

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

answered Mar 8 '17 at 11:04

Michael GreenMichael Green

14.7k83060

answered Mar 8 '17 at 11:04

Michael GreenMichael Green

14.7k83060