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.

Answer 1

So here is another schema.I would describe a purpose as the following:

• Well, like in Facebook. A user can belong to many groups, also a group can belong to many users too.

• On table user_group_chat, I make the column user_id, group_id and created_at as a PRIMARY KEY. The column user_id is also a FOREIGN KEY of table users but on DELETE will be CASCADE, and group_id is also a FOREIGN KEY of table groups and on DELETE will be CASCADE too. And the reason for making those as FOREIGN KEY are simple. If a user or a group is not exists, they should be gone too.
• Also on table user_user_chat, I make the column from_user_id, to_user_id and created_at as a PRIMARY KEY too. In a nutshell, it's behavior is the same as table user_group_chat too. It's just this table is responsible for storing a chat between a user and a user.

Answer 2

Consider storing one message per row in the table:

id, user1id, user2id, message, status, timestamp

where id is an autoincrement column.

Answer 3

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.

---

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);

Answer 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.