jcombs_31 Posted September 19, 2009 Share Posted September 19, 2009 I've created some pretty simple event calendars that output a monthly table and query the database for daily events. I'm looking to create a much more advanced calendar like you would find in a desktop app like outlook. I need recurring events and floating events and probably times because people will be submitting requests for schedules. I'm really just trying to wrap my head around the basic database design to handle the floating and recurring events. I don't to be limited to a weekly recurrence pattern. I would like options like daily, weekly, monthly, or maybe even biweekly, etc. How do you account for a recurring event that has no end date in the database? Maybe this is something easy to create, but I would like some direction on the database schema that would work best to help get me going in the right direction. Any thoughts or references on this would be very helpful. Quote Link to comment Share on other sites More sharing options...
paralyzah Posted September 20, 2009 Share Posted September 20, 2009 RecurringEvents: Key, Pattern, Date RecurringEventsExceptions: ForeignKey, Date In RecurringEvents, Pattern is a TINYINT where 1 = Weekly, 2 = Every second week, 3 Monthly Date is if Pattern is 1 or 2 the day of the week, and if Pattern is 3 the day of the month. In RecurringEventsExceptions, Date is an excact date that match a recurring event and means that the user have moved it (and you've stored it as a regular event this week or month or whatever) or that the user has deleted it this week or month or whatever. You'd probably need some other fields in the tables for other purposes, but these are the ones you asked for. Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted September 25, 2009 Author Share Posted September 25, 2009 I probably should have worded better what I'm after. I do want a good idea about data design, but let's say for example I did something like this: TABLE: EVENTS id event_start [date] event_end [date] recurrence [enum] {once, daily, weekly, monthly, yearly} title [varchar] description [varchar] The only things I'm concerned about are the dates. Floating events can be queried pretty easily by the start/end dates, but how would you really be querying recurrence? The calendar itself is basically created by a loop and on each day I would check if an event(s) exist and display it. I'm really looking for a little logic on how this would work best. Quote Link to comment Share on other sites More sharing options...
paralyzah Posted September 28, 2009 Share Posted September 28, 2009 What your asking for is exactly what I answered in my previous post!? You'll need the two tables I gave you in addition to the table where you store the regular events. Quote Link to comment Share on other sites More sharing options...
paralyzah Posted September 28, 2009 Share Posted September 28, 2009 If you're asking how to get and sort the data, I'd be using two queries. One to get all the regular events and one to get the recurring events. Then I'd have to convert the recurring events to actual dates for a specific time frame (i.e. from now and three months forward in time). Finally I'd store all the dates in one array and sort them. Quote Link to comment Share on other sites More sharing options...
448191 Posted September 28, 2009 Share Posted September 28, 2009 Perhaps this topic will help you: http://www.phpfreaks.com/forums/index.php/topic,227128.0.html Just need to squeeze in an end date and it should drop in nicely I think. Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted September 28, 2009 Author Share Posted September 28, 2009 Thanks John, that's a hell of a query. Points me in the right direction. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.