PHP 和 MySQL 中的评级系统 [英] Rating System in PHP and MySQL

问题描述

如果我们查看 stackoverflow 网站，就会有投票.但问题是存储谁投票和谁没有投票的最佳方式是什么.让我们也更简化一下，说我们只能投票支持，我们只能删除支持.

If we look at the stackoverflow website we have votes. But the question is what is the bestway to store who has voted and who has not. Lets also simplify this even more and say that we can only vote Up, and we can only Remove the Up vote.

我想把桌子做成这样的形式

I was thinking having the table to be in such form

问题 - Id(INT) |用户 ID(INT) |标题(文本) |投票(INT) |ratingBy(TEXT)

Thrre rest 是不言自明的，但 ratingBy 是用户的逗号分隔 Id 值.

Thre rest is self explanitory but ratedBy is a Comma Seperated Id values of the Users.

我想读取 ratedBy 并将其与当前登录用户的 userId 进行比较.如果他不在 ratedBy 中，他可以投票，否则他可以取消他的投票.这反过来将从 ratedBy

I was thinking to read the ratedBy and compare it with the userId of the current logged in User. If he dosent exist in the ratedBy he can vote Up, otherwise he can remove his vote. Which in turn will remove the value from ratedBy

推荐答案

我觉得换一张桌子投票"比较好.用户和投票之间的关系是 n 到 n，因此应该创建一个新表.应该是这样的:

I think to make another table "vote" is better. The relationship between users and votes is n to n, therefore a new table should be created. It should be something like this:

question id (int) | user id (int) | permanent (bool) | timestamp (datetime)

永久字段可用于在给定时间后保留选票，正如 SO 所做的那样.
可以根据需要的特征添加其他字段.由于每行至少需要 16B，因此在表使用 4GB 之前，表中最多可以有 250M 行(如果每个表有一个存档，则限制为 fat32，MyISAM 和 InnoDB 就是这种情况).
此外，正如 Matthew Scharley 在评论中指出的那样，不要一次将所有投票加载到内存中(如获取结果集中的所有表).您始终可以使用 LIMIT 子句来缩小查询结果的范围.

Permanent field can be used to make votes stay after a given time, as SO does.
Other fields may be added according to desired features. As each row will take at least 16B, you can have up to 250M rows in the table before the table uses 4GB (fat32 limit if there is one archive per table, which is the case for MyISAM and InnoDB).
Also, as Matthew Scharley points out in a comment, don't load all votes at once into memory (as fetching all the table in a resultset). You can always use LIMIT clause to narrow your query results.

这篇关于PHP 和 MySQL 中的评级系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！