Writing a comment table in a database

Question

I am working on a social media system in which comments from several different places will appear. You can be friends, you can be events, you can be groups - just like Facebook. I am wondering, from a practical point of view, what is the easiest way to write a comment table? Should I do all this in one table and allow foreign keys for different tables or does each individual table have its own comment table? Thanks for the help!

Answer 1

One comment table is a more elegant design, I think. Instead of several FKs, consider an intermediate table - CommentedItem. So, Friend, Event, Group, etc. There is an FK for CommentedItem, and you create a CommentedItem row for each new row in each of these tables. Now comments need only one FK, in 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 2

I did both, and the answer depends on the situation. For what you are trying to do, I would make the SINGLE table "Comments" and then split the "linker" tables. This will give you better performance since you can achieve the Perfect Index . "

I would also recommend putting the CommentTypeID field in the Comments table to give a hint about which link table you will choose for additional information.

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

Answer 3

This is the equivalent question of this .

EDIT: Based on the comment, it is not clear that this is an equivalent question, so I wrote it down below.

Both questions ask questions about projects (both are social networks, but it's just a coincidence) when the question arises about database performance. Both have a diverse set of objects that have a common collection of attributes (in one, these are events that occur on each object, and in the other, comments that occur on each object).

Both questions effectively ask the question: is it more efficient to create a UNION query that combines disparate common functions or decompose them into a common table with the corresponding foreign keys.

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

(If you do not agree, I am glad to hear why, please leave a comment.)

Answer 4

you need to be careful if you are not building a highly standardized database, this can sometimes lead to a chain of I / O chains and tables.

I believe that the oracle proposes to fulfill the normalization model around the 3rd normal form.

Answer 5

I would go for polymorphic associations . Many modern web developments support it out of the box, which makes it really the easiest and most painless way to handle such relationships.

Answer 6

In fact, you can go to http://www.zazazine.com and view your articles. You can find the answer there.