MySQL：关于矩阵的结构解决方案

Question

I have the following UI:

The goal is that an organiser can enter a recurrent activity for the entire year, and the organiser can decide to select which weekdays are applicable for the recurrent event.

My database has a regular structure:

activity_id  |  start_date  |  end_date 

For a one-time event, this is perfect. Even for a date range of three weeks, it's still fine.
However, for a year, it means that there can be hundreds of intervals (records) that need to be inputted into the database in a single save.

I am not confident that this is a good solution, can you give me some advice on the matter?

Answer 1

If you are worrying about data load, my suggestion would be to modify your structure above to record which day(s) of the week an event occurs on: activity_id | start_date | end_date | days_of_week and only have one row in the DB for that event. Then, I would create a "blacklist" table, so if a user decides the event won't occur on specific days and "deletes" that occurrence in the UI, it's not removing an occurrence but actually adding a blackout day to another table. Then you can programmatically remove occurrences from your UI based on rows in the blacklist table. Hope this makes sense, let me know if you need clarification.