Schedule Task prevent duplicate notification

Question

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?

Answer 1

Another option would be to figure out a common set of fields for all events. For example it could be {id, eventType, occurenceDate}. Then you can create a table with these three columns and with one more text column evendData that would contain JSON or XML with serialized event parameters. Also, you can have one more column which would contain a HASH of that JSON or XML. That column can be indexed and used for quick search.

The benefit of this approach:

1. You can flexibly add new events.
2. You don't need to maintain a lot of NULL columns for events where those columns are not used.
3. Fast search by HASH to check if event with the same parameters has been executed. Only one index is enough to search for an event.

Few downsides that I can think of:

1. If you are adding a new parameter to the existing event, you need to update previous records with new JSON/XML and recalculate HASH. But I guess you would have to do the same with your current design.
2. You need to make sure that your serialization of parameters returns consistent result every time you serialize the same parameters, so the HASH is the same all the time for the same set of parameters.
3. No foreign key constraints check, though it might not be an issue.