Remind customers of an event 7 days before the event based on a date

Question

Scratching my head on something for sometime now and I can't seem to resolve it through MySQL. I have a table that has records of events. I want to remind customers of an event every month, 7 days before it's due date. I have 3 tables called customers, events and event_reminders.

To describe what these tables do:

Customers table - a list of customers Events table - when the event was created and the day the reminder is set for Event Reminders table - A list of date/time for when the reminder was sent out

My thought process is that based on the "reminder_day" field ( 1st, 2nd, 3rd, 30th) you create a custom date, passing in the field called "reminder_day". You then do an interval of -7 days and check if this exists in the event_reminders table for every month. My SQL statement so far is below:

SELECT c.customer_name, c.customer_email, REPLACE(e.reminder_day, RIGHT(e.reminder_day,2), '') as due_day
FROM customers c JOIN events e ON (c.customer_id = e.customer_id)
WHERE NOT EXISTS 
(
 SELECT 1 FROM event_reminders r 
 WHERE MONTH(r.reminder_date) != MONTH(CURRENT_DATE()) 
)

The query above is checking the event_reminders table, to check if the current month record exists for an event of a customer but I need it to do a check on the due_day alias with an interval of -7 days.

I hope some genius could help me and it is clearly defined on my end. Much appreciated!

Answer 1

A database is a place to store data. An application is where that data is manipulated.

MySQL cannot "send email" or otherwise reach outside of itself.

So... I would have a daily 'cron' job in the OS open the "events" table, read all the rows, compute who needs a reminder, send out the emails, and remove any expired rows from the table.

If you have only thousands of entries in the table, this is quite manageable; if you have millions, then there needs to be some way for the SELECT to filter out most of the table, thereby lightening the load on the app.

To accommodate February, you might want to say 'last' instead of '30th'. That can be computed as "go forward 1 month, then back up 1 day". Or, in MySQL, date + INTERVAL 1 MONTH - INTERVAL 1 DAY.

Your definition of Event Reminders table is confusing -- is it a list of future reminders to issue? Or a list of past reminders sent out. Pick one. You seem to need to remember that you have already sent out a reminder; so I prefer that. Pre-computing the 'future' is not worth the effort (as I already pointed out).