Database Architecture Recurring Events

Question

I am building a Database based on open and crowdsourced data. The data involves recurring scheduled events. One event is maximum 7 days long.

Users ask for the current event and the next one depending on their location and datetime. (They cannot ask for the event happening in 2 months, only the current one, and the next one)

There is always one unique event going on at a certain time and location. (You cannot have 0 events, nor 2)

Here are the issues I am facing :

• First issue: In the open data gathered, the events do not have start and end datetimes. They have a day (1 to 7) and week (1 to 5) id. A start hour and an end hour. So If an event happens every Tuesday, I'll have 5 lines (Week 1 to 5 with Day 2). If an event goes from Monday 10PM to Tuesday 2AM. I'll have two lines. One Monday from 10PM to midnight. One Tuesday from midnight to 2AM.

• Second issue: The data can change at any time thanks to the crowdsourcing feature. A user can say that an event goes from 9 to 11 instead of 8 to 10. Since events follow each other, this will impact other events happening at the same location and same day.

How would you build your database based on these elements ?

Answer 1

1. Use RFC 5545 (iCalendar) RRules. Store them in your events table

2. Use materialized views to generate actual events from the RRules, say +-1 month or +-1 year

If you use PostgreSQL as your database, you can use the pg_rrule extension to calculate dates based on RRules.