30

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?

Orson
  • 14,981
  • 11
  • 56
  • 70
  • 2
    Can't you use an in-built scheduler? Most RDBMS have these and you don't have to re-invent the wheel then. – Ben Aug 23 '12 at 10:17
  • Should "execution" also be represented in the database (and how?) or just scheduling? – Branko Dimitrijevic Aug 23 '12 at 10:18
  • @BrankoDimitrijevic Yes it should. – Orson Aug 23 '12 at 10:32
  • @Ben I can try that but i want to store custom information and will prefer if i could to have a table design that can solve my problem. – Orson Aug 23 '12 at 10:35
  • 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](https://github.com/kagkarlsson/db-scheduler/ "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. – Gustav Karlsson Oct 19 '18 at 10:03
  • Since you've thought about this a ton, do you just schedule out each recurring event until some super future date? Or are you looking up any recurring event and displaying it in the view that comes up as needed? – Stephane Jul 02 '20 at 05:40

5 Answers5

29

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
Orson
  • 14,981
  • 11
  • 56
  • 70
29

Microsoft SQL Server has an efficient and flexible design: https://msdn.microsoft.com/en-us/library/ms178644.aspx

Rob Kraft
  • 1,122
  • 1
  • 11
  • 18
12

I think the accepted answer is much more complicated than it needs to be. This can be done with a single table like this:

Schedules

 - Id :int
 - Greetingid :int
 - Startdate :date
 - Frequencytype :char(1)
 - Frequencyinterval :int
 - Timeofday :time

Frequencytype would be one of the following values

  • 'O' = Once
  • 'D' = Daily
  • 'W' = Weekly
  • 'M' = Monthly
  • 'A' = Annually

Frequencyinterval would be numeric and the meaning of the value depends on the value of frequencytype

  • If type = 'Once' then value = 0 (no interval) schedule would execute on startdate
  • If type = 'Daily' then value = # of days interval
  • If type = 'Weekly' then 1 through 7 for day of the week
  • If type = 'Monthly' then 1 through 31 for day of the month
  • If type = 'Annually' then 1 through 365 for day of the year
Vincent
  • 1,741
  • 23
  • 35
  • Hi! I like this solution. Just once thing, how do you check datetime overlaps efficiently? given that, for example, just one task can be running in that time. – miguelps Mar 03 '21 at 22:17
  • This solution is for executing tasks at a set time or interval, so there can be no overlap. It sounds like you're looking for scheduling date or time ranges so you'll need a different solution.. – Vincent Jul 01 '21 at 01:02
6

I have read through the answers above and I think a lot of things are unnecessary, correct me if I'm wrong.

Here is what I think should be done:

Schedule

  • Id

  • type (Daily, monthly, weekly, fixed, yearly) - Enum

  • frequency (Can be 1-7[days of week], 1-30(or 28)[days of month], 1-365[days of year] or null(for daily, fixed) - ArrayField(of ints) - [1, 7] OR [23] OR [235]OR null

  • time (time of day in UTC) - ArrayField(of Char strings - ['9:00', '13:30']

  • date (for fixed type) - datetime - 2009-03-21

  • is_active (boolean) - for enabling, disabling the schedule

  • name (CharField) - If you want to name the schedule

Rest of the fields would require context to what you are building.

Now, for this I'm thinking of running a cronjob every 30mins(I'm taking time input separated by 30mins) which runs a script(django management command in my case) which filters schedules from this table that need to be run:

Query would be something like this:

current_day_of_week = 3
current_day_of_month = 24
current_day_of_year = 114
current_time = 13:30
current_date = 2019-04-24

Filter records that match the below query(not even psuedo code)(I'm using Q objects(https://docs.djangoproject.com/en/2.2/topics/db/queries/#complex-lookups-with-q-objects)

Q(daily AND current_time) OR
Q(weekly AND current_day_of_week AND current_time) OR
Q(monthly AND current_day_of_month AND current_time) OR
Q(yearly AND current_day_of_year AND current_time) OR
Q(fixed AND current_date AND current_time)
Gourav Chawla
  • 470
  • 1
  • 4
  • 12
  • I don't understand what all this is: `(Can be 1-7[days of week], 1-30(or 28)[days of month], 1-365[days of year] or null(for daily, fixed) - ArrayField(of ints) - [1, 7] OR [23] OR [235]OR null` – Eduards Nov 18 '21 at 13:24
  • 1
    That's the explanation of what frequency means and what sort of information you could store in it. If type is daily then you can store values 1 through 7 in frequency column. If type is monthly then you can store the values 1 through 30 in the frequency column. Hope that makes it clear. – Gourav Chawla Nov 20 '21 at 15:49
  • Not quite clear no, if you we're to add a value of `20` what would it mean? – Eduards Nov 22 '21 at 08:24
  • 1
    If type is `Monthly` then frequency will have value `20` as you say. It would mean that we repeat that task every 20th of the month. – Gourav Chawla Nov 26 '21 at 12:32
1

You could start with a simple table with the following generic schema for storing schedules (PostgreSQL). Consider every instance of a schedule run termed as a "job".

CREATE TABLE Schedule (
    id SERIAL UNIQUE,                      -- unique identifier for the job
    name varchar(64) NOT NULL,             -- human readable name for the job
    description text,                      -- details about the job
    schedule varchar(64) NOT NULL,         -- valid CRON expression for the job schedule
    handler varchar(64) NOT NULL,          -- string representing handler for the job
    args text NOT NULL,                    -- arguments for the job handler
    enabled boolean NOT NULL DEFAULT TRUE, -- whether the job should be run
    created_at timestamp NOT NULL,         -- when was the job created
    updated_at timestamp NOT NULL,         -- when was the job updated
    start_date timestamp,                  -- job should not run until this time
    end_date timestamp,                    -- job should not run after this time
    last_triggered_at timestamp,           -- when was the job last triggered
    meta json                              -- additional metadata for the job 
);
aayushsarva
  • 472
  • 1
  • 4
  • 9