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?

luqo33
  • 141
  • 3
  • 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 '17 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 '17 at 22:54
  • @CandiedOrange: Well, you do have to store the schedule *somewhere.* – Robert Harvey May 28 '17 at 03: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 '17 at 09:41
  • One query per minute is still inconsequential, unless you're running the thing on an Arduino. Even then... – Robert Harvey May 29 '17 at 01:23
  • I hope everyone involved has the same view on what _There has to be a guarantee that a notification was delivered_ means. – Kwebble Aug 26 '17 at 21:27

1 Answers1

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.

Artur Wierzbicki
  • 21
  • 1
  • 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 '17 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 '17 at 21:25