Best way to store chat messages in a database?
I'm building a chat app and I want a full history off all messages ever sent in the chat conversation. At the moment I am storing each message as a single row in a table called 'messages'. I am aware that this table could grow huge as even small messages like 'Hi' would have their own database record.
Can anyone recommend a more scalable mysql solution? I don't require the individual messages to be searchable, editable or deletable. Could the whole conversation be stored in one huge field?
Would love to hear your ideas!
Answer
There's nothing wrong with saving the whole history in the database, they are prepared for that kind of tasks.
Actually you can find here in Stack Overflow a link to an example schema for a chat: example
If you are still worried for the size, you could apply some optimizations to group messages, like adding a buffer to your application that you only push after some time (like 1 minute or so); that way you would avoid having only 1 line messages