[PHP/MySQL] Planningssysteem

[Freshnezz]

Beste tweakers,

ik ben bezig met een planningssysteem in PHP waarbij er medewerkers op klanten kunnen worden ingepland.
Dit kunnen eenmalige, wekelijkse en zelfs dagelijkse afspraken zijn.

Versimpeld zien deze afspraken er in de database als volgt uit:
- afspraken(afspraak_id, klant_id, medewerker_id, start_unix, eind_unix);

Nu moet er bij het inplannen van een afspraak gecheckt worden welke medewerkers er beschikbaar zijn (dus niet al ingepland zijn op hetzelfde tijdstip). Om dit te realiseren vraag ik het aantal overlappende afspraken op die binnen de start- en eindtijd van de nieuwe afspraak valt

Dit wordt in de huidige situatie gedaan d.m.v. één query

Bijvoorbeeld bij een enkele afspraak:
- SELECT count(afsprk_id), medewerker_id FROM afspraken WHERE 'nieuwe_start_unix' < eind_unix AND 'nieuwe_eind_unix> start_unix ;

Dit is dus de situatie bij een eenmalige afspraak, simpel dus.

Nu komt als volgt het probleem dat ik tegenkom; als er een afspraak wordt ingepland die wekelijks/dagelijks voor komt, kan deze query erg groot worden (omdat er per dag gekeken moet worden of er een afspraak binnen de tijdstippen valt).

Bijvoorbeeld:

SELECT count(afsprk_id), medewerker_id FROM afspraken WHERE ('dag1_start_unix' < eind_unix AND 'dag1_eind_unix> start_unix) OR ('dag2_start_unix' < eind_unix AND 'dag2_eind_unix> start_unix) OR ('dag3_start_unix' < eind_unix AND 'dag3_eind_unix> start_unix) OR ('dag4_start_unix' < eind_unix AND 'dag4_eind_unix> start_unix) etc tot de einddatum....

Dit werkt gewoon goed totdat de afspraken tabel goed gevuld is (boven de 100.000 rijen) en er veel verschillende medewerkers zijn.

De query die ontstaat doordat er bijvoorbeeld een dagelijkse afspraak wordt ingepland die tot december 2012 doorloopt zal zoals je ziet erg groot worden.

De query zal er dan erg lang over doen om de overlappingen op te vragen. In de praktijk kan dit bijna een minuut duren voordat de overlappingen zijn opgevraagd.

Nu is mijn vraag of er geen efficientere methode is om de overlappingen op te vragen dan deze hierboven omschreven? Op deze manier wordt de query namelijk wel erg groot. Maar het is toch nog altijd beter dan een heleboel minder grote queries?

[TheLunatic]

Misschien denk ik te simpel, maar waarom voer je niet twee checks uit: eentje om te kijken of er overlap is met een niet-herhalende afspraak en eentje om te kijken of er overlap is met een wekelijkse afspraak. In de eerste check gebruik je datum+tijd, in de tweede check gebruik je alleen dagnummer (1 t/m 7 voor ma t/m zo) + tijd. Moet je in je database uiteraard wel onderscheid kunnen maken tussen herhalende en niet-herhalende afspraken. Voordeel daarvan is dat een herhalende afspraak dan maar één record heeft.

[Freshnezz]

TheLunatic schreef op donderdag 19 februari 2009 @ 12:42:
Misschien denk ik te simpel, maar waarom voer je niet twee checks uit: eentje om te kijken of er overlap is met een niet-herhalende afspraak en eentje om te kijken of er overlap is met een wekelijkse afspraak. In de eerste check gebruik je datum+tijd, in de tweede check gebruik je alleen dagnummer (1 t/m 7 voor ma t/m zo) + tijd. Moet je in je database uiteraard wel onderscheid kunnen maken tussen herhalende en niet-herhalende afspraken. Voordeel daarvan is dat een herhalende afspraak dan maar één record heeft.

Herhalende afspraken staan in de database per dag als record in. Dus een herhalende dagelijkse afspraak van 4 weken heeft dus 28 entries in de database...

Dagnummer is geen optie omdat de datum+tijd als unix in de database staan, maar ik vraag mij dan af of de query sneller zal gaan als ik de desbetreffende dagnummer mee opvraag?

[Guillome]

Is dat wel verstandig om per herhaling een record te hebben? Zijn ze nog wel aan elkaar gelinkt op de een of andere manier? Lijkt mij persoonlijk geen goede opbouw. Kan denk ik beter met een soort formule.
Startdag = unix timestamp, bijv. 10 feb 2009. En herhaling per dag, week, maand, zoveelste maandag etc. En dan uitrekenen geblazen

[TheLunatic]

Je zou inderdaad moeten beperken. Herhaling beperken tot een aantal niveau's: week, twee weken, maand, jaar. Een afspraak die twee keer per week plaatsvindt heeft twee records met herhaling op weekniveau.

Je argument datum+tijd als unix gaat niet op, daar kun je natuurlijk makkelijk een dagnummer uit ophalen, zeker met php en vast ook met MySQL.

Je eigen voorbeeld, een dagelijkse afspraak over 4 weken, heeft wat mij betreft de volgende data:
- begindatum: maandag 2 februari 2009
- einddatum: zondag 1 maart 2009
- begintijd: 15:00
- eindtijd: 16:30
- herhaling: dagelijks

Voor je check vergelijk je dan je nieuwe records tegen dit bestaande records: als je nieuwe begindatum/einddatum binnen de bestaande valt en de tijd ook nog eens overlapt, dan heb je overlap. Klaar. Eén query.

Freshnezz schreef op donderdag 19 februari 2009 @ 12:54:
[...]

Dagnummer is geen optie omdat de datum+tijd als unix in de database staan, maar ik vraag mij dan af of de query sneller zal gaan als ik de desbetreffende dagnummer mee opvraag?

Je bent toch nog in de ontwikkelfase? Dan kun je je database toch aanpassen mocht dat nodig zijn?

[ Voor 20% gewijzigd door TheLunatic op 19-02-2009 13:16 ]

[Freshnezz]

Guillome schreef op donderdag 19 februari 2009 @ 13:02:
Is dat wel verstandig om per herhaling een record te hebben? Zijn ze nog wel aan elkaar gelinkt op de een of andere manier? Lijkt mij persoonlijk geen goede opbouw. Kan denk ik beter met een soort formule.
Startdag = unix timestamp, bijv. 10 feb 2009. En herhaling per dag, week, maand, zoveelste maandag etc. En dan uitrekenen geblazen

Het staat idd niet bij de beschrijving hierboven maar ja de bijhorende afspraken zijn wel met elkaar gelinkt. In de query wordt al aan de hand van de frequentie de unixtimestamps berekend van de start/eind van de mogelijk overlappende afspraken.

Het werkt gewoon goed, probleem is echt alleen dat de query te traag wordt naarmate het aantal afspraken/klanten/medewerkers toe neemt.

Ik zit trouwens niet in de ontwikkelfase, ik werk aan een al bestaand systeem. (Ik zou sowieso het inplannen ook anders aangepakt hebben).

[ Voor 7% gewijzigd door Freshnezz op 19-02-2009 13:20 ]

[Guillome]

Het werkt gewoon goed

..

probleem is echt alleen dat de query te traag wordt

Dus werkt het niet goed