Adding attachments to task records in T_SQL

Question

I have an application which stores tasks and I want to add attachments to those tasks.

I have tried three different ways of doing this and don't know if any of them are correct and am looking for advice on where to go:

For example, simplified I have used a table:

+----------------------------------------------------------------------------+
| TaskID   Description  attachmentString                                     |
+----------------------------------------------------------------------------+
| 1         Task1       "FileName1:::fileLocation;FileName2:::fileLocation"  |
| 2         Task2       "FileName3:::fileLocation;FileName4:::fileLocation"  |
+----------------------------------------------------------------------------+

This is similar to how profile data is stored in ASP.NET membership.

I have also tried:

+---------------------------+
| TaskID   Description      |
+---------------------------+
| 1         Task1           |
| 2         Task2           |
+---------------------------+

+------------------------------------------------------+
| AttachmentId   Description  Location       TaskId    |
+------------------------------------------------------+
| 1              FileName1   FileLocation    1         |
| 2              FileName2   FileLocation    1         |
+------------------------------------------------------+

If I use the first option, I can just select tasks and get all the attachment data in one SQL call; but it seems cluncky to me to have to then parse the string. Its also not very "relational"

However using an attachment Id, if I want to get the attachments, I either JOIN both tables on attachmentId and then have number of attachments x number of tasks returned. I can have up to 5 attachments so for 50 tasks, it could return 250 rows of which the first columns (from the task table side of the JOIN) are repeated and this seems like a waste. Obviously I have a little more than just description in my table!!!

I have also considered just getting the task data and then just getting the attachment data separately and then joining them in my application. This returns less data than the second option, but requires two calls to the database and that seems wrong too.

I am doing this wrong? Is there a better way? Does anyone have any thoughts on the best way to do this.

I'm not very confident with SQL and maybe I have missed something huge so any pointers would be gratefully received.

Answer 1

The right design is obviously two tables. Having only one table violates the first normal form.

Relating to the load problem, both approaches are correct.

Joining the tables in the sql statement is what most ORM's do to eagerly load related objects. Obviously there is some network traffic overhead, but I think it is acceptable.

Executing two separate sql statements is also correct. You can send them together in one batch to SQL Server to save roundtrips. It has a disadvantage although, you need to perform the join at the client side.

So, are you willing to write more code to save some network traffic?

EDIT:

Given the following table and data:

CREATE TABLE Tasks
(
    TaskId int IDENTITY(1,1) PRIMARY KEY,
    TaskDescription nvarchar(500) NOT NULL
)
CREATE TABLE TaskAttachments
(
    AttachmentId int IDENTITY(1,1) PRIMARY KEY,
    TaskId int NOT NULL REFERENCES Tasks(TaskId),
    [FileName] nvarchar(500) NOT NULL,
    [FileLocation] nvarchar(500) NOT NULL
)
GO
INSERT INTO Tasks VALUES 
('Task1'), ('Task2')

INSERT INTO TaskAttachments VALUES
(1, 'FileName1', 'File location 1'),
(1, 'Filename2', 'File location 2'),
(2, 'FileName3', 'File location 3'),
(2, 'Filename4', 'File location 4')

The following classes:

public class TaskAttachment
{
    public int AttachmentId { get; set; }
    public string FileName { get; set; }
    public string FileLocation { get; set; }
}

public class AppTask
{
    public int TaskId { get; set; }
    public string TaskDescription { get; set; }

    public List<TaskAttachment> Attachments { get; set; }

    public AppTask()
    {
        this.Attachments = new List<TaskAttachment>();
    }
}

The following class loads the tasks with its attachments by executing two select statements in one single batch:

    public class DataLayer
    {

        private readonly SqlConnection connection;

        public DataLayer(SqlConnection connection)
        {
            this.connection = connection;
        }

        public List<AppTask> GetTasks()
        {
            var commandText = @"
SELECT TaskId, TaskDescription FROM Tasks;
SELECT AttachmentId, TaskId, [FileName], FileLocation FROM TaskAttachments;
";

            using (var cmd = new SqlCommand(commandText, connection))
            using (var reader = cmd.ExecuteReader())
            {
                var tasks = new List<AppTask>();
                while (reader.Read())
                {
                    var task = new AppTask
                    {
                        TaskId = reader.GetInt32(0),
                        TaskDescription = reader.GetString(1)
                    };
                    tasks.Add(task);
                }
                var taskDic = tasks.ToDictionary(x => x.TaskId);
                reader.NextResult();
                while (reader.Read())
                {
                    var attachment = new TaskAttachment
                    {
                        AttachmentId = reader.GetInt32(0),
                        TaskId = reader.GetInt32(1),
                        FileName = reader.GetString(2),
                        FileLocation = reader.GetString(3)
                    };

                    var task = taskDic[attachment.TaskId];
                    task.Attachments.Add(attachment);
                }
                return tasks;
            }
        }
    } 

You can use the above class like this:

using (var cn = new SqlConnection("Data Source=(local);Initial Catalog=Tests;Integrated Security=SSPI"))
{
    var dataLayer = new DataLayer(cn);
    cn.Open();
    var tasks = dataLayer.GetTasks();
}