title: Database design for records with server and local copies
body: <p>I'm building an iOS app that works (creates, edits) records (in sqlite) that are synced to/from a server database.  If a record in the app has been downloaded from the server and then modified locally, I'm making a copy since I want to be able to revert to the server version.  Therefore, for a given record id, I can sometimes have two copies (server, local).  I am asking for assistance in designing the database layout.</p>
  
  <p>Initially, I used two tables - one for storing the server records (arriving via sync), and one for storing locally modified / locally created (yet to be synced) records.  I found this approach to be cumbersome, since (a) I need to do aggregate searches (select records giving priority to the locally modified copy), (b) I needed to move data from one table to another, which doesn't sound like a good practice, and (c) the schema is very complex (hundreds of columns) and maintaining the two tables schemas in sync was difficult.</p>
  
  <p>I then merged everything in one table, adding a status column (server/local).  This seemed fine, until I realised how complicated it was to filter the duplicate records (those for which both a server and local copy existed).  Counting, searching, selecting needed 10-line intricate queries (because of the limitations in sqlite) - see my other questions <a href="https://stackoverflow.com/questions/11913588/select-records-based-on-column-priority">here</a> and <a href="https://stackoverflow.com/questions/11914664/select-unique-record-based-on-column-value-priority">here</a>.</p>
  
  <p>I'm contemplating now keeping everything in one table, but ditching the status column, and creating a separate table for tracking statuses, one row per record, something like this:</p>
  
  <p><strong>Data:</strong></p>
  
  <pre><code>id    recordID  name                                 col2   col3   ...
  1     1001      Server record, not changed locally   xxxx   xxxx   ...
  2     1002      Server record changed locally        xxxx   xxxx   ...
  3     1002      Server record changed locally        xxxx   yyyy   ...
  4     1003      Record created locally               xxxx   xxxx   ...
  5     1004      Server record changed locally        xxxx   xxxx   ...
  6     1004      Server record changed locally        xxxx   yyyy   ...
  </code></pre>
  
  <p><strong>Status tracking:</strong></p>
  
  <pre><code>id    recordID  server  local
  1     1001      1
  2     1002      2       3
  3     1003              4
  4     1004      5       6
  </code></pre>
  
  <p>Aggregating the above information for display would mean showing the local record, if any, or the server record otherwise - in this case, data rows 1,3,4 and 6.  In this case, my queries would be simpler (just a join with a case).</p>
  
  <p>Would this be the best approach, or is there a better design I should use?</p>