How to make comment-quote function?

Question

We allow commenting on our website (like a blog). Now we want to change it so that people can comment other comments.

Our table looks something like this:

ID    |    CommentID    |    Comment     |    User     |     Date
-------------------------------------------------------------------------
1001  |        1        |    Nice site   |     Me      |     20.01.2010
1001  |        2        |    Thx!        |     You     |     21.01.2010

I came up with two options:

1) Create a ParentCommentID and with that decide how the comments are listed from top to bottom.

2) Store the comment you're commenting together with your own comment with some [QUOTE]-tags or something.

Any good tips to solving this?

Answer 1

What you're asking really is how normalized you want your database table to be. I would say this is a judgement call based on factors such as how large your website is and how far you believe you should take normalization. To use a similar example, in our forums we store the username of who posted a thread in the thread table - this is de-normalized for performance reasons - sometimes you just don't want to be doing lots of joins. The Wikipedia article on database normalization goes into more detail.

My personal take is I would store the comment with the quote inside it, as this prevents people from changing something they've already written (ninja editing) to obfuscate arguments/discussions ;)

Answer 2

Sounds like we need to do some more requirements gathering first!

• What type of database are you using (MySQL, MSSQL, etc)? What version? SQL Server 2008, for example, has a built in hierarchyId datatype for this sort of thing.
• Do you want to support more than one level of nested comments (ie. full hierarchical reply trees)?
• What kind of performance requirements do you have? How scalable and robust does this have to be?
• Would you ever need to report on replies or would a reply be strictly a piece of content? For instance, vBulletin has simple quoted replies that are just part of the message, whereas some blog commenting systems will alert you when you get a reply, and you can view replies to your comments separately.

Answer 3

I would go with a ParentCommentID that acts as a foreign key back to that table's CommentID. That will enforce referential integrity, and you'll avoid some duplication.

If ten people quote a comment, it is replicated ten times with option 2. In option one, you only have ten FKs.

The more characters you allow in a single comment, the more duplication you'll have per quote with option 2.

Option 1 also allows you to do more reporting. You can more easily query to find out which comments are quoted the most often.

Answer 4

The parentid is a good option. You can also store time along with date to find out the hierarchy of posted reply or you can even use the CommentID field for it.

Answer 5

If the comment ID isn't displayed on the page you could always just apply a numbering scheme like an IP address or an application version. I.E., CommentID 2.1 is the first comment on the second comment, and 2.2 is the second comment on the second comment. As it wouldnt take much of a string tokenizer to acknowledge CommentID 3.1.7 is the seventh comment on the first comment on the second comment of a post. Sorting becomes a breeze once you tokenize it into integers.