CODEWITHSUNDEEP
database-designdatabase-schema
Orson
Orson

Reputation: 15431

Database table design for scheduling tasks

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?

Upvotes: 34

Views: 68621

Answers (5)

Rob Kraft
Rob Kraft

Reputation: 1232

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

Upvotes: 31

aayushsarva
aayushsarva

Reputation: 492

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 
);

Upvotes: 3

Vincent
Vincent

Reputation: 2139

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

Upvotes: 14

Gourav Chawla
Gourav Chawla

Reputation: 500

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)

Upvotes: 6

Orson
Orson

Reputation: 15431

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

Upvotes: 29

Related Questions