如何有效地设计MySQL数据库为我的特定情况 [英] How to efficiently design MySQL database for my particular case

问题描述

我正在开发一个PHP MySQL的论坛。我想让我的论坛尽可能高效。

我创建了这两个表格

1. tbl_threads


2. tbl_comments

现在，问题是喜欢和不喜欢按钮下每个评论。我必须存储用 comment_id 点击了Like或Dislike按钮的 user_name 。我已经在 tbl_comments 中创建了列user_likes和列 user_dislikes ，以存储逗号分隔的user_names。但在这个论坛上，我已经看到这不是一种有效的方式。我建议创建第三个表来存储喜欢和不喜欢，并遵守我的数据库设计与1NF。

但问题是，如果我做一个第三个表 tbl_user_opinion 并创建两个字段如下
1. comment_id
2.类型（喜欢或不喜欢）

$ b b

所以，我必须运行尽可能多的sql查询，在我的页面上有评论，以获得喜欢和不喜欢每个评论的数据。会不会效率低下。我认为这里有一些混乱我的部分。

解决方案

逗号分隔列表违反了 -friendly：it groups up-votes属于同一个注释物理上靠近在一起（同上下投票），使以下查询相当高效：

  SELECT 
 COMMENT.COMMENT_ID，
< other COMMENT fields>，
 COUNT（DISTINCT UP_VOTE.USER_ID） -  COUNT（DISTINCT DOWN_VOTE.USER_ID）SCORE 
 FROM COMMENT 
 LEFT JOIN UP_VOTE 
 ON COMMENT.COMMENT_ID = UP_VOTE.COMMENT_ID 
 LEFT JOIN DOWN_VOTE 
 ON COMMENT.COMMENT_ID = DOWN_VOTE.COMMENT_ID 
 WHERE 
 COMMENT.COMMENT_ID =< whatever> 
 GROUP BY 
 COMMENT.COMMENT_ID，
< other COMMENT fields> ;; 
  

请衡量实际的数据量，如果你的速度足够快。如果没有，则对模型进行反标准化，并在COMMENT表中缓存总分，并在每次向* _VOTE表插入或删除新行时，通过触发器使其保持当前状态。

如果您还需要获取特定用户投票的评论，您需要使用* _VOTE {USER_ID，COM​​MENT_ID}的索引，即上述主要/群集密钥的反向。 ¹

---

¹ 这是我没有去的原因之一只有一个VOTE表包含一个额外的字段，可以是1（用于投票）或-1（用于投票）： cover 次要索引。

I am developing a forum in PHP MySQL. I want to make my forum as efficient as I can.

I have made these two tables

1. tbl_threads
2. tbl_comments

Now, the problems is that there is a like and dislike button under the each comment. I have to store the user_name which has clicked the Like or Dislike Button with the comment_id. I have made a column user_likes and a column user_dislikes in tbl_comments to store the comma separated user_names. But on this forum, I have read that this is not an efficient way. I have been advised to create a third table to store the Likes and Dislikes and to comply my database design with 1NF.

But the problem is, If I make a third table tbl_user_opinion and make two fields like this 1. comment_id 2. type (like or dislike)

So, will I have to run as many sql queries as there are comments on my page to get the like and dislike data for each comment. Will it not inefficient. I think there is some confusion on my part here. Can some one clarify this.

解决方案

The comma-separate list violates the principle of atomicity, and therefore the 1NF. You'll have hard time maintaining referential integrity and, for the most part, querying as well.

Here is one way to do it in a normalized fashion:

This is very clustering-friendly: it groups up-votes belonging to the same comment physically close together (ditto for down-votes), making the following query rather efficient:

SELECT
    COMMENT.COMMENT_ID,
    <other COMMENT fields>,
    COUNT(DISTINCT UP_VOTE.USER_ID) - COUNT(DISTINCT DOWN_VOTE.USER_ID) SCORE
FROM COMMENT
    LEFT JOIN UP_VOTE
        ON COMMENT.COMMENT_ID = UP_VOTE.COMMENT_ID
    LEFT JOIN DOWN_VOTE
        ON COMMENT.COMMENT_ID = DOWN_VOTE.COMMENT_ID
WHERE
    COMMENT.COMMENT_ID = <whatever>
GROUP BY
    COMMENT.COMMENT_ID,
    <other COMMENT fields>;

[SQL Fiddle]

Please measure on realistic amounts of data if that works fast enough for you. If not, then denormalize the model and cache the total score in the COMMENT table, and keep it current it through triggers every time a new row is inserted to or deleted from *_VOTE tables.

If you also need to get which comments a particular user voted on, you'll need indexes on *_VOTE {USER_ID, COMMENT_ID}, i.e. the reverse of the primary/clustering key above.¹

---

¹ This is one of the reasons why I didn't go with just one VOTE table containing an additional field that can be either 1 (for up-vote) or -1 (for down-vote): it's less efficient to cover with secondary indexes.

这篇关于如何有效地设计MySQL数据库为我的特定情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！