Creating a threaded private messaging system like facebook and gmail

Viewed 26332

I am creating a threaded message system much like gmail and facebook where the inbox lists the most recent threads displaying the subject, the sender's name, and time-stamp of the most recent message.

Here's How my tables are set up:

users:
    user_id
    user_name

thread:
    thread_id
    title
    to_id
    to_keep
    to_read
    from_id
    from_keep
    date

message:
    message_id
    thread_id
    to_id
    from_id
    message_text
    date

What I'm doing right now is when a user creates a new message, it creates a new thread in the thread table and then a new message in the message table and if a user responds to a thread, it duplicates the current thread in the thread table except it swaps the to_id and from_id and then creates a new message based on that.

Also, for the inbox view, I'm able to just query all threads based on a user_id. so something like SELECT * FROM thread WHERE to_id = 2 and to_keep = TRUE ORDER BY date DESC or if I want to view messages in the outbox it would be something like SELECT * FROM thread WHERE from_id = 2 and from_keep = TRUE ORDER BY date DESC.

If a user opens a thread when there's a new message, then to_read is updated to true UPDATE thread SET to_read = TRUE WHERE thread_id = 4.

I feel like I'm over complicating this process and that there should be a better way to do this.

Any help or ideas would be appreciated.

This way let's me just select everything from the thread table and then do a join with the user table to display everything I need. However I feel like there should be a better way to do this.

1 Answers
Related