I am trying to create a basic internal messaging system. The only 'special' consideration is a user can send a message to multiple recipients and a receiver of a message can Reply To All (if applicable). This is what I currently have:
MESSAGE TABLE
- thread_id
- message_id
- subject
- content
- date_sent
- from (FK to user_id)
- to (FK to user_id(s))
READ TABLE
- message_id (FK)
- recipient (FK to user_id, limit one un-read message per thread)
- read (boolean)
Does this DB design seem sufficient for an internal messaging system, or is it lacking anything / any way to improve it?
It seems like there's a many-to-many relationship between messages and recipients. I would therefore factor that out into a join table; you may as well put the "read" flag on that join table.
That would give you something like:
MESSAGE TABLE
- thread_id
- message_id
- subject
- content
- date_sent
- from (FK to user_id)
MESSAGE_RECIPIENT TABLE
- message_id (FK)
- recipient (FK to user_id)
- read (boolean)
Your requirement to show only one unread message for a thread is a display issue, and I'd keep it out of the database. In your front-end code, you'd have logic to show the lesser of the actual number of unread messages and 1 when calculating the number of unread messages to show.
To find all recipients for a message (so you can do "reply all"), you need to join to the MESSAGE_RECIPIENT table, as follows:
select u.email_address
from users u,
message m,
message_recipient mr
where u.user_id = mr.recipient
and mr.message_id = m.message_id
and m.message_id = $whateverYourCurrentMessageIDis
(apologies for the archaic join syntax)