title: Schema/Strategy and queries for ranking system
body: <p>I've got the following schema in my db:</p>
  
  <pre><code>| items | CREATE TABLE `items` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `description` text COLLATE utf8_unicode_ci,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
  
  | rankings | CREATE TABLE `rankings` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) DEFAULT NULL,
    `item_id` int(11) DEFAULT NULL,
    `rank` int(11) DEFAULT NULL,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
  </code></pre>
  
  <p>I basically want to users to be able to add items and rank them against each other. Administrators in my system would be able to view a list of items sorted by the average rank. I'm currently determining the average rank across all users with a query like this:</p>
  
  <pre><code>SELECT AVG(r.rank) AS rank, i.* FROM rankings r LEFT OUTER JOIN items i ON r.item_id = i.id GROUP BY i.id ORDER BY rank ASC;
  </code></pre>
  
  <p>What I'm struggling with is how to craft a query or have an insert strategy when a new item is added and has not been ranked by any users yet. The two viable strategies I see are:</p>
  
  <p>1) Construct the query to get the sorted list across all users such that unranked items are still accounted for but perhaps added to the end of the list for each user sorted by created date</p>
  
  <p>2) When a new item is added, add a row in the rankings table for each user for the new item, placing it last in the current rankings</p>
  
  <p>Thoughts?</p>