Best practices for coding threaded multiple-recipient messaging system?

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 lightw开发者_如何学编程eight 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!

This sounds like a job for a true message queuing system such as RabbitMQ or any number of other message queues that are out there. There are large and complicated issues that must be fully understood and handled when attempting to code your own and these issues are not trivial. On a minimum, it's worth kicking the tires on one or two in order to get a feel for the power and capabilities offered.

Once you go down the message queue route, you'll want to look into a concept known as publish/subscribe which will keep each subscriber (or recipient) separate from one another. Following this, a database table (or even a completely separate database) can be created per subscriber if necessary.

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.