List:General Discussion« Previous MessageNext Message »
From:John G. Heim Date:January 11 2012 4:16pm
Subject:trick trigger
View as plain text  
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.



Thread
trick triggerJohn G. Heim11 Jan
  • Re: trick triggerMySQL)11 Jan
    • Re: trick triggerClaudio Nanni16 Jan
  • Re: trick triggerKaren Abgarian16 Jan