1

happy day.

I am creating a database of movies, all movies will receive one vote for each user, valuing as it seemed the movie, "Good, good or fair" These votes of these stored in a table [movies_has_rating] that have the ID of the user who made the vote, with the type of vote, and the creation date, I need to ensure and prevent a user can participate twice in a movie, as only one vote per user is allowed, although this already I have done through PHP and MySQL queries, there is still the possibility of adding it manually from MySQL, and would also be able to establish this same default MySQL, my question is:

1) If the fields are defined [vote id] and [user_id] as primary keys, you can avoid having two evaluations of a user for the same film, example.

CREATE TABLE `user_has_rating` (
    `movie_id`  int UNSIGNED NOT NULL ,
    `vote_id`  int UNSIGNED NOT NULL ,
    `user_id`  int UNSIGNED NOT NULL ,
    `create_at`  datetime NOT NULL ,
    PRIMARY KEY (`vote_id`, `user_id`)
)

2) It is necessary to add to the fields UNIQUE INDEX [vote id] and [user_id], when they are already defined as primary keys, which has advantages and differences using UNIQUE INDEX when we defininas primary keys.

CREATE TABLE `user_has_rating` (
    `movie_id`  int UNSIGNED NOT NULL ,
    `vote_id`  int UNSIGNED NOT NULL ,
    `user_id`  int UNSIGNED NOT NULL ,
    `create_at`  datetime NOT NULL ,
    PRIMARY KEY (`vote_id`, `user_id`),
    UNIQUE INDEX `vote_id` (`vote_id`) ,
    UNIQUE INDEX `user_id` (`user_id`) 
);

3) It is necessary to specify the type of method the index, such as "BTREE or HASH"

CREATE TABLE `user_has_rating` (
    `movie_id`  int UNSIGNED NOT NULL ,
    `vote_id`  int UNSIGNED NOT NULL ,
    `user_id`  int UNSIGNED NOT NULL ,
    `create_at`  datetime NOT NULL ,
    PRIMARY KEY (`vote_id`, `user_id`),
    UNIQUE INDEX `vote_id` (`vote_id`) USING BTREE ,
    UNIQUE INDEX `user_id` (`user_id`) USING BTREE 
);

Much appreciate your help, thank you very much!

Learning and sharing
  • 1,378
  • 3
  • 25
  • 45
  • Personally I think the setup is wrong, you should have a table where all movies are listed and have an ID. That ID belongs in the table `user_has_rating` to know if the user voted on that movie. Unique or multiple keys should have nothing to do with it. – Xorifelse Mar 26 '16 at 20:14
  • @Xorifelse Thank you very much, and this corrected, I will step specify the field of movies. – Learning and sharing Mar 26 '16 at 21:03
  • @Learningandsharing 1) You don't need a vote_id `PRIMARY KEY (movie_id, user_id)` will do what you want. 2) No you don't. A unique key on user_id would prevent a user from voting two different movies. 3) You don't need to. If you don't specify it default values will be used. – Rocki Mar 26 '16 at 21:19
  • @Rocki, That such, thank you very much for answering, the user you may vote in many films, but can only vote in one movie, my question is clear, it is more convenient to define the columns [vote id] and [user_id] as primary ? or define UNIQUE INDEX [vote id] and [user_id]? – Learning and sharing Mar 26 '16 at 22:29

0 Answers0