MYSQL schema for comment system

Question

I have a basic comment system on an app I'm creating with the following table setup:

CREATE TABLE `meet_comment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `meet_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `date_created` datetime NOT NULL,
 `comment` mediumtext NOT NULL,
 PRIMARY KEY (`id`),
 KEY `meet_id` (`meet_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `meet_comment_ibfk_1` FOREIGN KEY (`meet_id`) REFERENCES `meet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `meet_comment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

meet_id is a reference to the object the user is commenting on. This works great, although at the moment if a user edits the comment I am just updating the comment field.

I want to be able to see comment history if the comment gets edited, what's the best way to go about this? I'm guessing I will need another table that holds the comment and references meet_comment.id? Maybe like:

CREATE TABLE `meet_comment` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `meet_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `meet_id` (`meet_id`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `meet_comment_ibfk_1` FOREIGN KEY (`meet_id`) REFERENCES `meet` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `meet_comment_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `meet_comment_content` (
 `revision` int(3) NOT NULL,
 `meet_comment_id` int(11) NOT NULL,
 `date_created` datetime NOT NULL,
 `comment` mediumtext NOT NULL,
  UNIQUE KEY `revision_2` (`revision`,`meet_comment_id`),
 KEY `revision` (`revision`),
 KEY `meet_comment_id` (`meet_comment_id`),
 CONSTRAINT `meet_comment_content` FOREIGN KEY (`meet_comment_id`) REFERENCES `meet_comment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

If so, what would be the best way to query the tables, I'm guessing I can do a join to get the required data?

Answer 1

Revision history leads to a tug of war --

• Simplicity, or not
• Efficiency at scale, or not
• Complexity of the queries, or not
• Disk space, or bloat

Consider the following "general" principle:

Have two tables:

• Current -- the current version of the Comment
• History -- all versions of the Comment

Current makes the main queries simpler and more efficient.

Creating or editing a Comment means an INSERT or UPDATE in Current, plus (always) an INSERT into History.

History will have a slightly different schema than Current, since there needs to be a revision number, and maybe other dates/flags/etc.

This does not address disk space; I suspect you can last some time before needing to worry about such. One technique is to "compress" the main TEXT column and put it into a BLOB. (Note: another schema difference. And added complexity in the code.) Typical text shrinks by 3:1.

Side note: int(3) -- the (3) says nothing. An INT is always 4 bytes. Suggest using SMALLINT UNSIGNED, which takes 2 bytes and has a range of 0..64K.

I would abandon the FOREIGN KEYs, especially the CASCADE; there are likely to be references that will get tangled up in these two tables. Anyway, you will need to carefully write the code to handle the various tasks, thereby obviating the need for FKs.

Every table should have a PRIMARY KEY. For "clustering" of data, I recommend (comment_id, revision), not the other way around.

And, consider whether meet_id be part of the PK.

Answer 2

-- User USR exists.
--
user {USR}
  PK {USR}

-- Meet MET exists.
--
meet {MET}
  PK {MET}

-- User USR attended meet MET.
--
user_meet {USR, MET}
       PK {USR, MET}

FK1 {USR} REFERENCES user {USR}
FK2 {MET} REFERENCES meet {MET}

If a user commented on a meet, then that user attended that meet.

-- User USR commented CMT_TXT on meet MET,
-- as comment number CMT_NO from that user on that meet.
--
comment {USR, MET, CMT_NO, CMT_TXT}
     PK {USR, MET, CMT_NO}

FK {USR, MET} REFERENCES user_meet {USR, MET}

-- For any given (USR, MET), CMT_NO is in {1,2,3 ...}.

-- User USR commented CMT_TXT on meet MET,
-- in version number VER_NO of comment number CMT_NO
-- from that user on that meet.
--
comment_history {USR, MET, CMT_NO, VER_NO, CMT_TXT}
             PK {USR, MET, CMT_NO, VER_NO}

     FK {USR, MET, CMT_NO} REFERENCES
comment {USR, MET, CMT_NO}
     ON UPDATE CASCADE
     ON DELETE RESTRICT

-- For any given (USR, MET, CMT_NO), VER_NO is in {1,2,3 ...}.

I would suggest to treat deletion of a comment as a new version. You may, for example, set comment.CMT_TXT to an empty string. This way the history and data integrity are both preserved.

If comment_history contains only older versions, and the current is in comment, then use a view:

CREATE VIEW v_comment
AS
SELECT USR
     , MET
     , CMT_NO
     , 'current' as VER
     , CMT_TXT
FROM comment
UNION
SELECT USR
     , MET
     , CMT_NO
     , concat('ver_', lpad(VER_NO,3,0)) as VER
     , CMT_TXT
FROM comment_history ;

To query comments and histories for a specific user and meet:

SELECT *
FROM v_comment
WHERE USR = specific_user
  AND MET = specific_meet
ORDER BY CMT_NO, VER

Note:

All attributes (columns) NOT NULL

PK = Primary Key
FK = Foreign Key