1

I'm trying to build a very simple messaging system:

  • a list of conversation with shows the profile pic of my contac, the last message of that conversation and conversation not read will be simply displayed as 'non-read'
  • when entering a conversation I get the list of message

Here is how I have started designing my database table (I use MySQL)

CREATE TABLE conversation (
  id INT NOT NULL AUTO_INCREMENT,
  user_one_id INT NOT NULL,
  user_two_id INT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_u1_u2 (user_one_id, user_two_id),
  FOREIGN KEY (user_one_id) REFERENCES user(id) ON DELETE CASCADE,
  FOREIGN KEY (user_two_id) REFERENCES user(id) ON DELETE CASCADE
);

CREATE TABLE message (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  message TEXT NOT NULL,
  conversation_id INT NOT NULL,
  created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE, 
  FOREIGN KEY (conversation_id) REFERENCES conversation(id) ON DELETE CASCADE
);

Two table, one that store the conversation and an other that list all the messages (I haven't yet included the read feature)

Using these two tables I have written this query to get the list of conversation for the userid=1

select c.id, u.nickname, u.profile_picture, m.message, m.created
        FROM user u, conversation c, message m
        LEFT JOIN message m2
        ON (m.conversation_id = m2.conversation_id AND m.id < m2.id) -- this is to select last message
        WHERE
            CASE
              WHEN c.user_one_id = 1
              THEN c.user_two_id = u.id
              WHEN c.user_two_id = 1
              THEN c.user_one_id= u.id
            END
        AND
        c.id = m.conversation_id
        AND
        (c.user_one_id = 1 OR c.user_two_id = 1)
        AND
        m2.id IS NULL ORDER BY m.id DESC;

This will display the name of the person which the messages was sent/receive from, its profile picture and the last message of that conversation. It's quite a complicated query and don't really know it will perform when I will have millions of messages and conversations..

I would like to know if my tables design can be improved (which would maybe make my SQL queries easier/simpler)?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.