Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

database scheme

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?

share|improve this question

2 Answers 2

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.
share|improve this answer
    
Wow, never heard of STI. Great! –  Slevin Jan 23 '13 at 14:03

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
share|improve this answer
1  
Thanks a lot! Very clean, very useful :) But i think i need has many ... through for the User <-> Task relation, cause i want to display all tasks assigned to a user and all users assigned to a task (a task can be assigned to many users). –  Slevin Jan 23 '13 at 14:05
    
@Slevin Please mark this as the answer if it has helped you. –  Ameen Oct 7 '13 at 10:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.