5

I am stuck with a problem. In an app's db, I am having a schedule table which will store user provided schedules. E.g

  • Daily
  • Every Week
  • Twice a Week
  • Every 3rd (or any user chosen) day of week
  • Every Month
  • Twice a month
  • Every x day of month
  • Every x month of year

And so on. These schedules will then provide reference point to schedule different tasks or identify their repeat-ance.

I am not able to think of a proper database structure for it. The best I can get is to have a table with following columns:

  • Day
  • Week
  • Month
  • Year
  • type

Then store the specified schedule in the related column and provide the type. e.g Every week can go like 1 in week column and 1 (designated value for repeating whole) or something like that.

The problem with this approach is that this table is gonna be used very frequently and the data retrieved will not be straightforward. It will need calculation to know the schedule type and hence will require complex db queries to get each type of schedule.

I am implementing it in Laravel app if that can provide any other methodology. It's a SAAS app with huge amount of data related to the schedule table.

Any help will be very much appreciated. Thanks

gview
  • 14,876
  • 3
  • 46
  • 51
Metabolic
  • 2,808
  • 3
  • 27
  • 41
  • 1
    I would be very interested in the query used to find scheduled jobs to execute. I feel like it must be rather complex. – Namoshek Apr 23 '18 at 04:50
  • @Namoshek Another solution I think of is to store it as cron job strings and query and parse them using a helper package e.g https://github.com/dragonmantank/cron-expression – Metabolic Apr 23 '18 at 06:26
  • 1
    The problem with going this way is that you have to load all your scheduled jobs into memory to check if they need to be run - and that most likely every minute. In this case you could also just use the built-in Laravel cron scheduler instead of a third-party package. But it seems not very performant to me and also hard to distribute across multiple servers (if this will be a requirement). – Namoshek Apr 23 '18 at 07:18
  • 1
    Do you also need history? For example, do you need to know when a user's job was *last* run, so you don't run a once-a-month job twice in one month? (Due to, say, a server restart.) – Mike Sherrill 'Cat Recall' Apr 23 '18 at 10:43
  • @MikeSherrill'CatRecall' Yes I need to know when it was last run so that I don't run again but I will have that info on hand from the jobs table that is related to the schedule – Metabolic Apr 24 '18 at 07:25
  • @Namoshek Yes, but my first problem is to get an optimal db structure and then to get it optimized as the schedule table will be queried with eloquent relationships using wherehas etc. So unless the db is very straightforward, it will be difficult for me to search through table a based on it's relation to schedule etc. – Metabolic Apr 24 '18 at 07:29
  • *"It's a SAAS app with huge amount of data related to the schedule table."* How many users? Can you estimate the number of schedules per user? – Mike Sherrill 'Cat Recall' Apr 24 '18 at 12:52
  • Users will be 0.1 Million+ in first year. Scheduled jobs that any user can create or query will be round about 1 Million+ within first year. – Metabolic Apr 24 '18 at 13:27
  • And the number is expected to increase exponentially in subsequent years. – Metabolic Apr 24 '18 at 13:28
  • Refer my answer to a similar question: https://stackoverflow.com/a/60843760/3865776 – aayushsarva Mar 25 '20 at 06:17

1 Answers1

9

I suggest you are approaching the problem backwards.

Devise several rules. Code the rules in your app, not in SQL. When inserting an event, pre-fill a calendar through the next 12 months with all occurrences of the event. Every month, go through all events and extend the "pre-fill" through another month (13 months hence).

Now the SELECTs are simple and fast.

SELECT ... WHERE date = '...'

has all the events for that day (assuming it is within 12 months).

The complexity is on inserting. But presumably you insert less often than you select.

The table with the event definitions would be only as complex as needed for your app to figure out what to do. Perhaps

start_date DATE,
frequency ENUM('day', 'week', 'month', ...)
multiplier TINYINT, -- this lets you say "every second week"
offset TINYINT,   -- to get "15th of every month"

Twice a week would be two entries.

Better yet, there are several packages (in Perl, shell, etc) that provide a very rich language for expressing event-date-patterns. Furthermore, you may be able to simply 'call' it to do all the work for you!

Rick James
  • 135,179
  • 13
  • 127
  • 222