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
