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?