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 Friday.

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.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql