On 1/11/2012 11:16, John G. Heim wrote:
> I am working on an app to allow a committee to schedule classes. The
> members of the committee can all update the database by changing the
> time or the instructor for a class. I have to write an app to warn them
> when they've scheduled an instructor for 2 classes at the same time or
> if they've scheduled any of a large list of classes at the same time.
> For example, they shouldn't schedule Calculus 212 at the same time as
> Physics 302 because a student might want to take both classes. And
> obviously, they shouldn't schedule Professor Higgenbothom to teach both
> Calculus 212 and Probability 278 at 10:00 AM on Monday, Wednesday, and
> The problem isn't actually writing mysql to select the conflicts. The
> problem is when and how to run the code. I could put it in a trigger but
> say someone assigns Dr. Higgy to teach Calc 212 at 10 AM MWF. They need
> to be able to see that he is now scheduled for another class if they
> look at Probability 278. Get the problem? An update to one record can
> necessitate an update to any number of other records.
> I'm just looking for basic suggestions on how you'd deal with this.
> Should I attempt to write a trigger that updates both Calc 212 and
> Physics 302 when either is changed? Am I going to create an infinate
> loop? I am thinking of telling the committee that it can't be done and
> they'll have to wait for the list of conflicts to be recalculated by a
> background process once an hour or so.
> My current database structure is that there is a link table for
> conflicts. If Calc 212 is scheduled at the same time as Physics 302,
> that is shown by there being 2 records in a conflicts table. The
> conflicts table would contain a record with the primary key for Calc
> 212, the pkey for Physics 302, and a code indicating that its a course
> conflict. There'd also be a record for Physics 302 indicating that it
> has a conflict with Calc 212. If Prof. Higgy is also scheduled to tach
> Calc 212 and Probability 278 at the same time, that would also create 2
> records in the conflicts table. Like this:
> calc212 | phys302 | course_conflict
> phys302 | calc212 | courseConflict
> calc212 | prob278 | instructorConflict
> prob278 | calc212 | instructorConflict
> Then my web app can do a select for conflicts when displaying Calc 212,
> Probabbility 278, or Physics 302. But how to get that data into the
> table? I'm thinking of trying to write a trigger so that wen a class
> record is updated, the trigger deletes the conflicts records for the
> class if the id appears in either column 1 or column 2, re-calculate
> conflicts, and re-add the conflicts records. But if anybody has basic
> suggestions for a completely different approach, I'd like to hear them.
This is all a matter of GUI design and application logic. For example,
you could force the user to wait for some kind of database error before
realizing that the data they just entered was invalid or you can
pre-select conflict lists from the database and block out certain times
and people as 'already used' before they make their selections. This
requires your application to check with the database at certain events.
Let's say you want to schedule a class for Higgy to teach Calc 212, well
there are at least two lists, from your description, that you need to
know before allowing the user to pick a date and time:
1) the list of all classes that Higgy is already teaching
2) the list of any other classes that might interfere with Calc 212
Some additional lists may also be useful
* Any other Calc 212 sections already scheduled for other professors
* Any 'no classes here' schedule preferences for Higgy
* The list of teaching areas that may be available/unavailable in which
your Calc 212 may be taught.
These all need to be added to the logic present at the time the
scheduler wants to make their choices so that they can avoid many
un-necessary trips to the database for every schedule they want to create.
Another thing to do is to temporarily block (not with a database-level
transaction) access to both Higgy and Calc 212 to minimize the chance of
conflicting with the changes made to the database by someone else also
trying to enter scheduling information.
* Get as much data as you can get before the request leaves the user.
This frees up the database to handle just the data changes as they need
to happen. Conflicts can still exist (always assume someone else may
steal the room, for example) and those may need to be resolved through a
* Keep the business logic in your application, leave the data integrity
rules to the database.
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN