Rating System in PHP and MySQL

问题:

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

question - Id(INT) | userId(INT) | title(TEXT) | vote(INT) | ratedBy(TEXT)

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

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

回答1:

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)

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.

回答2:

A new table:

Article ID | User ID | Rating

Where Article ID and User ID make up the composite key, and rating would be 1, indicating upvote, -1 for a downvote and 0 for a removed vote (or just remove the row).

回答3:

I believe your design won't be able to scale for large numbers of voters. The typical thing to do is to create to tables

Table 1: question - Id(INT) | userId(INT) | title(TEXT)
Table 2: question - ID(INT) | vote(INT) | ratedBy(TEXT)

Then you can count the votes with a query like this:

SELECT t1.question_Id, t1.userId, t1.title, t2.sum(vote)
FROM table1 t1
LEFT JOIN table2 t2 ON t1.question_id = t2.question_id