List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:January 11 2012 6:39pm
Subject:Re: trick trigger
View as plain text  
Hello John,

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

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.

Summary :
* 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 
different process.

* Keep the business logic in your application, leave the data integrity 
rules to the database.

Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
trick triggerJohn G. Heim11 Jan
  • Re: trick triggerMySQL)11 Jan
    • Re: trick triggerClaudio Nanni16 Jan
  • Re: trick triggerKaren Abgarian16 Jan