Storing chat messages inside a MySql table

Question

For the last couple of weeks I've been working on a web based chat client application and I have a question regarding storing chat messages inside a MySql table. I created a table called conversations and for now it consists of 5 fields;

user1ID, user2ID ,messages(mediumtext), status, timestamp.

When I test the chat application everything works perfectly fine, but the problem is every time a user sends something, I'm appending that value into my 'messages' field as a new line. And when it comes to retrieving the message, my sql code reads the whole thing and show it to the corresponding user. So the amount of data linearly increase by the amount of text added into the messages field. My question is, is there any way to SELECT only the last line from a text field or maybe another solution that will reduce the amount of transferred data.

Do you append your message to an existing field in the only row in a table??? O_O — Nemoden, Dec 02 '11 at 03:49

Tim G · Accepted Answer · 2016-05-06T14:47:07.797

You need a better db schema - more relational. Doing so will give you some other improvements as well (password protected chats and multi-user chat to name a couple)

Here is one take on an ERD for your db.

enter image description here

5/6/2016 edit Adding DDL with (hopefully) improved field types and names

CREATE TABLE user
(
    user_id CHAR(32),
    user_login VARCHAR(255),
    user_password CHAR(64),
    user_email VARCHAR(400),
    PRIMARY KEY (user_id)
);

CREATE TABLE message
(
    message_id CHAR(32),
    message_datetime DATETIME,
    message_text TEXT,
    message_chat_id CHAR(32),
    message_user_id CHAR(32),
    PRIMARY KEY (message_id)
);

CREATE TABLE user_chat
(
    user_chat_chat_id CHAR(32),
    user_chat_user_id CHAR(32),
    PRIMARY KEY (user_chat_chat_id,user_chat_user_id)
);

CREATE TABLE chat
(
    chat_id CHAR(32),
    chat_topic VARCHAR(32),
    chat_password CHAR(64),
    user_chat_user_id CHAR(32),
    PRIMARY KEY (chat_id)
);

CREATE INDEX user_login_idx ON user (user_login);
ALTER TABLE message ADD FOREIGN KEY message_chat_id_idxfk (message_chat_id) REFERENCES chat (chat_id);

ALTER TABLE message ADD FOREIGN KEY message_user_id_idxfk (message_user_id) REFERENCES user (user_id);

ALTER TABLE user_chat ADD FOREIGN KEY user_chat_user_id_idxfk (user_chat_user_id) REFERENCES user (user_id);

ALTER TABLE chat ADD FOREIGN KEY chat_id_idxfk (chat_id,user_chat_user_id) REFERENCES user_chat (user_chat_chat_id,user_chat_user_id);

There are a couple different ways to go about limiting the data transferred: 1. Limit the data to the last x minutes max. Or 2, send only message data since the last datetime you provide to the refresh script. — Tim G, Dec 02 '11 at 03:55
thanks for accepting my answer. ask more questions here if you need. :) — Tim G, Dec 02 '11 at 04:00
@TimG Maybe you already have a set of queries for this chat schema? It will be very useful. — Dmytro Medvid, May 06 '16 at 10:42
@DmytroMedvid - done. I updated the schema slightly with what I hope are improvements. The DDL should work fine in mySQL — Tim G, May 06 '16 at 14:48

score 4 · Answer 2 · answered Dec 02 '11 at 03:49

4

Why not have a table structure like this:

chats

chatID
user1ID
user2ID
startedDateTime
EndedDateTime

chatContent

chatContentID
chatID
message
dateTime
status

This way, your data is much easier to search and organize. For example, what if you wanted to get a certain message spoken at X time? or you want to get all chat messages with X status?

Seperating the data into 2 tables should be much nicer and neater.

answered Dec 02 '11 at 03:49

F21

32,163
26
99
170

Thanks a lot. I was trying to use a minimum number of tables and fields thats why I did it like this, but it seems like it wasn't a good solution – akk kur Dec 02 '11 at 03:56
It might be worth updating your schema to save you headaches down the track. – F21 Dec 02 '11 at 03:58
Actually you can get the same results if you combine the tables into one. The combined table will have a Message_id which is PK and AI and also will contain a field of Message_Sent_Timestamp and since you have a AI you will be able to know when was the first message was sent and this is of course the "startedDateTime". Same thing goes to "EndedDateTime" - the last row of Message_id will contain that date. The question you need to ask your self is "Is there a significant to a chat table ? if so, what is so important in a chat that I need it to be a table ?" – OhadM Jan 09 '16 at 11:37
hi, quick questions,1) in chats can i added status to see if admin has solved user issue if so admin can close the chat. 2) in chatContent, can i add seen, to see if user has seen all the messages admin has sent or not? – kd12345 Oct 13 '22 at 07:44

score 0 · Answer 3 · answered Dec 02 '11 at 03:49

0

Consider storing one message per row in the table:

id, user1id, user2id, message, status, timestamp

where id is an autoincrement column.

answered Dec 02 '11 at 03:49

Joshua Martell

7,074
2
30
37

Storing chat messages inside a MySql table

3 Answers3

Linked