SQL Server many-to-many relationship, row reuse?

Question

Below is your typical many-to-many relationship. Pretend there are 2 tasks that have the exact same Tags. because of foreign key/primary key relationship constraints, there is no way for the rows in the table TasksTag to be reused (both Tasks would have the same PK; impossible)

In my scenario, theoretically, a task will usually contain most or all tags, that means every Task will have roughly 300+ rows in the TasksTag table. This scales horribly because there will be a few hundred Tasks per month and a lot of which will have the same Tag list.

Can someone explain another way of doing this without removing the constraints? Am I worrying about space too much?

Thanks!

Answer 1

The below sample structure illustrates how you can do the TasksTags table most efficiently. The Tasks table enforces unique task names. The Tags table enforces unique tag names. The TasksTags table joins these together allowing any combination of Tasks and Tags.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TasksTags
(
    TaskID INT NOT NULL
    , TagID INT NOT NULL
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TaskID, TagID)
);
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (1,1);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (2,3);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,2);
INSERT INTO dbo.TasksTags (TaskID, TagID) VALUES (3,1);


SELECT TK.TaskName, TG.TagName
FROM dbo.TasksTags TT
    INNER JOIN dbo.Tasks TK ON TT.TaskID = TK.TaskID
    INNER JOIN dbo.Tags TG ON TG.TagID = TT.TagID;

EDIT

The following allows a list of tags to be associated with more than one task. You could blend this with the prior structure to create a totally customizable list of tags for each task.

USE tempdb;
CREATE TABLE dbo.Tasks
(
    TaskID INT NOT NULL CONSTRAINT PK_Tasks PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TaskName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tasks_TaskName UNIQUE
);
CREATE TABLE dbo.Tags
(
    TagID INT NOT NULL CONSTRAINT PK_Tags PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagName VARCHAR(255) NOT NULL CONSTRAINT UQ_Tags_TagName UNIQUE
);
CREATE TABLE dbo.TagList
(
    TagListID INT NOT NULL CONSTRAINT PK_TagList PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , TagListName VARCHAR(255) NOT NULL CONSTRAINT UQ_TagList_Name UNIQUE
);
CREATE TABLE dbo.TagListTags
(
    TagListID INT NOT NULL CONSTRAINT FK_TagListTags_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , TagID INT NOT NULL CONSTRAINT FK_TagListTags_TagID FOREIGN KEY REFERENCES dbo.Tags(TagID)
    , CONSTRAINT PK_TasksTags PRIMARY KEY CLUSTERED (TagListID, TagID)
);
CREATE TABLE dbo.TasksTagList
(
    TaskID INT NOT NULL CONSTRAINT FK_TasksTagList_TaskID FOREIGN KEY REFERENCES dbo.Tasks(TaskID)
    , TagListID INT NOT NULL CONSTRAINT FK_TasksTagList_TagListID FOREIGN KEY REFERENCES dbo.TagList(TagListID)
    , CONSTRAINT PK_TasksTagList PRIMARY KEY CLUSTERED (TaskID, TagListID)
);


INSERT INTO dbo.Tasks (TaskName) VALUES ('Task1');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task2');
INSERT INTO dbo.Tasks (TaskName) VALUES ('Task3');

INSERT INTO dbo.Tags (TagName) VALUES ('MyTag1');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag2');
INSERT INTO dbo.Tags (TagName) VALUES ('MyTag3');

INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 1');
INSERT INTO dbo.TagList (TagListName) VALUES ('Tag List 2');

INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 2);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (1, 3);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 1);
INSERT INTO dbo.TagListTags (TagListID, TagID) VALUES (2, 3);

INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (1, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 1);
INSERT INTO dbo.TasksTagList (TaskID, TagListID) VALUES (2, 2);


SELECT Tasks.TaskName, Tags.TagName
FROM dbo.TasksTagList TTL
    INNER JOIN dbo.TagListTags TLT ON TTL.TagListID = TLT.TagListID
    INNER JOIN dbo.Tags ON TLT.TagID = Tags.TagID
    INNER JOIN dbo.Tasks ON TTL.TaskID = Tasks.TaskID;