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)