Abfrage ob geplant

[brandm]

Guten Morgen alle zusammen

ich habe folgende Frage, da ich doch etwas verzweifle.

Ich habe eine Tabelle mit Terminen.

Ich möchte prüfen ob ein Mitarbeiter bereits für eine Zeitspanne eingeplant ist.

Die Tabelle sieht so aus:
`ID` INT(11) NOT NULL AUTO_INCREMENT,

`personalid` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
`personalname` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
`start_datum` DATE NULL DEFAULT NULL,
`start_zeit` TIME NULL DEFAULT NULLL,
`ende_zeit` TIME NULL DEFAULT NULL,

Jetzt bin ich dabei eine Abfrage zu erstellen die genau Prüft ob:

die `personalid` bereits im Zeitraum `start_zeit` -> `ende_zeit` in einem anderen Termin ist.

Ich könnte dies per PHP lösen, aber ich denke mir, dass des über eine DB Afrage schneller und effizienter ist.


Grüße

 

[akretschmer]

Du hast zwar MySQL, aber ich zeige Dir mal eine sehr effektive Lösung mit einer anderen Datenbank:

postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create table brandm(p_id int, von_bis tsrange, exclude using gist(p_id with =, von_bis with &&));
CREATE TABLE
postgres=# insert into brandm values (1, '[2022-05-31 10:00:00,2022-05-31 12:00:00)');
INSERT 0 1
postgres=# select * from brandm ;
 p_id |                    von_bis                    
------+-----------------------------------------------
    1 | ["2022-05-31 10:00:00","2022-05-31 12:00:00")
(1 row)

Wir haben nun für person_id = 1 einen Termin heute 10 bis 12 Uhr.

Nun wollen wir wissen, ob er von 11 bin 13 frei ist - ist er nicht, hat ja von 10-12 einen Termin:

postgres=# select 1 from brandm where p_id = 1 and '[2022-05-31 11:00:00,2022-05-31 13:00:00)' && von_bis;
 ?column? 
----------
        1
(1 row)

Da ist also ein Eintrag. Wie sieht es von 15-16 Uhr aus?

postgres=# select 1 from brandm where p_id = 1 and '[2022-05-31 15:00:00,2022-05-31 16:00:00)' && von_bis;
 ?column? 
----------
(0 rows)

Der Constraint dient nicht nur der Abfrage, ob sich was überlappt, er verhindert es auch:

postgres=# insert into brandm values (1, '[2022-05-31 11:00:00,2022-05-31 13:00:00)');
ERROR:  conflicting key value violates exclusion constraint "brandm_p_id_von_bis_excl"
DETAIL:  Key (p_id, von_bis)=(1, ["2022-05-31 11:00:00","2022-05-31 13:00:00")) conflicts with existing key (p_id, von_bis)=(1, ["2022-05-31 10:00:00","2022-05-31 12:00:00")).
postgres=# insert into brandm values (1, '[2022-05-31 15:00:00,2022-05-31 16:00:00)');
INSERT 0 1
postgres=#

Und es ist sehr schnell alles, da Indexunterstützt:

postgres=# \d brandm
               Table "public.brandm"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 p_id    | integer |           |          | 
 von_bis | tsrange |           |          | 
Indexes:
    "brandm_p_id_von_bis_excl" EXCLUDE USING gist (p_id WITH =, von_bis WITH &&)

Mit etwas Aufwand bekommst Du die Prüfung auch in MySQL hin, mit etlichen Checks ob es sich überschneidet oder innerhalb liegt etc., den EXCLUSION CONSTRAINT, der eine Mehrfachbuchung verhindert, bekommst in MySQL so nicht hin.

 

[ukulele]

Also eine Abfrage ist ja erstmal leicht:

SELECT * FROM tabelle
WHERE ID = @id
AND start_datum = @datum
AND ( start_zeit BETWEEN @start AND @ende
OR ende_zeit BETWEEN @start AND @ende )

Als erstes stört mich die Einschränkung auf genau einen Tag. Das finde ich vom Tabellendesign her schlecht auch wenn keine Tagesübergreifenden Termine statt finden irgendwann rächt sich das. Ich würde daher Start und Ende als DATETIME definieren. Dann musst du sicherstellen das du als Variablen (die musst du für eine Abfrage vor dem Insert ja haben oder willst du auf vorhandene Überscheidungen innerhalb der Tabelle prüfen?) nicht eventuell auch NULL übergibst bzw. die Möglichkeit entsprechend berücksichtigen.

Wenn die Datenbank die Prüfung beim Insert durchführen soll geht das mit MySQL mit einem Trigger, Check-Constraints kann man glaube ich immer noch getrost vergessen.

 

[Walter]

Das ganze Tabellendesign ist fragwürdig, auch personalid und personalname in einer Bewegungstabelle...

 

[brandm]

Das ganze Tabellendesign ist fragwürdig, auch personalid und personalname in einer Bewegungstabelle...

Kannst du mir das mal näher erläutern?

 

[akretschmer]

Wenn da eine personalid ist, dann sollte das ein Foreign Key auf eine Personalstammdatentabelle sein. Dann hat aber der Personalname nichts mehr in dieser Tabelle zu suchen. Ich sehe auch keinen PRIMARY KEYm und die Zertrümmerung eines Timestamp-Feldes in datum und Zeit ist auch Murks. Wenn die Zeitfelder DEFAULT NULL sind wird auch ein Vergleich mit Zeiten Murks, wenn da hier und da NULLen auftauchen. Da MySQL keine Check-Constraints kennt, kannst Du auch nicht via Constraint sicherstellen, daß die Ende-Zeit NACH dem Start ist, was wieder eine ganze Latte potentieller Fehler ermöglicht.

Reicht das als Erklärung?

 

[brandm]

Reicht das als Erklärung?

Ja Danke.

Die ID ist mein PrimaryKey.
Aber ich schreibe in die Daten für Datum und Zeit ja meine Werte rein, wenn ich einen neuen Termin anlege.

 

[akretschmer]

Aber ich schreibe in die Daten für Datum und Zeit ja meine Werte rein, wenn ich einen neuen Termin anlege.

Ja. Und wenn es mal nicht (vollständig) klappt? Dann hast Du halt auch NULL drin. Und wenn Start und Ende nicht harmonieren, werden spätere Vergleiche damit schwer. Und wenn mal ein Termin über 2-3 Tage geht wird es ganz knifflig ...

 

[brandm]

Meine Termine sind nur innerhalb der Zeit von 6-22 Uhr.
Ausserdem prüfe ich über PHP die Eingaben zum Termin in dem ich die Dateneintrage und per Array, abfrage und vergleiche. Sollte etwas nicht stimmen, dann wird das ganze nochmals einem update mit den eingetragenene Zeiten unterzogen.

Das hab ich schon bedacht.

Deswegen passt das schon soweit.