This sounds like a pretty simple task. First of all, you need a database to store your messages; presumably you already have one for your user accounts, but if you want recommendations, MySQL, PostgreSQL and SQLite are all common choices and should do fine.
Next, you'll need a table to store the messages. A typical message table should probably contain at least the following columns:
- Message ID, the primary key to the table. This can be just an auto-incrementing number.
- A timestamp of when the message was sent; useful for chronological sorting. Create an index on this column. (In fact, if you frequently sort messages by this columns, you may also want to include it as the last column of any other indexes.)
- The sender of the message; a user ID. Also index this column (or mark it as a foreign key into your user table, if your DB supports those).
- A parent message ID, for threading. May be
NULL. Should be indexed as a foreign key to this table itself.
- The title / subject of the message; a
VARCHAR type should be good.
- The body of the message, whatever variable character type your DB has that lets you store at least several kilobytes of text.
Obviously, that's just a bare-bones sketch of a message table. You might want to add some other fields, like a last edit timestamp if messages can be edited after sending. Also, some of the fields above could be left out if the information is stored in some other way; for example, the sender could instead be stored in the same table as the recipients (see below), and it could be more efficient to store the message body in a separate table as a form of vertical partitioning.
Now we also need to store the recipients. Since a single message might have several of those, we need a separate link table, with columns like this:
- Message ID: a foreign key to the message table.
- Recipient ID: a foreign key to the user table.
Make sure both of these columns are indexed, so that finding both the recipients of a given message and the messages sent to a given recipient will be efficient.
That's basically enough, but you might also want to add a few more columns. For example, this table could conveniently store a flag indicating whether or not the recipient has seen the message. Also, if you added a "role" column, you could use it to indicate different types of recipients (maybe some only got "carbon copies"?), or even use it to store the sender of the message, and any other possibly associated users, in this same table.
Note that you don't really need a separate ID column for this table: the first two columns above (and possibly the role column, if any) already make up a natural primary key together.
Here's what the definitions of these tables might look like in SQL. I've written these for MySQL since it's what I'm most familiar with, but they shouldn't be hard to adapt for other DBs:
-- here's our basic message table
CREATE TABLE message (
message_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
sent TIMESTAMP NOT NULL,
sender INTEGER NOT NULL, -- foreign key to user table (not shown)
parent_id INTEGER NULL, -- optional foreign key to this table
title VARCHAR(255) NOT NULL
);
-- timestamp index for chronological sorting, also appended to other indices
CREATE INDEX i_sent ON message (sent);
-- foreign key indices (because MySQL doesn't really have true foreign keys)
CREATE INDEX i_sender_sent ON message (sender, sent);
CREATE INDEX i_parent_sent ON message (parent, sent);
-- optional, for sorting messages by title if that's a common use case
CREATE INDEX i_title ON message (title);
-- let's make a separate table for the body, just to show how it's done
CREATE TABLE message_body (
message_id INTEGER NOT NULL PRIMARY KEY, -- also foreign key to msg table
body TEXT NOT NULL
);
-- and finally, the recipient table
CREATE TABLE recipient (
message_id INTEGER NOT NULL, -- foreign key to message table
rcpt INTEGER NOT NULL, -- foreign key to user table (not shown)
seen TINYINT NOT NULL DEFAULT 0, -- MySQL doesn't have a real boolean type
PRIMARY KEY (message_id, user_id)
);
-- an optional index for quickly finding unread messages
CREATE INDEX i_rcpt_seen_msg ON recipient (rcpt, seen, message_id);
Using these tables, here are some examples of common queries:
A user's outbox, newest first:
SELECT * FROM message
WHERE sender = :sender
ORDER BY sent DESC
A user's inbox, newest first:
SELECT * FROM message
JOIN recipient USING (message_id)
WHERE rcpt = :recipient
ORDER BY sent DESC
The number of unread messages to a user:
SELECT COUNT(*) FROM recipient
WHERE rcpt = :recipient
AND seen = 0
One or more full messages with body and comma-separated recipient list:
SELECT
message.*,
message_body.body AS body,
GROUP_CONCAT(recipient.rcpt) AS recipients
FROM message
LEFT JOIN message_body USING (message_id)
LEFT JOIN recipient USING (message_id)
WHERE message_id IN (:message1, :message2, :message3)
GROUP BY message_id
Replies to a message (iterate to find replies to replies, etc.):
SELECT * FROM message
WHERE parent = :message_id
ORDER BY sent ASC
(In all the examples above, identifiers beginning with : denote placeholders to be substituted with (or bound to) values from the client.)
Also note that there are several other ways of storing hierarchical data like threaded messages in a database which avoid the need for iterated queries at the cost of some complexity. (Also, some database engines provide built-in support for recursive queries.) However, if you don't mind occasionally doing multiple queries in a loop to fetch a set of results, a simple parent message ID column like I used above will do the job just fine.
