2
\$\begingroup\$

We had an assignment to create a database for a fictional nursing service. To get accustomed to stored procedures we should implement one of our own choice. I decided to try my hands at writing a procedure to create a sort of workplan for employees for a given day, taking into account vacation days and driving times, trying to optimize the plan by letting a single employee visit all patients in the same street.

The data model is simplified in the way, that the address is only consisting of the street name and a number, assuming that the home care service only operated in a single city, where street names are unique.

The result should contain the used vehicle, the responsible employee, the bought service this tour satisfying and of course the date and time of the tour. The time referenced the time the employee is supposed to arrive at the patient.

Sadly, the database itself needs to contain german table and column names. I tried to provide the necessary translations, but if it is still completely unclear please feel free ask for additional clarification and I will either add add more extensive translations or, if that's not enough, delete the question and repost it with with a translated database, to avoid changing the code after posting the question.

For translations of relevant names please see below.

This is the SQL Statement, which created the result table "tour":

CREATE TABLE `tour` (
  `Fahrzeug` int(11) NOT NULL,
  `Mitarbeiter` int(11) NOT NULL,
  `Datum` date NOT NULL,
  `Uhrzeit` time NOT NULL,
  `TNr` int(11) NOT NULL AUTO_INCREMENT,
  `Bewertung` int(11) DEFAULT NULL,
  `Verweilzeit` int(11) DEFAULT NULL,
  `BNr` int(11) DEFAULT NULL,
  PRIMARY KEY (`TNr`),
  KEY `tour_fahrzeug_FNr_fk` (`Fahrzeug`),
  KEY `tour_mitarbeiter_PNr_fk` (`Mitarbeiter`),
  KEY `tour_gebuchteleistung_BNr_fk` (`BNr`),
  CONSTRAINT `tour_fahrzeug_FNr_fk` FOREIGN KEY (`Fahrzeug`) REFERENCES `fahrzeug` (`FNr`),
  CONSTRAINT `tour_gebuchteleistung_BNr_fk` FOREIGN KEY (`BNr`) REFERENCES `gebuchteleistung` (`BNr`),
  CONSTRAINT `tour_mitarbeiter_PNr_fk` FOREIGN KEY (`Mitarbeiter`) REFERENCES `mitarbeiter` (`PNr`)
) ENGINE=InnoDB AUTO_INCREMENT=391 DEFAULT CHARSET=utf8

Code

The following is the actual code I want reviewed. I'm sure there are a lot of things to improve, as I have very little experience with MySQL and SQL in general, and frankly don't really like that language. The code works as intended. This is also not really performance sensitive so perfomance is a secondary goal. For a short summary see below.

CREATE PROCEDURE `planTours`(IN `datum` DATE)
  BEGIN
    DECLARE responsibleEmployee INT;
    DECLARE iStreet VARCHAR(30);
    DECLARE iPatient INT;
    DECLARE isPatientFirstOneInThisStreet BOOL DEFAULT TRUE;
    DECLARE driveTime INT DEFAULT 0;
    DECLARE endTimeOfLastVisit TIME DEFAULT '8:00';
    DECLARE iService INT;
    DECLARE visitDuration INT;

    -- Clean up in case something went wrong in a previous run
    DROP TABLE IF EXISTS servicesToBePerformedAtThisPatient;
    DROP TABLE IF EXISTS patientsToVisitInThisStreet;
    DROP TABLE IF EXISTS streetsToVisit;
    DROP TABLE IF EXISTS availableVehicles;
    DROP TABLE IF EXISTS availableEmployees;
    DROP TABLE IF EXISTS servicesToPlan;

    CREATE TEMPORARY TABLE servicesToPlan (
      BNr     INT,
      service INT,
      patient INT
    );

    CREATE TEMPORARY TABLE availableEmployees (
      PNr     INT,
      workload INT DEFAULT 0,
      vehicle INT
    );

    CREATE TEMPORARY TABLE availableVehicles (
      VehicleID INT
    );
    INSERT INTO availableVehicles (SELECT FNr
                                     FROM fahrzeug);

    CREATE TEMPORARY TABLE streetsToVisit (
      street VARCHAR(30)
    );

    CREATE TEMPORARY TABLE patientsToVisitInThisStreet (
      PNr INT
    );

    CREATE TEMPORARY TABLE servicesToBePerformedAtThisPatient (
      BNr INT
    );

    -- get available employees
    INSERT INTO availableEmployees (PNr, workload)
      SELECT
        M.PNr,
        0
      FROM mitarbeiter M
      WHERE M.Abteilung = 'Pflege'
            AND M.PNr NOT IN (SELECT U.PNr
                              FROM urlaubstag U
                              WHERE U.Datum = datum);

    -- reset tours of this day
    DELETE FROM tour
    WHERE tour.Datum = datum;

    -- Get all tours that need to be made
    INSERT INTO servicesToPlan (BNr, service, patient)
      SELECT
        B.BNr,
        B.Leistung,
        B.Patient
      FROM gebuchteleistung B, person P
      WHERE B.Patient = P.PNr
            AND datum BETWEEN B.Von AND B.Bis
      ORDER BY Patient, P.strasse;

    INSERT INTO streetsToVisit (street)
      SELECT person.strasse
      FROM servicesToPlan, person
      WHERE servicesToPlan.patient = person.PNr;

    -- Iterate streets that need to be visited
    WHILE (SELECT COUNT(*)
           FROM streetsToVisit) > 0 DO
      -- get current street
      SET iStreet = (SELECT *
                     FROM streetsToVisit
                     ORDER BY street
                     LIMIT 1);
      DELETE FROM streetsToVisit
      WHERE streetsToVisit.street = iStreet;

      SET isPatientFirstOneInThisStreet = TRUE;

      -- Calculate responsible employee
      SET responsibleEmployee = (SELECT PNr
                                 FROM availableEmployees
                                 ORDER BY availableEmployees.workload
                                 LIMIT 1);

      -- Assign Vehicle if not yet assigned to employee
      IF (SELECT availableEmployees.vehicle
          FROM availableEmployees
          WHERE availableEmployees.PNr = responsibleEmployee) IS NULL
      THEN
        UPDATE availableEmployees
        SET availableEmployees.vehicle = (SELECT VehicleID
                                             FROM availableVehicles
                                             LIMIT 1)
        WHERE PNr = responsibleEmployee;

        -- Remove Vehicle from pool
        DELETE FROM availableVehicles
        WHERE VehicleID = (SELECT vehicle
                     FROM availableEmployees
                     WHERE PNr = responsibleEmployee);

        -- If Pool is now empty, clear all remaining employees
        IF (SELECT COUNT(*)
            FROM availableVehicles) = 0
        THEN
          DELETE FROM availableEmployees
          WHERE vehicle IS NULL;
        END IF;
      END IF;

      -- Get all patients in the current street
      INSERT INTO patientsToVisitInThisStreet (PNr)
        SELECT DISTINCT U.patient
        FROM servicesToPlan U, person
        WHERE U.patient = person.PNr
              AND person.strasse = iStreet;

      -- Iterate patients
      WHILE (SELECT COUNT(*)
             FROM patientsToVisitInThisStreet) > 0 DO

        -- get current patient and remove them from pool
        SET iPatient = (SELECT PNr
                        FROM patientsToVisitInThisStreet
                        LIMIT 1);
        DELETE FROM patientsToVisitInThisStreet
        WHERE PNr = iPatient;

        -- Calculate drive time
        IF isPatientFirstOneInThisStreet
        THEN
          SET driveTime = (SELECT anfahrtzeit
                           FROM anfahrtzeiten
                           WHERE anfahrtzeiten.straße = iStreet
                           LIMIT 1);
          SET isPatientFirstOneInThisStreet = FALSE;
        ELSE
          SET driveTime = 5;      -- choose 5 as an estimate to switch patients in the same street
        END IF;

        -- get serives, that need to be performed at that patient
        INSERT INTO servicesToBePerformedAtThisPatient (BNr)
          SELECT BNr
          FROM servicesToPlan
          WHERE servicesToPlan.patient = iPatient;

        -- Iterate services
        WHILE (SELECT COUNT(*)
               FROM servicesToBePerformedAtThisPatient) > 0 DO

          -- get current service and remove from pool
          SET iService = (SELECT BNr
                          FROM servicesToBePerformedAtThisPatient
                          LIMIT 1);
          DELETE FROM servicesToBePerformedAtThisPatient
          WHERE BNr = iService;

          -- calculate end of the last visit of this employee
          IF (SELECT COUNT(*)
              FROM tour
              WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee) > 0
          THEN
            SET visitDuration = (SELECT L.Dauer
                                 FROM gebuchteleistung B, leistung L
                                 WHERE B.BNr = (SELECT BNr
                                                FROM tour
                                                WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee
                                                ORDER BY Uhrzeit DESC
                                                LIMIT 1)
                                       AND B.Leistung = L.LNr
                                 LIMIT 1);

            SET endTimeOfLastVisit = ADDTIME((SELECT tour.Uhrzeit
                                              FROM tour
                                              WHERE tour.Datum = datum AND tour.Mitarbeiter = responsibleEmployee
                                              ORDER BY Uhrzeit DESC
                                              LIMIT 1),
                                             MAKETIME(FLOOR(visitDuration / 60), visitDuration % 60, 0));
          ELSE
            SET endTimeOfLastVisit = MAKETIME(8, 0, 0);
          END IF;

          -- Add tour to database
          INSERT INTO tour (Fahrzeug, Mitarbeiter, Datum, Uhrzeit, BNr) VALUES (
            (SELECT vehicle
             FROM availableEmployees
             WHERE PNr = responsibleEmployee
             LIMIT 1),
            responsibleEmployee,
            datum,
            ADDTIME(endTimeOfLastVisit, MAKETIME(FLOOR(driveTime / 60), driveTime % 60, 0)),
            iService
          );

          UPDATE availableEmployees
          SET workload = (workload + driveTime + (SELECT L.Dauer
                                                FROM leistung L, gebuchteleistung B
                                                WHERE L.LNr = B.Leistung AND B.BNr = iService
                                                LIMIT 1))
          WHERE PNr = responsibleEmployee;

          SET driveTime = MAKETIME(0, 0,
                                   0); -- set drive to 0 in case additional services need to be made at this exast patient
        END WHILE;
        TRUNCATE servicesToBePerformedAtThisPatient;

      END WHILE;

      TRUNCATE patientsToVisitInThisStreet;
    END WHILE;

    DROP TABLE IF EXISTS servicesToBePerformedAtThisPatient;
    DROP TABLE IF EXISTS patientsToVisitInThisStreet;
    DROP TABLE IF EXISTS streetsToVisit;
    DROP TABLE IF EXISTS availableVehicles;
    DROP TABLE IF EXISTS availableEmployees;
    DROP TABLE IF EXISTS servicesToPlan;
  END

Recap of functionality: Essentially I have 3 nested loops, one iterating over distinct streets that need to be visited, one looping over the patients in this street and one lopping over the services that need to be performed at the patient. I assign the street to the employee with the smallest workload already assigned to them to equalize total workload among employees (This is a variant of the partition problem and a greedy, non optimal approximation algorithm).

Then the patients in the currently handled street are iterated. Only for the first one the drive time is taken into account, every subsequent tour in this street is approximated with 5 minutes drive time. Every subsquent "tour" to the same patient (as the may have ordered multiple services for the same day) is calculated without drive time.

The final time for a tour is the end of the last visit plus the drive time. For each tour the drive time and the expected presence time is added to the workload of the responsible employee.

Database Environment

The relevant tables, that are queried here are created with the following statements:

gebuchteleistung (bought service):

CREATE TABLE `gebuchteleistung` (
  `BNr` int(3) NOT NULL AUTO_INCREMENT,
  `Leistung` int(2) NOT NULL,
  `Von` date NOT NULL,
  `Bis` date NOT NULL,
  `Buchungsdatum` date NOT NULL,
  `Patient` int(3) NOT NULL,
  PRIMARY KEY (`BNr`),
  KEY `gebuchteleistung_leistung_LNr_fk` (`Leistung`),
  KEY `gebuchteleistung_patient_PNr_fk` (`Patient`),
  CONSTRAINT `gebuchteleistung_leistung_LNr_fk` FOREIGN KEY (`Leistung`) REFERENCES `leistung` (`LNr`),
  CONSTRAINT `gebuchteleistung_patient_PNr_fk` FOREIGN KEY (`Patient`) REFERENCES `patient` (`PNr`)
) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=utf8

(Leistung=service, Von=startdate, Bis=enddate)


Leistung (service):

CREATE TABLE `leistung` (
  `LNr` int(2) NOT NULL AUTO_INCREMENT,
  `Name` varchar(27) DEFAULT NULL,
  `Dauer` int(11) DEFAULT NULL,
  `Eigenanteil` decimal(2,2) DEFAULT NULL,
  `Tagespreis` int(2) DEFAULT NULL,
  PRIMARY KEY (`LNr`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

(Dauer=expected Duration)


Patient:

CREATE TABLE `patient` (
  `PNr` int(3) NOT NULL,
  `Krankenkasse` int(2) NOT NULL,
  PRIMARY KEY (`PNr`),
  KEY `patient_krankenkasse_KNr_fk` (`Krankenkasse`),
  CONSTRAINT `patient_krankenkasse_KNr_fk` FOREIGN KEY (`Krankenkasse`) REFERENCES `krankenkasse` (`KNr`),
  CONSTRAINT `patient_person_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `person` (`PNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(Krankenkasse=health insurance, not relevant in this procedure)


Person

CREATE TABLE `person` (
  `PNr` int(3) NOT NULL AUTO_INCREMENT,
  `Vorname` varchar(11) NOT NULL,
  `Nachname` varchar(10) NOT NULL,
  `Telefonnummer` varchar(17) DEFAULT NULL,
  `Email` varchar(33) DEFAULT NULL,
  `strasse` varchar(30) DEFAULT NULL,
  `hausnummer` int(11) DEFAULT NULL,
  PRIMARY KEY (`PNr`)
) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8

(Vorname = first name, Nachname = last name, strasse = street, hausnumber = street number)


Anfahrtzeit (driving time):

CREATE TABLE `anfahrtzeiten` (
  `straße` varchar(30) NOT NULL,
  `anfahrtzeit` int(11) DEFAULT NULL,
  PRIMARY KEY (`straße`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(straße = street, anfahrtzeit = drive time)


Mitarbeiter (employee):

CREATE TABLE `mitarbeiter` (
  `PNr` int(1) NOT NULL,
  `Stundenlohn` decimal(4,2) NOT NULL,
  `Einstelldatum` date NOT NULL,
  `Abteilung` varchar(10) NOT NULL,
  PRIMARY KEY (`PNr`),
  CONSTRAINT `mitarbeiter_person_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `person` (`PNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(Stundenlohn = salary, Einstelldatum = employment date, Abteilung = department (The value "Pflege" means "care" and is queried in the code))


Urlaubstag (vacation day):

CREATE TABLE `urlaubstag` (
  `PNr` int(1) NOT NULL,
  `Datum` varchar(10) NOT NULL,
  PRIMARY KEY (`PNr`,`Datum`),
  CONSTRAINT `urlaubstag_mitarbeiter_PNr_fk` FOREIGN KEY (`PNr`) REFERENCES `mitarbeiter` (`PNr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(Datum=date)


Fahrzeug (vehicle):

CREATE TABLE `fahrzeug` (
  `FNr` int(1) NOT NULL AUTO_INCREMENT,
  `Modell` varchar(10) DEFAULT NULL,
  `Kennzeichen` varchar(10) DEFAULT NULL,
  `Baujahr` int(4) DEFAULT NULL,
  `Letzte Inspektion` int(4) DEFAULT NULL,
  PRIMARY KEY (`FNr`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

(Modell = model name, Kennzeichen = licence plate, Baujahr = year of construction, Letzte Inspektion = last maintenance)

What I want reviewed

As I said before, I don't have much experience with SQL (although I have a lot experience with other languages, so you don't need to go easy on me), thus I could need a review of everything. I'm not sure if the temporary tables I create are a good way to achieve what I want to do or if there are cleaner ways. I'm also not really satisfied by the way I iterate through the different sets (streets, patients and services) and would like to know if there are cleaner ways. I would also appreciate if you could point out places where additional comments would be helpful.

If you have comments about the database structure please tell me.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.