时间戳

首页 > 解决方案 > 帖子、评论、回复和点赞数据库架构

mysql - 帖子、评论、回复和点赞数据库架构

问题描述

我有一个网站，用户可以在其中评论帖子或回复评论。用户还可以喜欢回复或评论。但是，回复表中还有另一个名为 reply_to 的字段。这是我当前的架构：

Comment
id
user (foreign key)
post (foreign key)
comment

Reply
id
user (foreign key)
reply_to (who the user is replying to)
comment (foreign key)
reply

CommentLike (Table that shows which user liked which comments)
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

ReplyLike (Table that shows which user liked which replies)
id
reply (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

这似乎是一个很好的架构，还是有更好的方法来创建这种结构？

标签： mysqldatabase-designrelational-databaseschema

解决方案

我会建议如下结构，只有 2 个表：

Comment:
id
user (foreign key)
post (foreign key)
comment_text
parent_comment_id (null or -1 if a new comment and comment_id of the parent if a reply)


CommentLike (Table that shows which user liked which comments):
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)
  • 这样做的原因是因为reply它comment本身就是一个，对于某些父母的评论来说只是一个孩子。因此，我不会让它成为一个单独的实体。
  • 请注意，您需要处理删除操作并删除所有当前评论被删除的评论，因为它是 parent_id。您可以ON DELETE CASCADE为此提供帮助。

推荐阅读