3

I'd like to ask what, in your view, would be one preferable and robust software design for implementing continuous processing of records in a time-aware manner. A problem overview of the problem to solve follows:

There are records added to the database. Each record is an event with signed up participants. Each event is scheduled to take place on a particular day at a particular hour. Each participant should receive a notification 1 hour before the event's scheduled start time (which is also saved in the database). Events are added to the database all the time. Each participant can take part in many events.

I'm particularly interested in solving this requirement: "Each participant should receive a notification 1 hour before the event's scheduled start time (which is also saved in the database)."

What would be a good software design approach to accomplish this business requirement.

I personally considered the following:

  1. The database is being polled for the latest event start times at regular intervals (every 5 minutes) - something like (simplified SQL): SELECT participant FROM events WHERE event.start_time > NOW - 10 minutes AND participant.was_notified = 0
  2. Each participant is then passed to the queuing system like, e.g. RabbitMQ
  3. A queue worker process sends notifications to participants selected in step 1 above
  4. A queue worker process marks the participants as "having received notification" (participant.was_notified = 1)
  5. The cycle goes again

The approach I described above would probably work to some extent, but it has lots of drawbacks and does not seem very accurate. Some problems that I can think of:

  • The notification are not very time-accurate. 10 minutes interval mean that a participant can receive a notification at 11:55 instead of at 12:00,
  • There has to be a guarantee that a notification was delivered and that a record published to the queue has been processed,
  • There might be no notifications to deliver within another several hours, but the database will still be polled every 5 minutes.

I'm sure there are more problems with this approach, but I just wanted to list some I could think about off the top of my head.

So coming back to the main question - what would be a preferred, production-suitable software design pattern to solve the presented problem?

Improve this question
6
  • 1
    Polling every minute is fine (assuming you have a non-trivial number of participants) which will solve your accuracy problem. It's just a single query instance, and if the number of participants is substantial, ordinary OLTP activity will dwarf your measly one query per minute. (it's actually two queries; one to get the current list of people to be notified, and another to clear the flags for those notifications).
    – Robert Harvey
    May 27, 2017 at 20:44
  • If you have a separate queueing system it seems very strange for the database to even be aware of events
    – candied_orange
    May 27, 2017 at 22:54
  • @CandiedOrange: Well, you do have to store the schedule somewhere.
    – Robert Harvey
    May 28, 2017 at 3:01
  • @RobertHarvey, thanks for your comment. Based on it, I conclude that the rough scheme I presented in the question can be made into a solution suitable for production use in high traffic volume systems. There of course be an array of edge cases to consider, but at the moment, I'm after a general direction that could be adopted to solve a similar problem.
    – luqo33
    May 28, 2017 at 9:41
  • One query per minute is still inconsequential, unless you're running the thing on an Arduino. Even then...
    – Robert Harvey
    May 29, 2017 at 1:23

1 Answer 1

Reset to default
2

I'd consider scheduling instead. Whenever an event is created, you can tell a scheduling service (which is not coupled with the event creation service) to notify all participants of that event to be notified at some hour.

Apart from problems you listed, there is also one with sharing one db/table for two services and bottlenecking whole system with a single poller.

Improve this answer
2
  • This solution is also good. It would be worth noting that it will require more "syncing" code to keep false notifications from being sent. (Such as when an event is "canceled")
    – Caleb
    Aug 26, 2017 at 20:51
  • With this solution I would have the scheduling service request the participants from the source when it is time to send the notifications, to reduce the need to sync.
    – Kwebble
    Aug 26, 2017 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.