This is really more of a design or best practice question than strictly a Yii question.
One of the things my app does is allow you to assign scheduled tasks to your staff and monitor their progress. I’m looking for some advice on how to implement the scheduling frequency piece.
What I’ve done so far is looked at some apps I use commonly to see how it is implemented. In my ‘assignment’ table I’ve setup freqNo and freqInt columns that would allow the following schedules. Every:
freqNo freqInt
1 week
1 month
1 day
I’m feeling this isn’t the best approach. I don’t easily see how to deal with ‘ONE TIME’ assignments (other than setting up a separate column called isOneTime or something).
I don’t really want to setup a fixed table where all future dates are saved either. What I’m really looking to do is setup something like Outlook calendar recurrence. Any guidance much appreciated!
Sorry, I don’t have tables yet. I am struggling with how to start modeling this.
The way I see it, with scheduling tasks you can approach this one of two ways - to persist or to calculate:
Store a schedule of event data in the database. For every task assigned, there will be data in related tables that has a fixed future schedule associated with it.
Pros:
Easy to determine if an assigned task is missed (no completion for an assigned date)
Easy to display future tasks.
Cons:
Cannot be ‘infinite’ - you need a reasonable end date.
Need to build additional tables and relations.
Do not persist the event storage. Calculate it based on the frequency parameters.
Pros:
Does not need separate association tables for events to assignments (frequency is attribute of assignment)
Cons:
More code to determine whether an assignment was met during a particular time period
It seems you got pros and cons, now if coding ability it’s not a problem for you you should also think about performance:
it’s relatively expensive to do a SQL query
it’s much more expensive to do many SQL queries (so try to get all the data you need in one query and process it with php) check the use of “with” and “togheter” in active record relations, and the difference between lazy loading and eager loading; if necessary use directly a SQL query with rich use of joins
it’s not expensive at all to manipulate datas in php. objects are a bit heavier than arrays, but nothing compared to a sql query
writing/updating records is much more expensive than reading, expecially when you use data replication (=one server for INSERT/UPDATE queries, many for SELECT)
