Creating specific database structure for calendar events

What would be the best calendar event structure for the database ?  In a I require the following from the event:

- Ability to be a single day event
- Ability to be an event spanning multiple days
- Ability to be an event spanning multiple days with exceptions (All days from 04/29/2006 to 07/29/2006 but not on 05/28/2006)
- Ability to be a reoccurring event on given date (every March the 5th for example)
- Ability to be a reoccurring event on given days (every Monday for example)
- Ability to be a reoccurring event on given days (every Monday for example with the exception of 2 weeks from now)

I don't want to end up in a situation where I design the DB my way and 100 hours later I end up scraping it because it's too slow and doesn't offer enough options. Thanks for your time !

Who is Participating?
SStoryConnect With a Mentor Commented:
How about something like this?

|       Event                |                 |       Exceptions          |
--------------------------                 ---------------------------
EventID                    |                 |        EventID             |
StartDate                 |                 |        ExceptionDate    |
EndDate                   |                 |                                |
Description               |                 |                                |
Type                        |
RecurranceInterval    |

        EventType                     <--This is a table of event types, such as Single, RecursMonthly, RecursDaily

Then make a store procedure that hits all of this to get events for a day, to display them on that day in the calendar and call it for each day.  Inside the stored proc, based upon the type of event, do whatever.

I actually did something similar on a job scheduling site and it works fine.  If this is a website and a lot of people will hit it, then you **MIGHT** want to build it to a cached dataset for the month to avoid so many hits and refresh the cache frequently.
Hi vpekulas!

As I think
there are should be 2 tables
first table store event' infromation and "start date","end date"
second table store "event infromation" and "date of event"

so you can use inner join to get specified date, left join to get all date of event(s)

Note: if you need store start time and end time, you must add the 2 fiels and sequense field (tinyint e.g. 0:daily, 1:weekly, 2:monthly... )

Hope this help
That wont get the exclusion dates.  If you want specific exclusion dates, and dates that could recur only once per year, I see no other way than to have a table (1) of the dates in each year for the next say 10 years; (2) of the dates per month, and (3) a formula for each query to calcualte the days as an index into the tables 2 and 1 above.  How else can you get this complexity of querying?
vpekulasAuthor Commented:
That's exactly my point. I'm a bit afraid that inserting each date as it's own separate records isn't the right way to go either though. It'd be best to do the date calculation on the SQL server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.