Tracking a user submitted rating in SQL
Consider a site where a user can rate games. The ratings available are: None, Liked, or Disliked. The code will keep tra开发者_StackOverflow中文版ck of the ratings just fine. The current problem is a user can rate a game and just keep re-rating it.
How would a SQL table look if I wanted to store a user's rating for a certain game? Duplicate ratings for each user/game shouldn't be allowed.
You can use the following tables:
user:
id
name
...
game:
id
name
...
user_rating:
user_id
game_id
rating
where user_id
and game_id
refer to user and game. They both form the PRIMARY KEY
, so the model does not support a user rating more than once.
rating
contains -1
, 0
, or +1
and can be summed to get the score of a game.
CREATE TABLE ratings(
user_id int,
game_id int,
rating_value int
);
ALTER TABLE ratings ADD CONSTRAINT pk_ratings
PRIMARY KEY(user_id, game_id);
The unique index created by the combined primary key will guarantee at the database level that no user will be able to vote twice on the same game. In addition it will be easy for your application to check if a user has already voted for a particular game.
You may also want to create an index on game_id
so that queries to check the ratings of the games would be able to use an index. You may want to include the rating_value
field in this index to make it a covering index for even better performance:
CREATE NONCLUSTERED INDEX ix_ratings
ON ratings(game_id, rating_value);
精彩评论