0

I have the following UI:

enter image description here

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?

html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • Looks like a good solution to me – Strawberry Dec 27 '14 at 22:33
  • Really? Isn't this heavy for the back end to process? I was thinking about storing weekdays as boolean fields (Monday - Sunday) in the database as well, and just a date_interval as is. What do you think about this? – html_programmer Dec 27 '14 at 22:34
  • Hm, perhaps I misunderstood your intent. Yes, you could store date_start, date_end and recurrence (within that range). Recurrence could be a bitwise value from 1 (Mondays) to 127? (everyday), but wouldn't allow for other possibilities like 'once every two weeks'. – Strawberry Dec 27 '14 at 22:39
  • You mean 1 to 7 (why 127?)? Edit-> oh never mind :-)) It's true: I have thought about this too, but it turns out to be hard to embed more flexibility. Given the fact that some organisers also plan activities on the last weekend or a last certain weekday of the month. – html_programmer Dec 27 '14 at 22:41
  • 1
    Maybe take a look at the icalendar specification - I suspect someone's already taken a look at the problem of storing this to a MySQL db – Strawberry Dec 27 '14 at 23:17
  • Yeah, thanks for advice. I am looking at the available options currently. – html_programmer Dec 27 '14 at 23:21

1 Answers1

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.

rpaskett
  • 456
  • 1
  • 3
  • 13
  • Thanks, I am looking over a few more options. Possibly I will store the duration and follow the workflow as suggested in this post: http://stackoverflow.com/questions/5183630/calendar-recurring-repeating-events-best-storage-method – html_programmer Dec 28 '14 at 00:50