Database table design for planning tasks

Question

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

How do I start creating database tables for this problem?

Edit # 1

Basically, I am writing an application that allows users to schedule pre-configured greetings that will be sent at various pre-configured times. I know that I need a table that stores information about the schedule (for example, Christmas, marketing, ... and when the work schedule should be executed). Then another table to record which schedule was running, which greeting was sent, to whom and by email; basically a transaction table.

My problem is to design the Schedule table because I want to allow users to run the schedule on a specific date, on a specific day of the week (repeating), on a specific day of each month, at a specific time every day, and on a specific day / month (e.g. 25 / 12) every year.

How can I create a set of tables for a schedule that will carefully take care of these inputs?

Answer 1

This is the table structure I came across:

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 2

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

Answer 3

Since you are talking about schedules, I assume that you want to create a batch application for managing and completing tasks.

You can check spring batch metadata for a reference implementation. However, the exact design will depend on your requirement. This is just a pointer.

Answer 4

I read the answers above and I think that many things are not needed, correct me if I am wrong.

Here is what I think should be done:

Schedule

---

• I'd

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

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

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

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

• is_active (boolean) - to enable, disable the schedule

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

The remaining fields require context to what you are building.

Now for this, I think of running a cronjob every 30 minutes (I take the time divided by 30 minutes), which runs a script (the django control command in my case) that filters the schedules from this table that need to be run:

The request will 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) 

Answer 5

To simplify your work, you can use an existing scheduler for part of the planning. In Java, for example, there is Quartz or a library that I wrote myself, a db-scheduler .

db-scheduler easy to integrate and has built-in support for tasks on a regular schedule (fixed duration, daily, etc.). Runtime is stored in a single database table, so it is saved after restarts.