Tracking a user submitted rating in SQL

Question

Consider a site where a user can rate games. The ratings available are: None, Liked, or Disliked. The code will keep track 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.

Answer 1

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.

Answer 2

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);