Hi all,

Myself and my colleague are producing an events calendar web application(using access and asp). We both agree that calendar events should be stored in an events table with a unique ID, start/end date and other information such as description.

I think that when we generate the html calendar using the asp script we should SELECT all EVENTS with a date between the start and end of that month so we can highlight cells which have events and display then in tooltips etc..

My colleague believes we should store dates in a CALENDAR table which also contains a boolean to indicate whether there are events for that date. That way when the calendar month is drawn by the asp script, it can match each date cell with that SELECTed from the CALENDAR table.

I think that although this is quicker for displaying a calendar, when an event is created or deleted, the database has to work harder. Furthermore we would have to create the dates in the CALENDAR table if they had not already been created and be storing a lot more data.

We have also designed a table of FIXTURES (for use in sports fixtures) which my colleague argues can also make use of the CALENDAR table for the date of the fixture(via a foreign key to the ID of the calendar date).

I think that fixtures and calendar events should both be EVENTS and events be given a type, thus we can select events by type,date etc when displaying them.

Which approach is the best? if any, and why?

Sorry that was so long winded.

Can you give an example of what a sports fixture is? I haven't heard the term before.

I would agree that you shouldn't link events that span several days to individual calendar days. That would be a lot of work when a change happened.

A sports fixture is one team playing agains each other (i.e a football match), this is done in a seperate part of the web application, the link is that each fixture has a date associated with it. we don't necessarily want to display fixtures on the calendar but I thought it would make sense to have them as an EVENT so it would be possible to add another even type to the database easily if we need to in the future.

Events are not linked to calendar days per-say but if there was an event that spanned several days, each day that it spanned in the calendar table would have to have its boolean flag set to true(to indicate that there are events scheduled for that day).

SELECT Calendar.Date, Event.Description
FROM Calendar LEFT OUTER JOIN Event ON Calendar.Date BETWEEN Event.StartDate AND Event.EndDate
WHERE Calendar.Date BETWEEN '01/01/2010' AND '01/31/2010'
ORDER BY Calendar.Date

That should give you a list of all the days in January and where available the descriptions of each event that happens to occur on that day, even if it is a multi-day event.

I would store the sports fixtures as a subtype of events, that way if people want them to show up on their calendar you can do it easily.

So you would keep the Calendar table? Without this table the query would become:

SELECT StartDate, EndDate, Description
FROM Event
WHERE StartDate BETWEEN '01/01/2010' AND '01/31/2010' OR EndDate BETWEEN '01/01/2010' AND '01/31/2010'
ORDER BY Calendar.Date

Making more work for displaying the calendar but less work for adding and removing events.

p.s thanks for replying.

I would only use the Calendar table as a way to know every day of the month. You could either build a regular table and populate it for the next 10 years OR build a temp table and populate it for the month you are looking at. Either way is valid.

By having only an events table you have to generate a day, loop through the events you have in your result set, generate the next day, loop through the events again, etc. If you generate all of the days into a temp table then use a join you end up with:

1/1/2010, 'New Years Day'
1/2/2010, NULL
1/3/2010, 'Jims birthday'
1/3/2010, 'Dentist appointment'
1/4/2010, NULL
etc.

Either way I see no reason for a boolean that says there is an event for that day. And if you don't want to store a permanent calendar table it shouldn't take long to build and populate a temp table.

Try both and see which is faster, generating a temp calendar table and doing the join or looping through the event result set for each day. Maybe processor speed will win over database speed.

Ok, thanks for that. It makes a lot of sense. I think my colleague is going to get his way then.

Omit the calendar table. As you've pointed out yourself, maintaining such a table could be quite tedious and error-prone. Plus you have to determine how, when, and if you need to generate dates in the calendar table.

That leaves two options to generate a calendar from a list of events:

1) Programatically in your application layer

Here's some pseudo-code, assuming you've fetched your events ordered by start date ASC, end date DESC into some sort of array-like structure:

2) In SQL

Mark's previous post got me thinking about accomplishing this using pure SQL. I imagine the code to accomplish this will be very server-specific, but here's how I'd do it using postgresql:

The key here is the generate_series function, which returns a series of rows that we can use to generate dates for the time period of interest. You'll need to find the equivalent (if it exists) for whatever server you're using.

thanks futurity,

these were exactly my original thoughts. I was planning to use the first method you suggested (generating the calendar in the application layer) as I wasn't aware of doing it within the sql itself. my colleague however, said that looping through the events to see if they applicable to the current day would be more time consuming than just joining a calendar table and and events table. I have found a few calendar systems that only store events so this must be the approach they use.

As I think about this more -- and mess around with this a bit myself in one of my own apps -- I personally would stick with method 1.

Either way, you have to iterate through the results to display the calendar, so you're not really able to save any process cycles. If you get the data back sorted correctly, the amount of looping you do should be the same regardless of which method you use. But using the db to generate the calendar requires using non-portable, server-specific SQL, and (at least with my own benchmarks) takes significantly longer to execute. In the end, I don't think you're really saving anything using the 2nd method.

the only way it saves process cycles is if you are checking only whether an event exists for each day(not actually displaying it), that way with MarkATrombley's sql before, a simple list can be returned from the database.

I think I have all the information I need now, thankyou very much both of you for your replies.

The two bolded terms probably shouldn't appear in the same sentence. Access is not designed for the inherently multi user environment of a web application.

I'd say the best approach is to install Sitefinity and, if necessary, modify a RADCalendar to suite your liking.


Is there a reason for rolling your own? There are A LOT of web calendars out there...

maybe I should have made myself clearer, the web application is not actually deployed to a server on the internet, just accessed from a local network connection by a few users.

access was not my choice of database as the system was created previous to me becoming an employee.

the reason for rolling our own calendar is because this is part of a system that we sell to clients and thus everything it uses has to be license free.