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.