1

So, I have found resources such as this Stack Overflow Question and this wiki that recommend database schemas Calendar Events or Reoccurring tasks. They explained how to create a schema that handles the following scenarios:

  • Daily Tasks
  • Weekly Tasks (i.e. every Tuesday)
  • Monthly Tasks (i.e. Every 15th or every 2nd Monday)

However, I would really like to to have my users be able to schedule tasks for every Monday, Wednesday, and Friday, or every Saturday and Sunday. (Similar to the way Google Calendar handles weekly repeating events where users can checkmark the different days).

I would like to be able to do this, because I don't want users to have to create two separate tasks to accomplish this (because if they need to make a change, they have to remember to update not one but two tasks).

Ideas:

  • Just have multiple entries in my table, one for each day of the week the user wants the task to repeat, but this would make the application level a bit of a pain to manage
  • Have a set number for each combination of days (Monday, Monday Tuesday, Monday Tuesday Wednesday), but thats a total of 127 combinations which would also be a pain to account for

Anybody have any good ideas for implementing this design?

My current schema:

ReoccurringRules
-------
RuleID PK int
Frequency varchar(7)
RepeatEvery int
RepeatBy varchar(4)
RepeatAt int
DayOfTheWeek int

Frequency Options:

  • Daily
  • Weekly
  • Monthly
  • Yearly

RepeatBy Options: (only used for monthly tasks)

  • Day
  • Date

DayOfTheWeek: Only used for Monthly Tasks and if day is selected for RepeatBy (Sunday = 0, Saturday = 7)

RepeatAt meaning is different depending on repeat options

  • Daily - No meaning
  • Weekly - Day of the week it is repeated on
  • Monthly:Day - Week number (i.e. 1st Monday, 2nd Monday)
  • Monthly: Date - Day of the monthy it is repeated on (i.e. 13th, 21st)
  • Yearly: No meaning

RepeatAt weekly is where my dilemma is. Should I have multiple entries in this table with different RepeatAt values? Should I input a number 1-127 to specify which combination of days? Is there a better way to do this?

Hope this question makes sense. If not, let me know and I'll try to clarify.

Thanks in advance!

Kiyoko
  • 13
  • 5
  • Have you considered inserting one event per repeat day? – zeros-and-ones Dec 26 '17 at 19:37
  • Similar to the way Microsoft Outlook Calendar handles events (if you've ever used Outlook...)? I.e. a tasks repeats on Mondays and Wednesdays -> One task that repeats on Mondays, duplicate task that repeats on Wednesdays? Yes, I considered doing it that way, and that would be my fallback. However, if users want to make changes to the task, they would have to edit two different tasks I realize that this inconvenience is minor, but still one I would like to avoid if possible. OR I would somehow have to keep track of - if a user edits this task, also update this other one... – Kiyoko Dec 27 '17 at 19:55
  • how about adding booleans for each day of the week? – zeros-and-ones Dec 28 '17 at 07:23

0 Answers0