I am creating schedule tasks that run on daily basis monitor system events and send email notification based on some criteria. For example, if user subscription will expire after one month, task scheduler will detect that by comparing dates then send email notification. There are many tasks listen to many events occurred in the system, most of them check for dates.
i want to make sure of not sending this email next day, because the condition still valid and it will be valid every day until (eg user upgrade his subscription).
I am looking for a design that prevent task scheduler from sending email agian if already sent.
My approach is to create table Event that contains foreign keys from various tables and to check if combinations of keys with specific event type is already exist and is notified. if so, i will not notify it again.
With this design , task one will check eventType = 'subscription_expire' and companyId =[id] and seriviceid=[id] if already exist.
While task two will check eventTyp='notPaidInvoice' and productId = [id] and companyId = [id] already exist or not.
I don't like the design because of many Foreign keys involved and lack of scalability if new tasks and rules added.
Is there standard or popular approach to handle these situation?