First of all, please excuse my perfectible English, I am French.
I'm coming to ask for some advice on a database design problem.
I have to design a calendar with events. Briefly, an event includes a start date/time, an end date/time, and a description.
The problem is that I have to consider repetitions; it is possible when creating an event to indicate that it starts next week and repeats itself until a specific date or not.
I see two possibilities of design:
- create an events table with id, start_datetime, end_datetime and description fields. When adding a new event, we generate as many rows as there are repeated events.
Advantages: we can make a SELECT * to retrieve all the events, without particular algorithm. In addition, it is possible to modify the descriptions of each occurrence of an event, insofar as they are considered as all different.
Disadvantage (MAJOR!): If we do not put an end date to have an infinite repetition, we will not memorize an infinity of events...
- take inspiration from the method described on this thread, that is to say two tables:
- events table
id description
1 Single event on 2018-11-23 08:00-09:30
2 Repeated event :
* every monday from 10:00 to 12:00 from Monday 2018-11-26
* every wednesday from 2018-11-28 from 14:00 to 14:45 until 2019-02-27
- event_repetitions table
id event_id start_datetime end_datetime interval end_date 1 1 2018-11-23 08:00:00 2018-11-23 09:30:00 NULL NULL 2 2 2018-11-26 10:00:00 2018-11-26 12:00:00 604800 NULL 3 2 2018-11-28 14:00:00 2018-11-28 14:45:00 604800 2019-02-27
Note: interval is the number of seconds between each occurrence, 604800 = 24 (hours) * 3600 (seconds) * 7 (days).
Advantage: In the case of infinite repetitions (case of the event of id 2), we have very few lines to write and performances are increased.
Disadvantages: if we want to modify the description of the event (or other possible fields) for a specific occurrence and not another, we can not without creating a third table, event_descriptions for example:
id event_id user_id datetime description 1 2 1 2018-11-26 10:00:00 Comment from 2018-11-26 2 2 2 2018-12-03 10:00:00 Comment of the second occurrence, i.e. from 2018-12-03
Note: user_id is the logged-in user who wrote the comment.
Another disadvantage is that to get the list of events for a given day, week, or month, the selection query will be more complex and use joins. The event_descriptions table may, when there are hundreds of thousands of events, be very big.
My question is: what would you recommend as a more effective alternative? Maybe the second solution is good? What do you think?
In terms of technologies used, I intend to go on MySQL, the DBMS I know best. Nevertheless, if you think that using for example MongoDB is better in case of very large numbers of lines, do not hesitate to report it.
For information, my application is an API developed with API Platform, so Symfony 4 with Doctrine ORM.
Thank you in advance for your answers.
