Sending message - Schedule hourly, daily, weekly

Question

i need some help on logic. I have an app which sends SMS. SMS to send is inserted by user (.NET MVC app), there is a table in DB.

Table outputSMS:

• Id (identity)
• Receiver (string)
• Message (string)
• DateTime2Send (DateTime)
• Sent (bool)

My scheduled app is a Windows service (used Topshelf), it is checking each 10 seconds the table for new rows WHERE field Sent is not TRUE and DateTime.Now-DateTime2Send=<0, and sends. Everything is find for messages inserted by user directly into table.

Now I need to implement schedule which sends SMS by interval - each 30 minutes, Daily, Weekly, monthly. For That I created a table SMSschedule:

• Id (identity)
• Receiver (string)
• ScheduleTypeId(int, FKey, values 30 minutes, Daily, Weekly, monthly)
• Date2Send (int)
• Time2Send (DateTime)
• Message (string)

There may be 1+ messages scheduled to each receiver. I think to make another schedule to check this table each 10 (or 30) minutes and create data in first table outputSMS (Ohh, i need to add field to identify Scheduled or inserted by user). I want to check it by this interval, because user can any time make changes on schedule. Number of receivers will be max 20k, and for each receiver may be several schedules.

Receiver SMS_ScheduleType SMS_DateToSend SMS_DayOfWeekToSend SMS_TimeToSend SMS_TextToSend 
Rec-1   daily   0   0   20:00   Evening
Rec-2   daily   0   0   6:00    Morning
Rec-3   hourly  0   0   0   INFO hourly
Rec-4   weekly  0   3   15:30   TEST weekly
Rec-4   Monthly 23  0   10:00   TEST monthly

for Hourly should i add one more field to set what time to send? For Time2Send field step must be 0.5 hour, that is, 30 minutes. What do you suggest? DB is Postgres, which can be shifted to Oracle.

Answer 1

From the look of things I think you should move the implementation detail to the code, maybe create 3 or 4 functions that scans the table "OutPut". Each function will have a specific time interval to scan for. Now your logic in each function will now be something like WHERE field Sent is not TRUE and DateTime.Now-DateTime2Send=<0 and Time2Send <= 'time_to_send'.

Or why not use a Scheduler like hangfire, which will do all the house keeping for you and do the work in a specified time interval (i.e: Send your sms for you)