I'm designing a system that will have events, both one-time and recurring. Trying to decide on my table structure - at this point I'm thinking two separate tables for the event details (with one parent table holding the basic similar data).
I'm having trouble with finalizing how I want to store everything for the recurring table. The types of appointments would be things like:
Every day at noon from START_DATE to END_DATE
3 times per day every day from START_DATE to END_DATE (maybe store these as three seperate events?) - it's very likely a recurring event would often be 2-3 times per day.
mon, wed, fri every week, no end
a set day every month
a set day every year
So the fields I'm thinking so far are:
start_date DATE (optional)
end_date DATE (optional)
start_time TIME (required) (all events require a time, but if it's a recurring event without an end date it won't have dates)
end_time TIME (required)
repeats_on (optional varchar, possible values like MM-DD)
repeats_every (optional varchar: possible values like "mon,wed,fri" or "daily" or "weekly")
Any input? I'm off to do some searching for more ideas.
OR: Do I use PHP to generate the events for each of the ones with start and end dates, and store them in the regular table (and somehow link them together), and then also generate them for up to a certain amount of time for the others?