Messages: Query a user's chats

Question

For an app like iOS Messages:

How should I design the server database queries & schema?

Queries
- Create a user.
- Get a user's chats.
- Get the messages between two users.
- Add a message to a chat (creating the chat if it doesn't yet exist).
Note:
- Every chat only has two users (no group chats).
- Every chat's id equals this unique unordered pairing function applied to user_id & with_id.
How should I query a user's chats, each with its latest message?

Example User Interface (UI)

See my design & query on SQLFiddle. It works, but how can I make it better?

Related: http://stackoverflow.com/questions/19651811/select-users-with-i-have-talked-and-last-message-with-them-like-whatsapp — ma11hew28, Mar 13 '14 at 06:21
Related: http://www.9lessons.info/2013/05/message-conversation-database-design.html — ma11hew28, Mar 13 '14 at 08:46
Related: http://stackoverflow.com/questions/21459900/how-to-build-this-sql-query-select-conversation-with-last-message — ma11hew28, Mar 13 '14 at 08:51
Related: http://stackoverflow.com/questions/12058902/sql-order-by-threads-messages — ma11hew28, Mar 13 '14 at 08:54
Related: http://stackoverflow.com/questions/9947172/sql-querying-for-threaded-messages — ma11hew28, Mar 13 '14 at 08:54
Related: http://stackoverflow.com/questions/18851962/mysql-query-for-thread-list-of-messages-with-specific-conditions — ma11hew28, Mar 13 '14 at 08:57
Related: http://stackoverflow.com/questions/19317123/query-to-display-the-only-the-most-recent-message-of-each-thread — ma11hew28, Mar 13 '14 at 08:57
Related: http://stackoverflow.com/questions/6261062/getting-the-most-recent-message-in-a-thread — ma11hew28, Mar 13 '14 at 09:01
Related: http://stackoverflow.com/questions/8873056/facebook-style-message-inbox — ma11hew28, Mar 13 '14 at 09:02
Related: http://stackoverflow.com/questions/6472201/mysql-group-by-order-by-issue-with-flat-messages-table-threads — ma11hew28, Mar 13 '14 at 09:03
Related: http://stackoverflow.com/questions/9919825/sql-need-a-query-to-get-most-recent-message-in-each-thread-that-a-user-is-part — ma11hew28, Mar 13 '14 at 09:05
Related: http://stackoverflow.com/questions/19077088/sql-show-last-messages-from-db — ma11hew28, Mar 13 '14 at 09:05
Related: http://stackoverflow.com/questions/12027160/sql-query-for-most-recent-messages — ma11hew28, Mar 13 '14 at 09:06
Related: http://stackoverflow.com/questions/20856416/how-to-get-the-latest-message-in-each-conversation-of-a-certain-user-in-sql — ma11hew28, Mar 13 '14 at 09:10
Related: http://stackoverflow.com/questions/19358767/get-last-message-in-a-conversation-in-sql — ma11hew28, Mar 13 '14 at 09:11
Related: http://stackoverflow.com/questions/11921204/creating-a-messages-list-in-sql — ma11hew28, Mar 13 '14 at 09:14
Related: http://stackoverflow.com/questions/17144447/sql-get-last-messages-from-to-certain-user — ma11hew28, Mar 13 '14 at 09:16
Related: http://stackoverflow.com/questions/16491967/getting-the-most-recent-date-after-using-group-by-with-mysql — ma11hew28, Mar 13 '14 at 09:17
Related: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group — ma11hew28, Mar 13 '14 at 09:18
Related: http://stackoverflow.com/questions/8276383/postgresql-join-to-most-recent-record-between-tables — ma11hew28, Mar 13 '14 at 09:23
Related: http://stackoverflow.com/questions/15596302/query-to-get-last-conversations-for-user-inbox — ma11hew28, Mar 13 '14 at 09:25
Related: http://stackoverflow.com/questions/21937686/select-distinct-last-messages-by-pairs-in-postgres — ma11hew28, Mar 13 '14 at 09:26
Related: http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — ma11hew28, Mar 13 '14 at 09:47

Messages: Query a user's chats

0 Answers0

Linked