CakePHP - event's date(s) & time(s) - how to structure

TLDR: Need to understand the best way for a user to be able to add a date(s) & time(s) for an event, and how to structure database

Explanation: When a user adds an event, they need to be able to choose the date of the event, whether or not it will repeat daily/weekly/monthly, start and end time of the event or "all-day", if it's weekdays only or weekend...etc. Basically everything you can do w/ Google calendar when you create an event and they need to be able to edit it too (if that matters). But ALSO, they need to be able to add another date/time - for instance:

Add an event where on Monday and Wednesday of this week and three weeks from now, it goes from 8-10pm. On Tuesday and Thursday this week only, it goes from 6-9pm.

My thoughts so far:

Create a "dates" table with a HABTM relationship w/ my "events" table. When a user adds a date (with all the options 开发者_JAVA百科of repeat..etc etc., it runs a function to process those repeats/limits...etc and adds all the dates into the dates table w/ their start/end times.

But - then how do I manage it if they want to edit that - since it just created multiple fields.

Question / Help?:

Am I even on the right track with this? I'm new to CakePHP, and it's hard for me to wrap my head around the best ways to do things... I'm not yet looking for technical help (would not turn it down though) - for now, I just need to get the idea for the best way to structure everything to be able to manage this. Maybe I need a "dates" table AND a "times" table? Maybe a "dates" table with an id that references many individual rows in a "dates_data" table?

Thank you very much ahead of time for any help / direction!

You're doing great. Let me just share my thoughts.

If I would design this, I'd have 3 models:

Event

• id
• user_id
• description
• created (datetime)
• updated (datetime)

Schedule

• id
• event_id
• description
• start (datetime)
• end (datetime)
• duration (time, if empty(NULL) it means this is a whole day event)
• repeat_time (e.g. 3:00pm means 3pm daily)
• repeat_day (for weekly/monthly, e.g. Monday, Monday & Tuesday, Monday to Friday)
• repeat_date (for monthly, e.g. 1 means every 1st day of month, 31 means every 31st or end of the month)
• repeat_anniversary (for specific date every year, e.g. every December 25th)

Date

• id
• schedule_id
• start (datetime)
• end (datetime)

Now let's have an example of an event. Let's say we want an event that will repeat every Saturday and Sunday of May & June 2011 at 1:00pm until 3:00pm (two hours):

The events table contains the basic detail of an event. One record will be saved here.

The schedules table is separated so that you could add multiple schedules. One record will also be saved in schedule with the following fields:

• duration: 02:00
• start: 2011-05-01
• end: 2011-06-30
• repeat_time: 13:00:00
• repeat_day: 01,07 (Sunday & Saturday)

Now on dates table, there will be 17 records, one for each occurrence of the schedule. The reason why I separated this is that it will be easier for me know when will the event fall. This will be useful, for example, when creating the calendar. One of the records for the dates table will look like this:

• start: 2011-05-01 13:00:00
• end: 2011-05-01 15:00:00

Now what if the user edits the schedule? The schedule record would be edited. All dates record would also be edited. You don't wanna delete and recreate the dates, since you might use each record for another model (e.g. user might want to tag other users as attendees for each date of the event).

I hope this helps. Goodluck on your project!