Many to Many Relationship Design Query
-
Hi,
I am looking to design tables which contain tag(s) and their relationship with tasks (parent/child).
Requirements:
Any Parent task can be assigned to a Tag.
If a parent task is assigned, assigning any child tasks related to that parent is optional.However, if any child task is assigned to a tag, then that child's parent automatically gets assigned to the tag.
So a tag will always have a parent assigned to it, and optionally any child of that parent assigned to it.Question:
Is this design suitable for these requirements?
-- parent task table
CREATE TABLE [Model].[ParentTask]
(
[ParentTaskID] [int] NOT NULL IDENTITY(1,1),
[ParentTaskName] [varchar](50) NOT NULL,
[Note] [xml] NULL,
[Action] [varchar] (80) NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_ParentTask_IsActive] DEFAULT 1,
CONSTRAINT [PK_ParentTask_ParentTaskID] PRIMARY KEY CLUSTERED ([ParentTaskID] ASC)
);
-- child task table
CREATE TABLE [Model].[ChildTask]
(
[ChildTaskID] [int] NOT NULL IDENTITY(1,1),
[ChildTaskName] [varchar](50) NOT NULL,
[Note] [xml] NULL,
[Action] [varchar] (80) NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_ChildTask_IsActive] DEFAULT 1,
[ParentTaskID] [int] NOT NULL
CONSTRAINT [PK_ChildTask_ChildTaskID] PRIMARY KEY CLUSTERED([ChildTaskID] ASC),
CONSTRAINT [FK_ChildTask_ParentTaskID] FOREIGN KEY(ParentTaskID) REFERENCES [Model].[ParentTask]([ParentTaskID])
);-- tag table
CREATE TABLE [Model].[Tag]
(
[TagID] [int] NOT NULL IDENTITY(1,1),
[TagName] [nvarchar](150) NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Tag_IsActive] DEFAULT(1),
CONSTRAINT [PK_Tag_TagID] PRIMARY KEY CLUSTERED([TagID] ASC)
);-- parent tasks related to a tag
CREATE TABLE [Model].[TagParentDetail]
(
[TagID] [int] NOT NULL,
[ParentTaskID] [int] NOT NULL,
CONSTRAINT [PK_TagParentDetail_TagDetailID] PRIMARY KEY CLUSTERED([TagID], [ParentTaskID]),
CONSTRAINT [FK_TagParentDetail_TagID] FOREIGN KEY(TagID) REFERENCES [Model].[Tag]([TagID]),
CONSTRAINT [FK_TagParentDetail_ParentTaskID] FOREIGN KEY(ParentTaskID) REFERENCES [Model].[ParentTask]([ParentTaskID])
);-- child tasks related to a tag
CREATE TABLE [Model].[TagChildDetail]
(
[TagID] [int] NOT NULL,
[ChildTaskID] [int] NOT NULL,
CONSTRAINT [PK_TagChildDetail_TagDetailID] PRIMARY KEY CLUSTERED([TagID], [ChildTaskID]),
CONSTRAINT [FK_TagChildDetail_TagID] FOREIGN KEY(TagID) REFERENCES [Model].[Tag]([TagID]),
CONSTRAINT [FK_TagChildDetail_ChildTaskID] FOREIGN KEY(ChildTaskID) REFERENCES [Model].[ChildTask]([ChildTaskID])
);
Thanks! -
Is there actually a many to many relationship here? YOu state "Basically a single 'Tag' can relate to many ParentTask items and many ChildTask items.", which means, unless I am mistaken, your relationship are as follows:
ParentTask has Many ChildTasks - A ChildTask has a single ParentTask
Tag has Many ParentTasks - A ParentTask has a Single Tag
Tag has Many Child Tasks - A ChildTask has a Single TagIs this correct? If so, there is no Many to Many relationship; thus the TagDetail Table, which is effectively a composite key table, isn't required.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, February 20, 2017 9:17 AM
The problem is that not all Child Tasks of a Parent Task will be assigned to a Tag. So a 'Tag Detail' can have for example Parent A (which has 4 child task in total), but this Tag may only require 2 of the 4 Child Tasks.
Also a tag can contain multiple Parent tasks.
- kevin.obrien 66193 - Monday, February 20, 2017 9:26 AM
I was thinking more that a Child would be assigned it owns tag (which could be the same as the parent). Maybe a different way to ask.
Can a Parent, or Child, ever be assigned more than one tag? I.e. It can have 0 or 1 tags only.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, February 20, 2017 9:32 AM
Yes. A parent and/or child can have more than one tag.
I might have a Tag table that contains for example 4 rows - tagA, tagB, tagC, tagD.
tagA gets ParentTaskA and ParentTaskB assigned to it, ChildTask1 and ChildTask2 of ParentTaskA, and ChildTask9 of ParentTaskB also get assigned to tagA. All other ChildTasks of these ParentTasks do NOT get assigned to tagA.tagB gets ParentTaskA and all it's ChildTasks assigned.
tagC gets ParentTaskB and only one of it's ChildTasks assigned. - kevin.obrien 66193 - Monday, February 20, 2017 9:41 AM
Ok, sorry, but your statement that Single Tag can relate to a Parent or Child confused the matter, as it simply wasn't true. Many Tags can have many Child or Parent tasks.
Having a single TagDetail Table is the right idea here then. Ideally you should have one composite table for each Many to Many relationship. Otherwise, using the above logic things become very confusing when you have a parent being assigned a tag, but it has no children, OR none of it's Children are assigned that tag.
I would suggest instead that you actually have a set up of two composite tables, one TagParent table and one TagChild Table. These would handle the many to many between tags and the appropriate parents and children, without creating a NULLable composite key (which is bad practice).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Monday, February 20, 2017 9:58 AM
Sorry about that. I have updated my initial post.
So would this be a better design? Composite PKeys being:
- TagID and ParentID on one tbl
- TagID and ChildID on another tbl
Is it better to use composite key as opposed to surrogate key?Thanks!
-
Hi,
I am working on creating CRUD sprocs for this design.
For creating a Tag/Parent reference is the below sproc correct? I am not sure whether I should accept the identifiers as parameters or if it is ok to use the names? What is best practice?
CREATE PROCEDURE [Model].[TagParentDetail_Create]
@TagName nvarchar(150),
@ParentTaskName varchar(50)
AS
BEGIN
-- get tagID
DECLARE @TagID int;
SELECT @TagID = TagID
FROM [Model].[Tag]
WHERE TagName = @TagName;
-- get parentID
DECLARE @ParentTaskID int;
SELECT @ParentTaskID = ParentTaskID
FROM [Model].[ParentTask]
WHERE ParentTaskName = @ParentTaskName;
-- insert tag parent detail
INSERT INTO [Model].[TagParentDetail](TagID, ParentTaskID)
VALUES(@TagID,@ParentTaskID);
ENDThanks.
-
A tag is associated with a child, and only indirectly with the parent. If a tag were associated with a parent, then ALL children of that parent would have that tag. This means that the ParentTaskID does not belong on the TagDetail record.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Tuesday, February 21, 2017 11:38 AM
Not necessarily. A tag could be associated with just a parent and not care about any child tasks.
So for example, "Parent Task A" might be "Send Notification". This task has 2 child tasks -> "Email CustomerA" and "Email CustomerB". However, for some users of this tag they might not care about those child tasks and for them there is only one task i.e. "Send Notification". Any child tasks on that are optional.
- kevin.obrien 66193 - Tuesday, February 21, 2017 1:23 PM
In that case, you shouldn't separate out your parent tasks from your child tasks. You should have one table which references itself. That way tags are applied to tasks and it doesn't care whether those tasks are parent tasks or child tasks.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - kevin.obrien 66193 - Monday, February 20, 2017 9:07 AMHi,
I have a table - TagDetail - that stores the many-to-many relationship between Tag, ParentTask, and ChildTask tables.
Basically any 'Tag' can relate to many ParentTask items and many/any ChildTask items. Not all ChildTask items of a ParentTask need to be related to a Tag.
I am looking for the best way to create a new 'Tag detail' using a stored proc.Do I need to pass a list of ParentTasks, and a list of ChildTasks? Or what is the best way - do I need to rethink the design here?
Sample data:
Design:
Thanks!
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Tuesday, February 21, 2017 3:10 PMkevin.obrien 66193 - Monday, February 20, 2017 9:07 AMHi,
I have a table - TagDetail - that stores the many-to-many relationship between Tag, ParentTask, and ChildTask tables.
Basically any 'Tag' can relate to many ParentTask items and many/any ChildTask items. Not all ChildTask items of a ParentTask need to be related to a Tag.
I am looking for the best way to create a new 'Tag detail' using a stored proc.Do I need to pass a list of ParentTasks, and a list of ChildTasks? Or what is the best way - do I need to rethink the design here?
Sample data:
Design:
Thanks!
In the future, could you follow basic netiquette that is been in place for 30 years? This means you post DDL instead of screenshots pictures and vague narrative. Are you working at a company where they give you only screenshots and vague narrative from which to program? If so, it is time for you to quit .
If you will simply be polite, follow netiquette's been established for decades, and not demand people read your mind, then you can get hundreds or thousands of dollars worth of free consulting. But if you do not some of us, not mentioning myself, will put your name in a file called "something derogatory"so that if anyone is looking for a reference on you in the database community, we will not say nice things about you 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
- drew.allen - Tuesday, February 21, 2017 2:36 PM
Got to agree with Drew here, a much better idea and makes your life a lot easier. Unless there are any signification differences between a Child and Parent, however, your database diagram doesn't present that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - jcelko212 32090 - Tuesday, February 21, 2017 3:10 PM
Apologies. I have updated my original post. Please let me know if it is not suitable.
Thanking you.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply