Writing Comments table in database

Question

I'm working on a social networking system that will have comments coming from several different locations. One could be friends, one could be events, one could be groups--much like Facebook. What I'm wondering is, from a practical standpoint, what would be the simplest way to write a comments table? Should I do it all in one table and allow foreign keys to all sorts of different tables, or should each distinct table have its own comment table? Thanks for the help!

Answer 1

This is an equivalent question to this one.

EDIT: Based on a comment, it isn't clear that this is an equivalent question, so I spell it out below.

Both questions ask about projects (both happen to be Social Networks, but that's just coincidence) where there is a question about the performance of the database. Both have a diverse set of objects that share a common collection of attributes (in one it is Events, that occur on each object, in the other it is Comments that occur on each object).

Both questions effectively ask whether it is more efficient to create a UNION query that combines the disparate common features, or to factor them out into a common table, with appropriate foreign keys.

I see them as equivalent; the best answer to one will apply equally to the other.

(If you disagree, I am happy to hear why; please leave a comment.)

Answer 2

A single comments table is the more elegant design, I think. Rather than multiple FKs though, consider an intermediate table - CommentedItem. So Friend, Event, Group, etc all have FKs to CommentedItem, and you create a CommentedItem row for each new row in each of those tables. Now Comments only needs one FK, to CommentedItem. For example, to get all Comments for a given Friend:

SELECT * FROM Comment c
JOIN CommentedItem ci on c.CommentedItemId = ci.CommentedItemId
JOIN Friend f on f.CommentedItemId = ci.CommentedItemId
WHERE f.FriendId = @FriendId

Answer 3

I've done both and the answer depends on the situation. For what you are trying to do, I would do a SINGLE "Comments" table, and then seperate "linker" tables. This will give you the best performance as you can achieve the "Perfect Index".

I would also recommend putting a "CommentTypeID" field in the Comments table to give a 'clue' as to which linker table you will pull from for the aditional detail.

EDIT: The CommentTypeID field should not be used in the indexes, but rather it's only for use in code.

Answer 4

one thing to be careful about is if you don't do a highly normalized database it can sometimes cause IO row chaining and table scans.

I believe oracle suggests performing a normalization model of about 3rd Normal form.

Answer 5

I would go for polymorphic associations. Many modern web development frameworks support it out of the box, which makes it really the simplest and most painless way to handle these kind of relationships.

Answer 6

Actually you can probably go to http://www.zazazine.com and look through their articles. You may find an answer there