Data storage strategies - a balancing act

• Chris Roberts

  SSC Veteran

  Points: 220

  More actions

  March 11, 2004 at 3:25 am

  #498229

  Well - here's my thoughts (although I haven't thought everything through that well!!).

  I'd have a table to store the events.  I would have a separate (but linked) table which can have an optional entry for the recurrance information.

  When creating a recurring event, I'd create all the events in the events table.  The first event would also have an entry with the details of the recurring pattern in the second table.  All subsequent events would store the ID of the first event.

  You can change individual events easily.  To change the entire series, you could change all the associated records easily with update queries using the ID of the first, 'master' item.

  This system would work well in an environment where viewing the calendar happens more frequently than editing it.

  Anyway - like I say - I haven't thought that through fully - but it's my initial reaction!  Hope it helps!

  - Chris

  PS. One other thought is how well this would work for events that have no end date!?!?!

• jraha

  SSC Eights!

  Points: 817

  More actions

  May 19, 2005 at 9:44 am

  #559895

  I also have a custom application that needs to access a SQL Server database to retrieve calendar events.

  To deal with recurring events, I would like to mimic the functionality that you can find in Outlook.

  Before I dive in and create an object model for that, I wanted to ask around and see if and how others have solved this problem.

  Any suggestions?

  Thanks,

  jmr

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  May 19, 2005 at 11:43 am

  #559953

  If it's an application that I'm writing, I tend to wrap everything in stored procedures.

  I'd use a datamodel to define events and recurrence and also an actual date/what's going on table.

  In that manner, you can use a stored procedure to "get_calendar" for a specific date range.  That procedure can both populate & clean up entries in the actual event calendar table based on the event definitions.  The work to create future event entries is going to have to happen anyway, why not encapsulate the retrieval of it with a process that maintains it.  Then you only will generate an entry for a range that is actually looked at.

• jraha

  SSC Eights!

  Points: 817

  More actions

  May 19, 2005 at 3:24 pm

  #560058

  I've been digging a bit further...

  I think that the scheduling pattern used by outlook conforms to a standard called RFC2445 (iCalendar).

  What I'm looking for is a set of tables/stored procedures that are set up to store and retrieve information based on this standard.

  Here is a link to a document entitled:

  Internet Calendaring and Scheduling Core Object Specification

                                (iCalendar)

  http://www.ietf.org/rfc/rfc2445.txt

  So if anyone has any information regarding tables/stored procs that can contain this information, please let me know!!

  Thanks,

  -jmr