Building a Message Delivery Queue in PHP

Question

I'm building a system that will send verses of scripture to subscribers over e-mail. Subscribers will be able to:

• Determine what volume of scripture they want to receive verses from
• Determine the daily frequency of the messages and how many verses will be included in each message
• Determine which times of day they want to receive the messages
• Keep track of their progress (so they can read a book over a period of time)

I'm having a really hard time wrapping my head around the data model for this problem. Particularly, I am struggling with how I am going to go about building and sending the messages to them.

So far I have come up with a subscription table, which will describe the user's preferences for content, frequency, and amount of content delivered per message, but I don't know exactly how to model storing the times they want the content delivered.

I'm thinking a cron job will run every hour that will generate messages that need to be sent and put them in a message_queue table. Another cron job will run and burn through the message_queue table and send the messages when the time is right.

Any ideas on how I can more efficiently model and build this system?

Here's my current data model:

Data Model http://www.kirkouimet.com/files/images/sendmescripture.gif

• ithcy: Can I ask what tool you used to build the data model? It looks pretty slick.
• chelmertz: @ithcy: MySQL Workbench, it's free.

Answer 1

You're pretty much on the right track.

Your proposed solution should scale pretty easily if you ever need to add additional boxes. You could easily segment your user base, and have each of N boxes handle 1/N users. Preference and scripture data could either be read from a central DB, or replicated read-only out to the slaves. Slaves would maintain their own queue, potentially sending some kind of summarized status data back to the master system. Of course, you may find that everything runs fine from one box, and your first step to scale is to do some kind of round-robin to various SMTP servers. I imagine that the sendmail (or equivalent) queue could easily end up being your biggest, earliest, bottleneck.

I'd go ahead and build things out as you've planned, then very carefully run some tests to figure out where things get hung up.

• Kirk Ouimet: Got it up and running! www.sendmescripture.com