编码线程多收件人邮件系统的最佳实践？

Question

I've been tearing my hair out trying to figure out how to code a messaging system capable of the following:

1. Multiple recipients, where the recipients can not see other recipients, but only the sender. In the sent messages, the sender sees messages sent to multiple recipients in one thread, rather than in a billion separate threads.
2. Relatively lightweight queries (we have relatively 20,000 users - about 10% of which regularly send messages to 200-300 people at once).

Our current system is incredibly inefficient, and the queries are killing our servers with excessive joins. I'm looking for conceptual advice on crafting a scalable threaded messaging system in PHP and MySQL - what sort of database structure is best, how to store recipients, best practices for queries. Can anyone help?!

Thanks!

Answer 1

It seems like the place to start would simply be three tables, a recipient table, a message table and a message_recipient many to many mapping table. Something like this:

recipient

• recipient_id
• name
• email

message

• message_id
• message_text

message_recipient

• message_id
• recipient_id

Be sure to creat an index on both fields of the message_recipient table, and then it becomes the focal point for all queries for messages.

Have you tried anything like that approach? It's the simplest and most straightforward. If that doesn't work, it can probably be tuned.