Best practices for coding threaded multiple-recipient messaging system?

Question

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

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.
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!

I say squirrel chutes or air tubes are vastly underrated. :D — Jared Farrish
– Jared Farrish, Commented Apr 20, 2011 at 4:16
Why are you creating your own message queue in the first place? blog.fedecarg.com/2008/11/03/… — Wes
– Wes, Commented Apr 20, 2011 at 4:39
you're not talking about a system-system message queuing system are you? I interpret the requirement to be to build a system to support sending messages from one user to perhaps multiple users, including the ability to support threaded messages where messages are in response to other messages. Basically, he's building more of an email system than a traditional message queue, or at least that was my interpretation... — squawknull
– squawknull, Commented Apr 21, 2011 at 2:43

Jonathan Oliver · Accepted Answer · 2011-04-20 12:03:09Z

3

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.

answered Apr 20, 2011 at 12:03

Jonathan Oliver

5,27734 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

squawknull Over a year ago

I don't think he's going for pure fifo or similar system to system queuing, so a traditional MQ wouldn't be appropriate if that's the case. I hope @walker can clarify this.

Jonathan Oliver Over a year ago

It wasn't immediately clear if he wanted for some more like internal email messages or otherwise.

squawknull Over a year ago

I agree. Hopefully @walker will clarify his question if that's what he wants.

squawknull · Accepted Answer · 2011-04-20 04:20:47Z

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.

answered Apr 20, 2011 at 4:20

squawknull

5,2122 gold badges19 silver badges28 bronze badges

Collectives™ on Stack Overflow

Best practices for coding threaded multiple-recipient messaging system?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related