How to improve database schema with different n:m and 1:n associations between users/tasks/comments/attachments

Question

I'm trying to build a database for an upcoming Ruby on Rails webproject.

There will be 4 tables minium: tasks, users, attachments and comments. And 1 table to join the n:m relations (tasks <-> users).

One task has many users, many comments and many attachments. One comment has many attachments and was created by one user. One attachment was created by one user.

Users can have many tasks, many comments and many attachments.

A little sketch to illustrate my thougts:

The tables with 1:n relations contains the ID from the related table (e.g.: one comment contains the user id)

Do you think this scheme follows the normalization rules? Does this make sense or are there any redundancies? I think there is a little problem with the attachment table, cause this must contain a type field and a id field to differentiate between task and comment.

And all content types (tasks, comments, attachments) are assigned by many users (tasks) or created by (comments, attachments) one user.

Comments are not recursive.

I've considered the following pseudocode:

tasks = getTasks
each tasks as task

    # get task content

    users = getUsersByTaskID
    each users as user
        #get user content

    attachments = getAttachmentsByTypeAndTaskID
    each attachments as attachment
        # get attachment content
        user = getUserByID

    comments = getCommentsByTaskID
    each comments as comment
        # get comment content
        user = getUserByID
        attachments = getAttachmentsByTypeAndCommentID  
        each attachments as attachment
            # get attachment content
            user = getUserByID

I think it's still a little bit too complicated. Did i overlooked something? Are there better ways to implement my ideas?

Answer 1

I think your schema is clear:

1. I do not see redundancies

2. on your doubts regarding the attachment table: rails makes it relatively easy to have CommentAttachment and TaskAttachment derive from a common Attachment model with single table inheritance (STI). STI is pretty much a standard rails setup. It comes at a certain cost, however, see e.g. this article for some discussion on the cons.
   Anyway, in your case, I would say that STI is the right choice because:

   • the 2 attachment-types share all attributes.
   • only their behaviour changes according to their type.

Answer 2

Edit: I've reread your requirements and shifted my design a bit.

I'd contemplate orienting your models around the task where possible:

class User; end

class Task
  belongs_to :created_by,    :class_name => "User"
end

class TaskAssignment
  belongs_to :task
  belongs_to :assigned_to,   :class_name => "User"
  belongs_to :created_by,    :class_name => "User"
end

class Comment
  belongs_to :task
  belongs_to :created_by,    :class_name => "User"
end

class Attachment
  belongs_to :created_by,    :class_name => "User"

  # Consider connecting attachments to :comment (and thus indirectly to a parent task) 
  # instead of directly to the :task
  #
  belongs_to :task
end