Database table design for scheduling tasks

Question

I want to be able to create schedules that can be executed based on a fixed date, repeated daily, repeated on a particular day of the week, repeated on a particular month of the year, repeated on a particular date every year, and repeated at a particular time of the day.

Please how do i go about building the database tables for this problem?

Edit #1

Basically, i'm writing an application that allows users to schedule pre-configured greetings to be sent at various pre-configured times. I know i need a table that stores information about a schedule (ex. Christmas, Marketing One, ... | and when the schedule should run). Then another table to record what schedule has ran, what greeting it sent, to who, and what email; basically a transactions table.

My problem is designing the Schedule table because, i want to allow users run the schedule at a specific date, on a particular day of the week (recurring), on a particular day of every month, on a particular time everyday, and on a particular day/month (ex. 25/12) every year.

How can i create a set of tables for schedule that will take care of these inputs in flexible way?

Answer 1

To make your job easier, you could use an existing scheduler for the scheduling part. In Java there are for example Quartz or a library I wrote myself, db-scheduler.

db-scheduler is easily embeddable, and has out-of-the-box support for tasks on a recurring schedule (fixed duration, daily, etc). The execution-times are persisted in a single database table, so it survives restarts.

Answer 2

This is the table structure i came up with;

Schedule
 - ScheduleName
 - ScheduleTypeId (Daily, Weekly, Monthly, Yearly, Specific)
 - StartDate
 - IntervalInDays
 - Frequency
 - FrequencyCounter

ScheduleDaily
 - ScheduleDailyId 
 - ScheduleId
 - TimeOfDay
 - StartDate
 - EndDate

ScheduleMonthly
 - ScheduleMonthlyId
 - ScheduleId
 - DayOfMonth
 - StartDate
 - EndDate

ScheduleSpecific
 - ScheduleSpecificId
 - ScheduleId
 - SpecificDate
 - StartDate

...

ScheduleJob
 - ScheduleJobId
 - ScheduleId
 - ScheduleTypeId
 - RunDate
 - ScheduleStatusId

Answer 3

Since you are talking about schedules, I assume you want to build a batch application for management and execution of jobs.

You can check spring batch meta data design for a reference implementation. However, exact design would depend on your requirement. This is just a pointer.

Answer 4

Microsoft SQL Server has an efficient and flexible design: https://msdn.microsoft.com/en-us/library/ms178644.aspx?f=255&MSPPError=-2147217396