Best way to store event times in (My)SQL database
mysql, time
Solution
Table: Events
- StartTime (dateTime)
- EndTime (dateTime) null for no end time
- RepeatUnit (int) null = noRepeat, 1 = hour, 2 = day, 3 = week, 4 = dayOfMonth, 5 = month, 6 = year
- NthDayOfMonth (int)
- RepeatMultiple (int) eg, set RepeatUnit to 3, and this to 2 for every fortnight
- Id - if required, StartTime might be suitable for you to uniquely identify an event.
- Name (string) - name given to the event, if required
This might help. It would require a decent amount of code to interpret when the repeats are. Parts of the time fields that are at lower resolutions than the repeat unit would have to be ignored. Doing the 3rd saturday of the month woudln't be easy either... the NthDayOfMonth info would be required just for doing this kind of functionality.
The database schema required for this is simple in comparison with the code required to work out where repeats fall.
Problem
I'm trying to decide on the best way to store event times in a MySQL database. These should be as flexible as possible and be able to represent "single events" (starts at a certain time, does not necessarily need an end time), "all day" and "multi day" events, repeating events, repeating all day events, possibly "3rd Saturday of the month" type events etc. Please suggest some tried and proven database schemes.