Facebook Style Messages: Efficient Query

users tbl:
id
username
first_name
last_name
email_address

messages tbl:
id
date_sent
title
content
status

message_relation tbl:
id
message_id
sender_id
receiver_id

What would be the most efficient way to query a message TO a selected user given these tables? In other words, I want to list all messages that are in userA's "inbox"

Secondly, how would you recommen开发者_如何学JAVAd handling global messages that need to be sent to everyone from the admin?

---

I think this is what you're looking for..

SELECT [WHATEVER] FROM 
messages
INNER JOIN message_relation
 ON (messages.id = message_relation.message_id AND message_relation.receiver_id = $id)

This does what you asked for efficiently. If you also want to select fields on either of the users, you can JOIN Receiver_ID to User_ID to to get that.

Suggested tables setup if you want Global/Admin messages:

Messages (Message_ID, [Fields common to all messages, e.g. Message_Content, Message_Timestamp, etc])
Global_Messages (Global_Message_ID, Message_ID, [any fields specific to Global_Messages])
User_Messages (User_Message_ID, Message_ID, [any fields specific to User_Messages])
User_Message_Relations (User_Message_Relations_ID, User_Message_ID, Sender_ID, Receiver_ID)

Then, to query an Inbox, something like:

SELECT [WHATEVER] FROM
Messages

LEFT JOIN (Global_Messages)
 ON (Messages.Message_ID = Global_Messages.Message_ID)

LEFT JOIN (User_Messages)
 ON (Messages.Message_ID = User_Messages.Message_ID)

LEFT JOIN (User_Message_Relations)
 ON (User_Messages.User_Message_ID = User_Message_Relations.User_Message_ID AND User_Message_Relations.Receiver_ID = $uid)

That will give you a result you can loop through to get a user's entire Inbox.

---

SELECT 
    m.id, m.date_sent,m.title,m.content,m.status
FROM 
    messages m
INNER JOIN 
    message_relation mr on (mr.message_id = m.id)
INNER JOIN
    users u on (u.id = mr.receiver_id);
WHERE u.id = ...your current user id ...

This is a simple way of doing it, and it will be fast and almost no load on your database