Best way to store chat messages in a database? [closed]

Question

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 1

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

Answer 2

If we assume that you do not read the data too.

This sounds to me like an audit\logging requirement, if it is, you do not need a database to store the chat messages.

Just append the conversation to a text file (1 file per day?). The file could look like this:

chat-1-bob 201101011029, hi chat-1-jen 201101011030, how are you? chat-1-bob 201101011030, fine thanks.     chat-1-jen 201101011035, have you spoken to bill recently?     chat-2-bob 201101021200, hi chat-2-bill 201101021201, Hey Bob, chat-2-bill 201101021203, what time do you call this? chat-2-bob 201101021222, about 12:22 

I think you will find it hard to get a more simple scaleable audit solution.

If your requirements change and you need to search\edit\delete then a database would be more appropriate.