I'm working on a PHP/MySQL rating system right now. For the user to be able to rate the user has to log in. Each user has a unique "UID". There will be multiple rating instances on the website (one for every game, in my case) and I need an efficient way of storing a list of UIDs in a MySQL row (one MySQL row in the ratings table for each instance of the rating system) to keep a tally of who has voted.
我正在研究PHP / MySQL评级系统。对于用户能够评价用户必须登录。每个用户都有一个唯一的“UID”。网站上会有多个评级实例(每个游戏一个,在我的情况下),我需要一种在MySQL行中存储UID列表的有效方法(评级系统的每个实例的评级表中有一个MySQL行)保持一个谁投票的记录。
I've seen in other systems that the list is stored in a serialized PHP array. Every time a user votes, then serialized array has to extracted, unserialized, the new UID is inserted, the array is re-serialized, and the MySQL row is UPDATEd. Every time the page is loaded, that list has to once again be unserialized and checked to see if the user viewing the page has voted yet to make sure the user doesn't vote twice.
我在其他系统中看到列表存储在一个序列化的PHP数组中。每次用户投票时,序列化数组必须被提取,反序列化,插入新的UID,重新序列化数组,并且MySQL行是UPDATEd。每次加载页面时,该列表必须再次被反序列化并检查以查看查看该页面的用户是否已经投票以确保用户没有投票两次。
This seems kind of inefficient and cumbersome. Does MySQL have a built in list function to help this process be more efficient? Are there any more clever ways I could fix this problem?
这似乎是低效和繁琐的。 MySQL是否具有内置列表功能以帮助此过程更高效?有没有更聪明的方法可以解决这个问题?
I've considered one possible alternative which is forget the serializing and store the UIDs in a TEXT type field in the MySQL database. I would just append some non-numeric character after each UID (let's say a period [.]). To add a user entry I would just concatenate the UID onto the end of the TEXT field and then a period. When checking if the user has already voted I could just "SELECT * FROM table WHERE votes = '%$UID.%';". Would this work more efficiently or is there a more elegant way of getting the job done?
我已经考虑过一种可能的替代方法,即忘记序列化并将UID存储在MySQL数据库的TEXT类型字段中。我会在每个UID之后附加一些非数字字符(比如句号[。])。要添加用户条目,我只需将UID连接到TEXT字段的末尾,然后连接句点。当检查用户是否已经投票时,我可以“SELECT * FROM table WHERE votes ='%$ UID。%';”。这会更有效地工作还是有更优雅的方式完成工作?
Follow-up post about the table structure... Efficient MySQL table structure for rating system
关于表结构的后续帖子...评级系统的高效MySQL表结构
2 个解决方案
#1
It is inefficient. What you have here in relational terms is a many-to-many relationship between users and games. A user can vote on many games. A game can be voted on by many users. The solution for this is to have a join table:
效率低下。你在关系中的含义是用户和游戏之间的多对多关系。用户可以对许多游戏进行投票。许多用户都可以投票。解决方案是使用连接表:
USERS (uid, name, ...)
GAMES (gid, name, ...)
VOTES (id, uid, gid, ...)
Where uid and gid are foreign keys back to their respective tables.
其中uid和gid是返回各自表的外键。
If someone votes, insert a record in VOTES.
如果有人投票,请在VOTES中插入记录。
To get a list of votes for a game:
获取游戏的投票列表:
$get = mysql_query("SELECT * FROM votes WHERE gid = $game_id");
...
To get a list of the user's votes:
要获取用户投票的列表:
$get = mysql_query("SELECT * FROM votes WHERE uid = $user_id");
...
and so on.
等等。
Don't join an array and store it in a single column. You're right to avoid that.
不要加入数组并将其存储在单个列中。你是对的,可以避免这种情况。
#2
In a normalized database, you should store it in a junction table:
在规范化数据库中,您应该将其存储在联结表中:
UserRatings
-------------
RatingID int
UserID int
UserVote int
I don't know what are your queries. Depending on the application, this might not have the acceptable performance. However, in either case, I suggest you go with the normalized approach, benchmark, and denormalize only if appropriate.
我不知道你的疑问是什么。根据应用程序的不同,这可能没有可接受的性能。但是,在任何一种情况下,我建议您仅在适当时使用规范化方法,基准和非规范化。
#1
It is inefficient. What you have here in relational terms is a many-to-many relationship between users and games. A user can vote on many games. A game can be voted on by many users. The solution for this is to have a join table:
效率低下。你在关系中的含义是用户和游戏之间的多对多关系。用户可以对许多游戏进行投票。许多用户都可以投票。解决方案是使用连接表:
USERS (uid, name, ...)
GAMES (gid, name, ...)
VOTES (id, uid, gid, ...)
Where uid and gid are foreign keys back to their respective tables.
其中uid和gid是返回各自表的外键。
If someone votes, insert a record in VOTES.
如果有人投票,请在VOTES中插入记录。
To get a list of votes for a game:
获取游戏的投票列表:
$get = mysql_query("SELECT * FROM votes WHERE gid = $game_id");
...
To get a list of the user's votes:
要获取用户投票的列表:
$get = mysql_query("SELECT * FROM votes WHERE uid = $user_id");
...
and so on.
等等。
Don't join an array and store it in a single column. You're right to avoid that.
不要加入数组并将其存储在单个列中。你是对的,可以避免这种情况。
#2
In a normalized database, you should store it in a junction table:
在规范化数据库中,您应该将其存储在联结表中:
UserRatings
-------------
RatingID int
UserID int
UserVote int
I don't know what are your queries. Depending on the application, this might not have the acceptable performance. However, in either case, I suggest you go with the normalized approach, benchmark, and denormalize only if appropriate.
我不知道你的疑问是什么。根据应用程序的不同,这可能没有可接受的性能。但是,在任何一种情况下,我建议您仅在适当时使用规范化方法,基准和非规范化。