CODEWITHSUNDEEP
sqlsql-serverdatabase-designazure
the Reverend
the Reverend

Reputation: 12559

Sharing a table row between users in SqlServer Azure

Context: A mobile note taking application that is connected to windows azure mobile services. (Sql Server Azure)

Currently I have 2 tables: Users & Notes.

A user downloads their respective notes by querying the Notes table and asking for all notes that have the userID match their own.
Example:

SELECT * FROM Notes WHERE userID = myID;

But now I want my users to be able to share notes between them, so...

I'm thinking of adding a "SharedList" & "SharedListMember" tables, where each note will have a shared list with their respective sharing members on the SharedListMember child table.

Example:

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)

I have added a LEFT OUTER JOIN because not all tasks will be shared.

I would be adding indexes on Notes.list, SharedList.id (Primary Key) , SharedListMember.memberID, SharedListMember.list

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 ?

Upvotes: 1

Views: 47

Answers (1)

Adam
Adam

Reputation: 16199

You will likely notice no impact on 10 million notes, with this SQL query.

Your bottlenecks will be bandwidth back to your app, if your notes ever contain attachments and latency with the SQL query call to the database, so cache locally if you can and do Async calls where practical in your application.

This is a case of don't try to over optimize a solution that isn't causing a problem. SQL Azure is highly optimized and I have millions of rows in some of my tables and queries return in less than a second and are far more complicated than the one you have shown above.

Upvotes: 1

Related Questions