I'm working on an application where an admin creates events for users. Also, the admin can create recurring events (e.g. every tuesday 8:30 PM). The user(s) can interact with events: reply to events, create comments for event, ...
My database design looks like this:
events table which holds all the events. Each entry is one event.
| id | recurring_event_id | name | date | ... |
| 1 | 4 | Event 1 | 2022-09-15 19:00:00 | ... |
| 2 | NULL | Event 2 | 2022-09-24 15:00:00 | ... |
| 3 | 4 | Event 3 | 2022-10-13 19:00:00 | ... |
recurring_events table that stores data about recurring events (weekday, time, ...).
| id | from | till | weekday | time |
| 2 | 2022-01-01 | NULL | 2 | 14:00:00 |
| 4 | 2022-08-01 | 2022-10-30 | 4 | 19:00:00 |
When the admin adds a recurring event to the recurring_events table and he provided a till date, all events are automatically created in the events table. The events contain the recurring_event_id as a foreign key.
All additional data is stored in extra tables - for example event_replies has the columns event_id, user_id and reply.
| id | event_id | user_id | reply |
| 1 | 2 | 1 | 1 |
| 2 | 2 | 45 | 2 |
Problem: The admin can’t add infinitely recurring events. Because every event needs its own row in the events table. My current design works well if the admin adds single events or recurring events with a fix end date. But I can't add an infinite number of events to the table.
For infinitely recurring events I thought about something like this, but I don't know if there is a better way to achieve this:
The user sees fake events. As soon he tries to interact with the event and the event does not exist, an event will be created. When the recurring-events changes, all the existing events will receive an update too (via recurring_event_id column in events table).
Is this a good way, or is there a better way to solve this problem? My approach is hard to maintain, because I have to update my complete event interaction logic.