对于简单的消息系统，我应该为发送和接收的消息创建数据库表，还是只创建一个表来处理这两个消息？

Question

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?

Answer 1

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.