I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation. I think about next tables
User id ... Conversation id user_id1 user_id2 cnt_new_msg last_msg_id Message id conversation_id sender_id type is_read created_at
For selecting data for list of conversation I can use next query:
SELECT * FROM conversation c LEFT JOIN message m ON m.id = c.last_msg_id l.last_ms WHERE user_id1=[current_user_id] OR user_id2=[current_user_id]
When user send new message I will increment
Conversation.cnt_new_msg and when user open conversation I will set
Message.is_read to 1 and
recalculate Conversation.cnt_new_msg again. Also to sync this value I can create cronjob.
But this approach has problems. I don't like
user_id1=[current_user_id] OR user_id2=[current_user_id] for each query. My tables will be very denormalized but I think it will be faster.
Please advise best practices to design conversations tables.