评论投票数据库结构的最佳做法 [英] Best practice for comment voting database structure

问题描述

我正在开发一个PHP应用程序，它有几个可以评论的对象。每个评论都可以投票，用户可以给它+1或-1（如Digg或Reddit）。现在我计划有一个投票表，其中包含user_id和他们的投票信息，这似乎工作正常。

事情是，每个对象数百个注释存储在单独的注释表中。加载评论后，我必须统计投票，然后单独检查每个投票反对用户，以确保他们只能投票一次。这工作，但只是似乎真的数据库密集 - 很多查询只是的意见。

有一个更简单的方法，这是少DB密集？

要清楚当前的数据库结构：

strong>注释表格： $ b

total_votes

投票表格：

$ b b

• comment_id


• user_id


• 投票

结束目标：

• 允许用户只对每条评论投一次与最少的MySQL查询数（每个对象有多个注释）

解决方案

每个选民只投票一次，设计您的Votes表与这些字段— CommentID，UserID，VoteValue。使CommentID和UserID为主键，这将确保一个用户只得到一个投票。然后，要查询投票以获取评论，请执行以下操作：

  SELECT SUM（VoteValue）
 FROM Votes 
 WHERE CommentID =？ 
  

这是否有帮助？

I'm working on a PHP app that has several objects that can be commented on. Each comment can be voted on, with users being able to give it +1 or -1 (like Digg or Reddit). Right now I'm planning on having a 'votes' table that has carries user_id and their vote info, which seems to work fine.

The thing is, each object has hundreds of comments that are stored in a separate comments table. After I load the comments, I'm having to tally the votes and then individually check each vote against the user to make sure they can only vote once. This works but just seems really database intensive - a lot of queries for just the comments.

Is there a simpler method of doing this that is less DB intensive? Is my current database structure the best way to go?

To be clearer about current database structure:

Comments table:

• user_id
• object_id
• total_votes

Votes table:

• comment_id
• user_id
• vote

End Goal:

• Allow user to vote only once on each comment with least # of MySQL queries (each object has multiple comments)

解决方案

To make sure that each voter votes only once, design your Votes table with these fields—CommentID, UserID, VoteValue. Make CommentID and UserID the primary key, which will make sure that one user gets only one vote. Then, to query the votes for a comment, do something like this:

SELECT SUM(VoteValue)
FROM Votes
WHERE CommentID = ?

Does that help?

这篇关于评论投票数据库结构的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！