title: Android SQLite and server database 2 way sync offline
body: <p>My database structure is: <br><br> <a href="https://i.stack.imgur.com/pRtGf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pRtGf.png" alt="enter image description here"></a></p>
  
  <p><br></p>
  
  <p>I want to sync it both ways between my android <code>SQLite</code> and server using a <code>sync adapter</code>.</p>
  
  <p>The android application can perform all <code>CRUD</code> operations and the website can do the same thing.</p>
  
  <p>The <code>website</code> will use the <code>server</code> database. Currently I use <code>PostgreSQL</code> but it can be changed.</p>
  
  <p>The <code>mobile app</code> will use its own <code>local</code> database.</p>
  
  <p>How can I sync them? The big problem is with the <code>primary key</code>.</p>
  
  <ol>
  <li><p>One of the ideas is to use <code>uuid</code> as primary keys but there are performance issues, because as you can see there are some join I need to perform to get from the user to other tables.</p></li>
  <li><p>Another idea is to leave the <code>primary key</code> as <code>auto increment integers</code> and use <code>uuid</code> columns only for syncing. But this adds a new table column for each <code>primary</code> and <code>foreign key</code> in each table.</p></li>
  <li><p>Last one is to use a <code>compound key</code> as primary. This one will include 3 columns <code>user_id</code>, <code>id</code> (origin database id) and <code>device_id</code> (website / mobile app etc). And the same thing for each foreign key.</p></li>
  </ol>
  
  <p>Are there any better solutions ? If no which of mine solutions should I use ?</p>
  
  <p>How does Gmail, Keep, Dropbox or other applications performs this kind of task?</p>
  
  <p><br>
  Thank you.</p>