0

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?

Improve this question

2 Answers 2

Reset to default
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.
Improve this answer
1
  • Wow, never heard of STI. Great! Commented Jan 23, 2013 at 14:03
1

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
Improve this answer
2
  • 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). Commented Jan 23, 2013 at 14:05
  • @Slevin Please mark this as the answer if it has helped you. Commented Oct 7, 2013 at 10:12

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.