3

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.

enter image description here

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?

  • What do you mean by "lack of scalability if new tasks and rules added?" – Robert Harvey Aug 15 '16 at 16:34
  • I mean i have to change the schema and add new fields to table events to support new schedule task rules. – user968159 Aug 16 '16 at 8:12
  • You do have to add new table records. Why would the schema need to change? – Robert Harvey Aug 16 '16 at 14:37
  • Because new rules may required foreign keys not exist in the table. We just have new story that requires adding user role id to this table to send notification based on role id, so i will add this column to the table. – user968159 Aug 17 '16 at 2:40
  • If the number of rules is finite, consider using genericid and genericidtype fields that can relate to any arbitrary PK in any table. – Robert Harvey Aug 17 '16 at 4:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.