Creating specific database structure for calendar events

Posted on 2006-04-29
Last Modified: 2010-07-27
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 !

Question by:vpekulas
    LVL 17

    Expert Comment

    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
    LVL 44

    Expert Comment

    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?

    Author Comment

    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.
    LVL 25

    Accepted Solution

    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.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now