westsorkin wrote:

> I am stuck with the database design of a kind of email messaging
> system. More precisely with the design of the read/unread messages
> feature. Note the system special feature: one message can have (and
> will have) multiple recipients. Thus I decided to put all messages in
> their own table, and organize them with the UserMessages table.
>
> Here is the current simplified design:
>
> User(ID, Name, etc),
> Messages(ID, TimeStamp, Subject, Body)
> UserMessages(ID, MessageID, UserID, IsRead)
>
> I will have the following simplfied query:
>
> select
> Message.id, Message.Subject
> from
> Messages
> inner join UserMessages on (UserMessages.MessageID = Messages.ID)
> where
> (:show_only_unread = 'YES' and UserMessages.IsRead = 'NO') or
> (:show_only_unread = 'NO')
>

I'd go with the above design, but with a diferent approach to query the
messages table.

I would create (on the fly ?) 2 distinct queries:

select
Message.id, Message.Subject
from
Messages
inner join UserMessages on (UserMessages.MessageID = Messages.ID)
where
UserMessages.UserId = :UserID and
UserMessages.IsRead = 'NO'

for the unread messages and

select
Message.id, Message.Subject
from
Messages
inner join UserMessages on (UserMessages.MessageID = Messages.ID)
where
UserMessages.UserId = :UserID

for all messages.

The FK Index on UserID will help on narrowing down the result set

I would create an index on Message.TimeStamp and Message.Sender (I believe you have this field) and probably on Message.Subject if the front end provides search by subject.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br