[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 796
  • Last Modified:

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 !

0
vpekulas
Asked:
vpekulas
1 Solution
 
HuyBDCommented:
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
0
 
scrathcyboyCommented:
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?
0
 
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.
0
 
SStoryCommented:
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
--------------------------
 Type


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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now