Alex Rivera | Logout

SQL Infinite Calendar Pattern

Asked 2012-06-10T21:58:47.863
33

I'm going to make a Mysql based calendar system where you can have repeating pattern for lets say every monday forever and ever. It must also cover static/once-only events. What I'm wondering about, is which solution would be most logical (and best) for me to use. I have four methods which I'm wondering to chose between.

Method #1

Make a function which accepts parameters from and to. This function would create a temporary table table which imports existing static schedule through INSERT ... SELECT. Afterward it would read of the pattern table and populate the temporary table through the peroid based on from and to.

This solution seems nice from the point of view that queries will be simplier to fetch data with and it works into infinity since you can just repopulate the table depending of which month you're loading. What I'm curious about is whenever this might be a laggy way to do it or not.

Method #2

Create and join given patterns through a subquery and JOIN with static calendar.

This seems to be rather annoying since the queries would be a lot more bigger and would probably not be good at all(?).

Method #3

Basicly just INSERT pattern for lets say one year ahead. Then I guess a cron job would repopulate to make it one year ahead always.

This is a simple way to do it, but it feels like a lot of unneeded data stored and it doesn't really give the infinity which I'm after.

Method #4 (Suggested by Veger)

If I understand correctly, this method would fetch the pattern from another query and creates events upon execution. It's similar to my thoughts regarding Method #1 in that way that I consider simple pattern to create several rows.

However if this would be implemented outside Mysql, I would loose some database functionality which I'm after.

I hope you guys und

php mysql codeigniter calendar
Edit
Report

1 Answer

0

I would suggest something around the lines of this: Split your Events table into 2 because there are clearly 2 different types recurring events and static events and depending on the type they will have different attributes.

Then for a given Event look-up you would run 2 queries, one against each Event Type. For the static events table you would defiantly need (at least) one datetime field so the lookup for a given month would simply use that feild in the conditions (where event_date > FirstDayOfTheMonth and event_date < LastDayOfTheMonth ). Same logic for a weekly/yearly view.

This result set would be combined with a second result set from the recurring events table. Possible attributes could be similar to crontab entries, using day of week/day of month as the 2 main variables. If you're looking at a monthly view, 

select * from recurring_events where DayOfWeek in (1,2,3,4,5,6,7) or (DayOfMonth > 0 and DayOfMonth < @NumberOfDaysInThisMonth )

Again similar if for a weekly/yearly view. To make this even simpler to interface, use stored procedures with all the logic for determining 'which days of the week are found between date A and date B'.

Once you have both result sets, you could aggregate them together in the client then display them together. The adavantage to this is there will be no need for "mock/empty records" nor async cronjobs which pre-fill, the queries could easily happen on the fly and if performance actually degrades, add a caching layer, especially for a system of this nature a cache makes perfect sense.

answered 2012-06-16T12:56:53.757

Your Answer