title: Sharing a table row between users in SqlServer Azure
body: <p>Context: A mobile note taking application that is connected to windows azure mobile services. (Sql Server Azure)</p>
  
  <p>Currently I have 2 tables: Users &amp; Notes.</p>
  
  <p>A user downloads their respective notes by querying the Notes table and asking for all notes that have the userID match their own.<br>
  Example:</p>
  
  <pre><code>SELECT * FROM Notes WHERE userID = myID;
  </code></pre>
  
  <p>But now I want my users to be able to share notes between them, so...</p>
  
  <p>I'm thinking of adding a "SharedList" &amp; "SharedListMember" tables, where each note will have a shared list with their respective sharing members on the SharedListMember child table.</p>
  
  <p>Example:</p>
  
  <pre><code>SELECT DISTINCT n.* FROM Notes n LEFT OUTER JOIN SharedList l ON n.list = l.id INNER JOIN SharedListMember lm ON l.id = lm.list WHERE (lm.memberID = myID OR n.userID = myID)
  </code></pre>
  
  <p>I have added a LEFT OUTER JOIN because not all tasks will be shared.</p>
  
  <p>I would be adding indexes on Notes.list, SharedList.id (Primary Key) , SharedListMember.memberID, SharedListMember.list</p>
  
  <p>Questions:
  How much performance impact can I expect with this setup ? Is there a faster way?
  I currently query about a 1000 notes in less than a second. What would happen if I've got 10 million notes ?</p>