[Tutor] sqlite3 Dilemma

[Walter Prins]

On 30 July 2012 14:57, Khalid Al-Ghamdi <emailkgnow at gmail.com> wrote:
> Hi All,
>
> I am a teacher at an industrial training center. We have to conduct hands-on
> exams (fixing pumps, etc). I review all the test schedules for all the
> students (made by other teachers) to make sure there are no clashes due to
> lack of ample supply of equipment or tools. Meaning no two trainees are to
> have the same test at the same time. If this is the case, then the date or
> time or test code have to be changed to avoid the conflict.

How exactly is the date or time changed, and by who?  Is the process
always the same or is there discretion involved?

> I have the schedule in csv format. I have been able to export it to sqlite3
> as seen below and I have found all the conflicts, too.
>
> My dilemma is how to go about making sqlite3 alter the date, time, or exam
> code if another trainee is scheduled at the same time and date with the same
> test code.
>
> What is the proper approach to handle this?

It depends on the current process of managing collisions.  If this is
discretionary, then the approach for the program should be one where
your program identifies the collisions, identifies suitable
alternatives and then requests the user to supply the alternate
date/time to resolve the conflict, repeating this for every conflict.
If the collision management rules can be expressed entirely
objectively then it might perhaps be fully automated.  There may also
be a "best effort" type middle road where many/most cases can be
automatically handled while some will require operator input.

By the way, nice job using SQLite to do set based operations.  To move
toward a solution, thinking off the top of my head, you will need to
probably do something along the following lines:
1) For each collision detected in your previous work, find the details
for that specific collision.
2) Determine which of the entries will keep the slot and remove it
from the list of colliding entries
3) Find alternate slots for each of the remaining entries.

3) Will involve something like:
3.1) Determine list of suitable/available slots, suitably ordered
3.2) Update the colliding entry with a new selected date/time
3.3) Repeat for each collision.

So 1) would involve another query, 2) involve input from the user, 3)
likely involves 2 more queries, one for retrieving available slots and
one for updating with a new date/time

Finally I imagine you may want to write the entire lot back out to CSV
(not sure if that's your goal or not.)

Anyway, that's just a few thoughts off the top of my head.

Walter