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