title: MySQL database design - Infinitely recurring events with additional relational data
body: <p>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, ...</p>
  <p>My database design looks like this:</p>
  <p><code>events</code> table which holds all the events. Each entry is one event.</p>
  <pre><code>| 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 | ... |
  </code></pre>
  <p><code>recurring_events</code> table that stores data about recurring events (weekday, time, ...).</p>
  <pre><code>| 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 |
  </code></pre>
  <p>When the admin adds a recurring event to the <code>recurring_events</code> table and he provided a <code>till</code> date, all events are automatically created in the <code>events</code> table. The events contain the <code>recurring_event_id</code> as a foreign key.</p>
  <p>All additional data is stored in extra tables - for example <code>event_replies</code> has the columns <code>event_id</code>, <code>user_id</code> and <code>reply</code>.</p>
  <pre><code>| id | event_id | user_id | reply |
  | 1  | 2        | 1       | 1     |
  | 2  | 2        | 45      | 2     |
  </code></pre>
  <p><strong>Problem:</strong> The admin can’t add infinitely recurring events. Because every event needs its own row in the <code>events</code> 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.</p>
  <p>For infinitely recurring events I thought about something like this, but I don't know if there is a better way to achieve this:</p>
  <p>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 <code>recurring_event_id</code> column in <code>events</code> table).</p>
  <p>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.</p>