I want to create a simple Messaging System on my LAMP site. Not an instant messaging System, but an asyncronous messaging system, like a very simple form of e-mail.
In the Database, should I:
A) create one table for the Inbox (receiver) and one table for Sent (sender), so that when a receiver deletes the message from their Inbox, the sender can still see it in their Sent folder,
or
B) create just one table, and then add a column for whether the Receiver/Sender has deleted it or not and then display it accordingly to each user based on that?
I would also like to keep track of whether the receiver has read it and whether they have replied to it or not.
Option B seems more efficient since you are not essentially duplicating a table, but I'm wondering if there are any potential issues with that method that I'm not thinking about.
So, which option do you recommend, A), B) or something else, and why?
I would create it as a single table with something like:
ID
FromID
ToID
Message
An additional table that linked users to messages would be used to determine who has read what (the sender automatically being linked to that message, for example).
UserID
MessageID
Essentially, if a user has read a message, they'll have an entry in this table.
Doing it this way gives us a single view for all users. Someone's inbox is basically any message in the table where they are the recipient, and there sent box is any message where they are the sender.
My InBox:
SELECT * FROM Messages WHERE FromID = @MyID
My OutBox:
SELECT * FROM Messages WHERE ToID = @MyID
My Unread messages:
SELECT * FROM Messages
WHERE ToID = @MyID
AND ID NOT IN (SELECT MessageID FROM MessagesRead WHERE UserID = @MyID)
This is far simpler, imho than trying to use two tables that are basically doing the same thing. And you'd be replicating the From and To in both tables, but in reverse and we'd have 2 copies of the message floating around when we only needed one. Using an additional MessagesRead table allows us to track who has read what.
At a very quick first thought I would be tempted to have a "Message" table, and then have a link table between a message and user. That way, you can always have multiple receivers at a later stage if you want - think group chat.
This link table enter can be deleted (or delete flagged) to prevent the user seeing it in their inbox.
Hope that makes sense.
By "link table" I mean a table that effectively provides a many-to-many relationship between a message and a user
Usually you have one table, a sender_id column and a recipient_id column.
With deletion, you'd need a "soft delete" facility so the message remained in the other person's message centre as you mentioned. For this, you could have a deleted_messages table that then has a foreign key on message_id, deleting_user_id, and you can also store other information like a timestamp so you know when a user deleted a message. Then when you're displaying a user's inbox (or sent items), you'd just select all messages from your messages table, minus any that appear in your deleted_messages table.