Let us have some fun with modelling recurring events

Programming is all about Modelling your problem using your language constructs and Yes!! Modelling a recurring event is a lot of fun if you push yourself into it 🙂

Ok, What are recurring events anyway? Any activity that you recurrently do, like for example Going to Gym everyday or every other day or every week.

Let us assume, in a pre-primary school, the administration team organises some events on a regular basis. They decided to develop a system which notifies them every morning about the planned event for that day. The problem is how to model this event data and process the information to arrive at the event with highest priority on a given date.

For example, consider below schedule. Starting from April 16th, 2018 every Monday yoga, every Tuesday Swimming, and every Friday Dance. Let’s see how to model this info using two tables. First one to save event information and the second one to save event recurrence information as shown below.

 

event_id week_day start_date end_date
1 Monday 2018-04-16 2019-04-14
2 Tuesday 2018-04-17 2019-04-15
3 Friday 2018-04-20 2019-04-18

Now, the real fun part. Let us find out what is the planned event for June 4th, 2018 with a simple query.

Let’s go one more step up. Now consider, the school administration decides to conduct Aerobics classes on every other Monday instead of Yoga, starting from May 7th, 2018 . How do we do this? Don’t worry. Just add one extra column which tells the recurring interval in terms of days. Let us add recurrence_inerval column to event_recurrence_details table and also add a new entry in event_master for Aerobics. So, now our tables look like this.

 

event_id week_day start_date end_date recurrence_interval
1 Monday 2018-04-16 2019-04-14 7
2 Tuesday 2018-04-17 2019-04-15 7
3 Friday 2018-04-20 2019-04-18 7
4 Monday 2018-05-07 2019-04-14 14

Let us run the same query again and see what is planned for May 21st, 2018.

Two events Yoga and Aerobics on the same day? Yes, because they both match with the criteria. If we set a rule that says, the event that rarely occurs will always take highest priority then, we only want Aerobics as the answer since it has higher priority. Let us add more conditions on the query. To make it simple, let us treat the event with higher recurrence_interval has higher priority. That means, Aerobics should be picked on May 21st. So, our query should be like this.

So, are we done with all possible cases? Shall we consider a few more? I know, the answer is yes 🙂 Hurray !! We got it correct this time.

Let us add one more case. The school admin wants to cancel all the events on last Monday of every month and conduct personality development classes.

How do we represent this data? We can not model this case using the above technique as it is not going to happen at a regular interval of n days.

Let us see what are the available options.

  • Option 1 : http://justatheory.com/computers/databases/postgresql/recurring_events.html. The idea is to define some recurring types like daily, weekly, monthly, etc and combine it with the start and end date of the event to get all the recurring events. This is very easy to implement but can not solve our problem. For example, the last Monday of every month can not be modelled using this approach.
  • Option 2 : Cron expression. Again, this is also an easy to implement design. The idea is to save the recurrence information in the form of a cron expression and parse it using cron parser utilities like cron-utils  This is a very good library and supports 3 types of cron expressions (UNIX, QUARTZ, and Cron4J). Using cron expression, we can model many recurrence types like  daily, weekly, monthly, last Monday of a month, odd Mondays of a month, etc. But, we can not fit our current requirement (every other Monday from a specific date) into a cron expression.
  • Option 3 : http://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method. This is basically a combination of Option 1 and Option 2. But instead of using the whole cron expression directly, each component is stored as a separate column. Along with these columns, there is one more column which takes recurrence interval. So, either recurrence interval or combination of other columns should give us the required information. Our requirement can be fit into this model up to some extent. But, we need to put extra effort to compile this information and get the recurring dates.
  • Option 4 : iCal compliant schema. Using this, every other week problem could be solved, but some of the cases which cron expression covers would not be covered. For example, the last Monday of every month kind of cases.

Note****: All the above options are useful only up to generating recurring dates for an event. None of them consider the priority of the events to pick up the higher priority one automatically which is very important point to consider.

Let us design our own data model to solve this problem.

Let us also add another case which is ‘Occurs Once’ type. For example, On Aug 27th, there have  Annual Day event which should have highest priority. All other events should be cancelled on this day.

So, here is our new schema. Create a  table with fields for event_id, week_day, start_date, end_date, and recurring_frequency. We could also save priority in the same table. But it would be like replicating the same information. So, for ease of maintainability create an enum class to hold different frequencies and their priority. The same enum class contains all the logic to arrive at the highest priority event given all the applicable events on a given day.

Now, our event_recurrence_details table would look like this after adding a few more event details.

 

event_desc event_id week_day start_date end_date recurring_frequency
Yoga 1 Monday 2018-04-16 2019-04-14 EVERY_WEEK
Swimming 2 Tuesday 2018-04-17 2019-04-15 EVERY_WEEK
Dance 3 Friday 2018-04-20 2019-04-18 EVERY_WEEK
Aerobics 4 Monday 2018-05-07 2019-04-14 OTHER_WEEK
Personality Development 5 Monday 2018-06-25 LAST_WEEK_OF_MONTH
Annual Day 6 Monday 2018-08-27 2018-08-27 OCCURS_ONCE

***Note:

  1. Here recurring_frequency column’s data type is enum with values  ‘EVERY_WEEK’, ‘OTHER_WEEK’, ‘FIRST_WEEK_OF_MONTH’, ‘SECOND_WEEK_OF_MONTH’, ‘THIRD_WEEK_OF_MONTH’, ‘FOURTH_WEEK_OF_MONTH’, ‘LAST_WEEK_OF_MONTH’, and ‘OCCURS_ONCE’
  2. For convenience, we make sure that the weekday of the start date for an event is always same as the weekday for which it is planned. That means for a Monday event, the start date should always be Monday. We made this arrangement just to simplify the logic.
  3. Setting end_date as NULL means it is a lifelong event.

Now, let us see how to get the most applicable event on “Oct 29th, 2018”

If we observe the data, There are totally 4 Monday events which are ‘Yoga’ (EVERY_WEEK)’, ‘Aerobics’ (OTHER_WEEK)’, ‘Personality Development’’ (LAST_WEEK_OF_MONTH), and ‘Annual Day’ (OCCURS_ONCE).  Oct 29th fits for all the above events except ‘Annual Day’ whose date does not match. But, ‘Personality Development’ should be picked since it has the highest priority.

Okay, now we have all the event information filled in the table. The challenge is how do we process this data to arrive at the most applicable event on a given date. Compiling this information manually is not that difficult but that is not our goal. Our aim is to automate this process. Let us do this step by step.

Step 1. First get all the events whose week day matches with the week day of given date and the given date falls between start date and end date of the event. The below query would give this list.

 

event_desc event_id week_day start_date end_date recurring_frequency
Yoga 1 Monday 2018-04-16 2019-04-14 EVERY_WEEK
Aerobics 4 Monday 2018-05-07 2019-04-14 OTHER_WEEK
Personality Development 5 Monday 2018-06-25 LAST_WEEK_OF_MONTH

Step 2. Sort this list of events in descending order of priority. Then the list becomes ‘Personality Development’, ‘Aerobics’, and ‘Yoga’.

Step 3. Filter all the events which are actually applicable. This step eliminates ‘Aerobics’ as the given date ‘2018-10-29’ does not satisfy the condition of OTHER_WEEK starting from 2018-05-07.

Step 4. Now pick the first item in the list which is ‘Personality Development’.

Observe that even though ‘2018-10-29’ fits for 2 events (‘Yoga’ and ‘Personality Development’) only ‘Personality Development’ is picked as it has the highest priority.

A simple demo application can be found here.

https://github.com/klakshmi75/event-modelling