My two cents are this. This is the kind of problems they invented transactions for. If you find yourself doing this on non-transactional tables, you will need to use a lot of error checking, triggers, application checks and post-cleanups to make it work somehow. Likely, every once in a while you will still have to look at your tables and make sure there is no junk there. The point is, the more natural (and easy) way of resolving it is with transactions.
The idea to do things like lock + check if there are no conflicts + update is also well handled with database procedures. Again, this is what they were originally invented for. This is an extension of Shawn's and Claudio's ideas about letting the database handle integrity. If both transactions and procedures are applied, the resolution will likely become trivial. The app will call the procedure, the procedure will lock the record in question, make checks, carry out updates and return success/failure codes. On Jan 16, 2012, at 1:18 AM, Claudio Nanni wrote: > Just adding an extra note to the already good Shawn response. > > Theoretically this is just as any booking system that needs to be run in > transactions (db or not). > What you are doing is not trivial if that makes you feel better. > The problem is basically that the web interface is asynchronous so you have > to 'watch' your transaction in some way. > Ad Shawn says you might collect all needed data on the user, check and > eventually book on server or return the conflict. > > @Shawn Isn't data integrity constraints part of the business logic? > > Cheers > > Claudio > On Jan 11, 2012 7:40 PM, "Shawn Green (MySQL)" <shawn.l.gr...@oracle.com> > wrote: > >> 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 >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
