I'm trying to produce a fundamental internal texting system. The only real 'special' consideration is really a user can send a note to multiple readers along with a receiver of the message can Answer All (if relevant). This is exactly what I presently 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)
Performs this DB design appear sufficient to have an internal texting system, or perhaps is it missing anything / in whatever way to enhance it? Thanks.
user user_id message thread_id (surrogate id to grant easy access to whole conversation) message_id parent_message_id (message replied on can be used to show replies in tree-view form. FK to message) subject contect date from_id (FK to user) message_user message_id (FK to message) user_id (FK to user_id) status (readed, deleted, new....)
"Limit one unread message" ought to be handled by query getting count thread_id with status='new'.
It appears like there is a many-to-many relationship between messages and readers. I'd therefore component that out right into a join table you might as well place the "read" flag on that join table.
That will provide you with something similar to:
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 exhibit just one unread message for any thread is really a display problem, and I'd ensure that it stays from the database. Inside your front-finish code, you'd have logic to exhibit the lesser from the actual quantity of unread messages and 1 when calculating the amount of unread messages to exhibit.
To locate all readers for any message (so that you can do "reply all"), you have to join towards the MESSAGE_RECIPIENT table, the following:
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 that archaic join syntax)