title: Design pattern for memcached data caching
body: <p>It's easy to wrap optional memcached caching around your existing database queries.  For example:</p>
  
  <p>Old (DB-only):</p>
  
  <pre><code>function getX
      x = get from db
      return x
  end
  </code></pre>
  
  <p>New (DB with memcache):</p>
  
  <pre><code>function getX
      x = get from memcache
      if found
        return x
      endif
  
      x = get from db
      set x in memcache
      return x
  end
  </code></pre>
  
  <p>The thing is though, that's not always how you want to cache.  For instance take the following two queries:</p>
  
  <pre><code>-- get all items (recordset)
  SELECT * FROM items;
  
  -- get one item (record)
  SELECT * FROM items WHERE pkid = 42;
  </code></pre>
  
  <p>If I was to use the above pseudo-code to handle the caching, I would be storing all fields of item 42 twice.  Once in the big record set and once on its own.  Whereas I'd rather do something like this:</p>
  
  <pre><code>SELECT pkid FROM items;
  </code></pre>
  
  <p>and cache that index of PK's.  Then cache each record individually as well.</p>
  
  <p>So in summary, the data access strategy that will work best for the DB doesn't neatly fit the memcache strategy.  Since I want the memcache layer to be optional (i.e. if memcache is down, the site still works) I kind of want to have the best of both worlds, but to do so, I'm pretty sure I'll need to maintain a lot of the queries in 2 different forms (1. fetch index, then records; and 2. fetch recordset in one query).  It gets more complicated with pagination.  With the DB you'd do LIMIT/OFFSET SQL queries, but with memcache you'd just fetch the index of PK's and then batch-get the relevant slice of the array.</p>
  
  <p>I'm not sure how to neatly design this, does anyone have any suggestions?</p>
  
  <p>Better yet, if you've come up against this yourself.  How do you handle it?</p>