How to handle recurring events and calendar tasks (SQL Server and C # tables)

Question

I need scheduled events, tasks, meetings, etc. in my db. Some of them will be assigned at once, and some of them will repeat the "To-Dos" that need to be checked. After viewing the layout of the Google calendar and others, as well as a lot of reading here, I still have.

Calendar table (you can name the schedule table that I assume): Basic_Event Name, start / end, reoccurs information.

Calendar table: link to the schedule table, specific input text, next date / time of occurrence ????

See how SQL Server performs its tasks: http://technet.microsoft.com/en-us/library/ms178644.aspx but this is a little different.

Why two tables: I need to track the status of each instance of the reoccurring task. Otherwise, it would be much easier ...

so ... to the questions:

1) Does this seem to be the right way? Is there a better way to deal with multiple appearances?
2) How often / how can I initiate the creation of entries? I really do not want to create a bunch of entries ... BUT ... What if the user wants to view the calendar for next year ...

Answer 1

It makes sense to define your schedule for the task in one table, and then a separate table for recording each instance of this separately - the approach that I used in the past.

And with respect to creating entries, there is probably no need to create them all ahead. Especially when you consider tasks that are repeated endlessly! Again, the approach I used in the past is only to create the next event. When this instance is activated, then the next instance is calculated and created.

This leaves the problem of viewing future events. To do this, you can start with the start / next scheduled appearance and simply calculate future events on the fly during the display.

Answer 2

Although this is not an exact answer to your question, I solved this problem earlier in SQL Server (although the database does not matter here) by modeling a solution based on Unix cron .

Instead of parsing rows, we used whole columns in a table to store different units of time.

We had events that could be planned; they can point to a one-time schedule table, which was a single point in time (date / time), or a repeating schedule table that was modeled after cron.

Also, be sure to model the solution correctly. An event has a duration, but the duration is not related to the schedule (but the duration of the event can affect the schedule, causing conflicts). Do not try to simulate the duration as part of your schedule.

Answer 3

In the past, when we did this, we had 2 tables:

1) Schedules → Includes repetition information

2) Exceptions → Edit / Modify Specific Instances

Using SQL, you can get a list of "Schedules" that have at least one instance in a specific date range. You can then deploy to the GUI where each instance resides.