title: Efficient MySQL table structure for rating system
body: <p>This post is a follow-up of this answered question: <a href="https://stackoverflow.com/questions/620645/best-method-for-storing-a-list-of-user-ids">Best method for storing a list of user IDs</a>. </p>
  
  <p>I took cletus and Mehrdad Afshari's epic advice of using a normalized database approach. Are the following tables properly set up for proper optimization? I'm kind of new to MySQL efficiency, so I want to make sure this is effective. </p>
  
  <p>Also, when it comes to finding the average rating for a game and the total number of votes should I use the following two queries, respectively?</p>
  
  <pre><code>SELECT avg(vote) FROM votes WHERE uid = $uid AND gid = $gid;    
  SELECT count(uid) FROM votes WHERE uid = $uid AND gid = $gid;
  
  CREATE TABLE IF NOT EXISTS `games` (
    `id` int(8) NOT NULL auto_increment,
    `title` varchar(50) NOT NULL,
    PRIMARY KEY  (`id`)
  ) AUTO_INCREMENT=1 ;
  
  CREATE TABLE IF NOT EXISTS `users` (
    `id` int(8) NOT NULL auto_increment,
    `username` varchar(20) NOT NULL,
    PRIMARY KEY  (`id`)
  ) AUTO_INCREMENT=1 ;
  
  
  CREATE TABLE IF NOT EXISTS `votes` (
    `uid` int(8) NOT NULL,
    `gid` int(8) NOT NULL,
    `vote` int(1) NOT NULL,
    KEY `uid` (`uid`,`gid`)
  ) ;
  </code></pre>