MySQL comments table structure question

Question

How should my comments table look like when I allow a user to enter comments for a post. Below is my MySQL table as of right now. The uniqueID is to stop users from hitting the submit button multiple times.

CREATE TABLE acomments (
   commentID INT UNSIGNED NOT NULL AUTO_INCREMENT,
   parent_commentID INT UNSIGNED NOT NULL,
   userID INT UNSIGNED NOT NULL,
   articleID INT UNSIGNED NOT NULL,
   comment TEXT NOT NULL,
   dateCREATE DATETIME NOT NULL,
   uniqueID CHAR(32) NOT NULL,
   PRIMARY KEY (commentID),
   KEY userID (userID),
   KEY articleID (articleID)
);

Answer 1

If I understand correctly, you're filling the field that is inserted in uniqueID on the client-side, such that if a form is submitted twice with the same id, you would reject it. If this is the case, I would consider the following:

You may want to define a unique constraint on the uniqueID field, so that you will be able to enforce the constraint on a database level:

CREATE TABLE acomments (
   ...
   UNIQUE (uniqueID),
   ...
);

But you can also eliminate this field altogether, and simply add some logic in your application such that when a comment is submitted by a user, and the comment contains the same body text as the previous comment submitted by the user on the same answer, then reject it. In most situations, I believe I would go with this approach. It is not that important to enforce this rule on the database-level, since the data integrity will not be compromised. Pseudo example:

function commentSubmitted ($userID, $articleID, $commentBody) {
   $lastComment = queryDatabase("SELECT    comment 
                                 FROM      acomments 
                                 WHERE     user_id = $userID AND  
                                           article_id = $articleID
                                 ORDER BY  dateCREATE DESC
                                 LIMIT     1")

   if ($lastComment == $commentBody) {
      // Duplicate submission ...
   }
   else {
      // Insert new comment into the database ...
   }
}

Answer 2

There are easier ways to prevent a user hitting submit multiple times. For example, you can use javascript to disable the button once it's been pressed (or just ignoring subsequent presses). Or you can explicitly check if it's a duplicate comment at creation time (although that's more expensive). There's no need to clutter your database to solve that problem.

Also, shouldn't parent_commentID be nullable, for top level comments?