So I have chat functionality for my app. Right now It is just one global room, all messages get loaded.
I want to create seperate chat rooms and I want to store the chat history efficiently. If I have thousands or tens of thousands of new messages being sent daily and they are all being stored into one table it seems crazy inefficient to have each user have to search through ALL of those messages every time they open a chat...
I have read about a few technies. The coolest one is having 2 tables.
One that stores the 2 (or more) users IDs and then a count of total messages sent.
The second table has all the messages but is grouped by the 2 User ID combo. And you just find that cell and then read the message data only to do with all the cells of the same name. This is the article that I found that presents that idea. http://qnimate.com/database-design-for-storing-chat-messages/ Someone in the comments said that they achieved the same thing but with just one table, is that better?
But is that the best way? And if it is, any tips on how to pull that off with Azure Mobile App and Xamarin Forms? I think I can figure it out, but pointers are always welcome!