sending "reminders" on regular intervals

[bcamp1973]

I'm trying to build an calendar app that stores reminders and sends them out based on user input.  for example, a user may wish to set a reminder for noon every weekday, or 9:00 AM on wednesdays, or the 2nd tuesday of each month.  i'm having trouble wrapping my head around this from a data model standpoint.  how do i store this data in a DB?  has anyone tackled this before? 

[shoz]

Using cron as the model you could create a table with "minute", "hour", "day of the month", "month" and "day of the week" fields.

You'd specify "any/every" with a value of your choosing. Possibly -1 or some other value.

 

The query could then be something similar to

SELECT ... WHERE min IN(-1, currentmin) AND hr IN(-1, currenthour) .....

[bcamp1973]

Thanks shoz!  That's something i hadn't even considered, probably because of my limited experience with cron among other things.  Anyway, although you've pointed me in the right direction, I'm still not quite connecting the dots Considering the above, say i create a recurring reminder for the 1st Tuesday of the month at 11:00 AM.  I'm guessing my INSERT would be like so?

 

INSERT INTO reminders (
   minute,hour,day_of_month,month,day_of_week
) VALUES (
   '0','11','-1','-1','3'
)

 

..ugh, as i try to write it i realize i have no clue...

[shoz]

say i create a recurring reminder for the 1st Tuesday of the month at 11:00 AM.  I'm guessing my INSERT would be like so?

 

INSERT INTO reminders (
   minute,hour,day_of_month,month,day_of_week
) VALUES (
   '0','11','-1','-1','3'
)

 

I don't believe there's a way using the standard cron options to make a reminder based on the occurrence of the day in the month. You'd have to create another column, say "occ_day_in_mn" and then do an insert similar to the following.

 

INSERT INTO
reminders
(min, hr, day_of_mn, mn, day_of_wk, occ_day_in_mn)
VALUES
(0,  11, -1, -1, 3, 1)

 

<?php
$date = '2007-07-22';
$occ = ceil(date('d', strtotime($date)) / 7);
print $occ;
?>

 

Note that Cron allows both "day of month" and "day of week" to be set, where the job will be run if either match. You can either write the SELECT to allow the same or have the options be mutually exclusive on input.

 

If you allow for a range of values you can store them as a list such as (1,2,3) and use FIND_IN_SET using the current table setup. This should be slow after the number of reminders reach a certain point. You could more efficiently create multiple tables eg: "hours(reminder_id, hr), min_table(reminder_id, min) ...."  and change the query to again, something similar to.

 

SELECT 
DISTINCT r.reminder
FROM
reminders AS r
INNER JOIN
hrs AS h
ON
r.rem_id = h.rem_id
INNER JOIN
min AS m
ON
r.rem_id = m.rem_id
.....
WHERE
h.hr IN (-1, ...) AND m.min IN (-1, ...)

 

If this is to be a simple reminder feature you may want to restrict the reminder to being sent once a day. You could have a hard coded time for when the reminder will be sent, have the user choose one hr or allow the user to choose both hr and minute for the day.I should note that the query may need to be optimized further.

 

Using Cron itself (Although I'm not sure how it will handle a significant number of jobs, I'd guess not very well) is one other option you may want to consider. If possible you could setup cronjobs that would pass the id of the reminder as an argument to the script that sends the reminders. To account for the "occurrences of the day in the month" you could have the script check a table that would store only that information.

[fenway]

Yeah, querying any such type of "reminder"-like table has always been a pain in the butt, and I've never really found a solution like I've liked, at least not in the general case... so I end up re-writing it time and time again with various client project simplifications in mind.

[bcamp1973]

shoz, thanks for the great explanation!  it's a big help.  I'm going to have to chew on this a while.  Initiallly this will be used on a low traffic site so if i restrict reminders to once a day it should be fine. However, having a more robust solution would be nice...but i think that will require something other than a PHP/cron solution from what I'm gathering. I don't know Pearl, but i'm guessing there might be some performance benefits there...or i could just hire a C developer and be done with it i suppose

[shoz]

shoz, thanks for the great explanation!  it's a big help.  I'm going to have to chew on this a while.  Initiallly this will be used on a low traffic site so if i restrict reminders to once a day it should be fine. However, having a more robust solution would be nice...but i think that will require something other than a PHP/cron solution from what I'm gathering. I don't know Pearl, but i'm guessing there might be some performance benefits there...or i could just hire a C developer and be done with it i suppose

 

I should note that my concern is mostly with the MYSQL side of things and then only after the number of reminders reach a significant number. One thing that comes to mind that you could implement at some time, is to load the reminders that should be sent for the day (with their scheduled minute and hour) into another table perhaps an hr before the next day begins. This table would still be updated if any more reminders are added to or deleted from the schedule for the day in question.

 

This should remove a large portion of the performance impact of having to deal with a large number of reminders. At some point if the reminders reach, or are anticipated on initial database creation to reach an even more significant number then the table(s) could be split or "loaded" in the same manner mentioned above per month and still use the "day loading" option.

 

These are my initial thoughts on how to make the system more robust. Note also that I say "significant" number because although I have my own guess as to what the number will be I don't want to give that as an actual figure. This reminder feature would have to be a very popular part of a very popular site to reach the numbers that I'm considering in the "month" loading scenario however.