user generated / user specific functions

Question

I'm looking for the most elegant and secure method to do the following.

I have a calendar, and groups of users.

Users can add events to specific days on the calendar, and specify how long each event lasts for.

I've had a few requests from users to add the ability for them to define that events of a specific length include a break, of a certain amount of time, or require that a specific amount of time be left between events.

For example, if event is >2 hours, include a 20min break. for each event, require 30 minutes before start of next event.

The same group that has asked for an event of >2 hours to include a 20 min break, could also require that an event >3 hours include a 30 minute break.

In the end, what the users are trying to get is an elapsed time excluding breaks calculated for them. Currently I provide them a total elapsed time, but they are looking for a running time.

However, each of these requests is different for each group. Where one group may want a 30 minute break during a 2 hour event, and another may want only 10 minutes for each 3 hour event.

I was kinda thinking I could write the functions into a php file per group, and then include that file and do the calculations via php and then return a calculated total to the user, but something about that doesn't sit right with me.

Another option is to output the groups functions to javascript, and have it run client-side, as I'm already returning the duration of the event, but where the user is part of more than one group with different rules, this seems like it could get rather messy.

I currently store the start and end time in the database, but no 'durations', and I don't think I should be storing the calculated totals in the db, because if a group decides to change their calculations, I'd need to change it throughout the db.

Is there a better way of doing this? I would just store the variables in mysql, but I don't see how I can then say to mysql to calculate based on those variables.

I'm REALLY lost here. Any suggestions? I'm hoping somebody has done something similar and can provide some insight into the best direction.

If it helps, my table contains

eventid, user, group, startDate, startTime, endDate, endTime, type

The json for the event which I return to the user is

{"eventid":"'.$eventId.'", "user":"'.$userId.'","group":"'.$groupId.'","type":"'.$type.'","startDate":".$startDate.'","startTime":"'.$startTime.'","endDate":"'.$endDate.'","endTime":"'.$endTime.'","durationLength":"'.$duration.'", "durationHrs":"'.$durationHrs.'"}

where for example, duration length is 2.5 and duration hours is 2:30.

Answer 1

Store only the start time and end time for the event, and a BLOB field named notes.

I've worked on several systems that suffered from feature creep of these sorts of requirements until the code and data modeling became nothing but an unmaintainable collection of exception cases. It was a lot of work to add new permutations to the code, and typically these cases were used only once.

If you need enforcement of the rules and conditions described in the notes field, it's actually more cost-effective to hire an event coordinator instead of trying to automate everything in software. A detail-oriented human can adapt to the exception cases much more rapidly than you can adapt the code to handle them.