Saving chat messages inside the MySql table

Question

Over the past two weeks, I have been working on a web chat client application, and I have a question regarding storing chat messages in a MySql table. I created a table called chains, and now it consists of 5 fields:

user1ID, user2ID, messages (middle text), status, timestamp.

When I test the chat application everything works fine, but the problem is that every time the user sends something, I add this value to the "message" field as a new line. And when it comes to receiving the message, my sql code reads all this and shows it to the appropriate user. Thus, the amount of data increases linearly by the amount of text added to the message field. My question is whether there is a SELECT method only in the last line of the text field, or perhaps another solution that will reduce the amount of data transferred.

Answer 1

You need a better db scheme - more relational. It will also give you some other improvements (password protected passwords and multi-user chat to name a couple)

Here is one example of ERD for your db.

---

6/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 2

Why not create a table structure like this:

chat rooms

• chatID
• user1ID
• user2ID
• startedDateTime
• EndedDateTime

chatContent

• chatContentID
• chatID
• message
• Datetime
• Status

Thus, your data is much easier to search and organize. For example, what if you want to receive a specific message saying X times? or do you want to receive all chat messages with status X?

Dividing data into 2 tables should be much better and neat.

Answer 3

Consider saving a single message in a row in a table:

id, user1id, user2id, message, status, timestamp

where id is the auto-increment column.