[SQL] Database opzet en goede queries

[GdeKeijzer]

Hallo,

Ik ben bezig met een verhuur/reserverings pagina voor een intranet te maken.
Zoals elk ontwerk begin ik op papier. Echter loop ik nu tegen een ontwerp-vraagstuk aan.

Ik heb (voorlopig) 4 tabellen:
- tblUser (gebruikers)
- tblObjects (verhuurobjecten)
- tblReservation (verhuur'contracten')
- tblAvailable (beschikbaarheid van objecten, later stadium, eerste instantie is hij 24/7 beschikbaar)

Nu moge duidelijk zijn waarvoor deze tabellen allen zijn. Als er een reservering wordt gedaan zit ik met de vraag of ik elke dag van de reservering (is datum + aantal dagen) een apart record moet maken? Of in 1 record de begin en einddatum.
Dit vraag ik omdat ik wil dat als een object bekeken wordt om dan in een maand overzicht per dag te laten zien of iets wel of niet beschikbaar is.

Voorbeeld:
Reservering object X vanaf 13-3-2007 t/m 17-3-2007.

Maak ik in de tabel tblReservation dan 5 records aan met elke separate dag of 1 record met datum-range?
De eerste optie lijkt me makkelijker voor een maandoverzicht echter kan in de records lopen als e.e.a. lekker loopt.

Wie kan me een duwtje in de goede richting geven?

[MrBucket]

Ik zou de oplossing af laten hangen van de volgende vragen:

1. Voor hoeveel dagen wordt zo'n reservering gemiddeld geplaatst? Bij weinig dagen verdient een record per dag de voorkeur, naarmate het aantal dagen toeneemt kan je beter de range-oplossing gebruiken.
2. Zijn de datumblokken altijd aaneengesloten, of zijn er ook dagen (weekenden, feestdagen, etc.) die 'geblokkeerd' zijn omdat er nooit geen reservering mag plaatsvinden op die dag? Als de datumblokken niet altijd aaneengesloten zijn, dan zou ik voor een record-per-dag gaan.

DutchBoy2000 schreef op zaterdag 24 maart 2007 @ 10:14:
Dit vraag ik omdat ik wil dat als een object bekeken wordt om dan in een maand overzicht per dag te laten zien of iets wel of niet beschikbaar is.

Nou, hoe zou je deze query schrijven?
Stel dat de reservering loopt van 28 Jan tot 4 Maart (geen idee of dat kan), hoe zorg je er dan voor dat je voor het overzicht voor Februari deze reservering getoond wordt?

Da's nog niet makkelijk

[Voutloos]

MrBucket schreef op zaterdag 24 maart 2007 @ 13:24:
Stel dat de reservering loopt van 28 Jan tot 4 Maart (geen idee of dat kan), hoe zorg je er dan voor dat je voor het overzicht voor Februari deze reservering getoond wordt?

Da's nog niet makkelijk

Is wel makkelijk, maar men denkt meestal te moeilijk. Je wil de records waar het begin van de reservering voor het einde van de geselecteerde periode is && het einde van de reservering na het begin van de periode.

[NMe]

Dit lijkt me meer wat voor Software Engineering & Architecture. Zie ook Waar hoort mijn topic?

PRG>>SEA

[GdeKeijzer]

MrBucket schreef op zaterdag 24 maart 2007 @ 13:24:
Ik zou de oplossing af laten hangen van de volgende vragen:

1. Voor hoeveel dagen wordt zo'n reservering gemiddeld geplaatst? Bij weinig dagen verdient een record per dag de voorkeur, naarmate het aantal dagen toeneemt kan je beter de range-oplossing gebruiken.
2. Zijn de datumblokken altijd aaneengesloten, of zijn er ook dagen (weekenden, feestdagen, etc.) die 'geblokkeerd' zijn omdat er nooit geen reservering mag plaatsvinden op die dag? Als de datumblokken niet altijd aaneengesloten zijn, dan zou ik voor een record-per-dag gaan.

[...]

Nou, hoe zou je deze query schrijven?
Stel dat de reservering loopt van 28 Jan tot 4 Maart (geen idee of dat kan), hoe zorg je er dan voor dat je voor het overzicht voor Februari deze reservering getoond wordt?

Da's nog niet makkelijk

Met die casus van 28-1 tot 4-3 zat ik ook in m'n maag wat betreft, hoe je het in februari ziet.

Ik praat over periodes van enkele dagen tot 4 weken (denk aan vakanties).

Waar het om gaat dat ik niet oneindige for-each loops moet maken in m'n sourcecode om per dag te kijken (bij een maandoverzicht) of een object die dag bezet is. Daar zit op dit moment even het probleem.

Voutloos schreef op zaterdag 24 maart 2007 @ 14:34:
[...]
Is wel makkelijk, maar men denkt meestal te moeilijk. Je wil de records waar het begin van de reservering voor het einde van de geselecteerde periode is && het einde van de reservering na het begin van de periode.

Maar hoe voorkom je door bij iedere dag in een maandoverzicht een query te draaien om te zien of die specifieke dag bezet is?

[Gwaihir]

MrBucket schreef op zaterdag 24 maart 2007 @ 13:24:
Zijn de datumblokken altijd aaneengesloten, of zijn er ook dagen (weekenden, feestdagen, etc.) die 'geblokkeerd' zijn omdat er nooit geen reservering mag plaatsvinden op die dag?

Ik mag toch hopen dat een reservering per definitie aaneengesloten is. Niet aaneengesloten -> meerdere reserveringen.

DutchBoy2000 schreef op zaterdag 24 maart 2007 @ 14:57:
Maar hoe voorkom je door bij iedere dag in een maandoverzicht een query te draaien om te zien of die specifieke dag bezet is?

Je draait één query die voor de hele maand de reserveringen voor die kamer ophaalt. Die query vraagt om alle reserveringen van die kamer die eindigen en / of beginnen in die maand. Daarmee vul je je overzicht in.

[Verwijderd]

Sla de reservering zelf op in 1 record, maar houd middels triggers (of desnoods client side) de beschikbaarheid en de toegewezen objecten op dagbasis bij. Die gegevens zijn in principe 100% redundant, maar 't maakt het beheren van de beschikbare objecten veel simpeler en overzichtelijker. En wanneer de database zelf die redundantie beheert is er niks mis mee.

/me programmeert al 9 jaar hotel-software, en dan heb je met hetzelfde vraagstuk te maken.

[GdeKeijzer]

Verwijderd schreef op zondag 25 maart 2007 @ 00:59:
Sla de reservering zelf op in 1 record, maar houd middels triggers (of desnoods client side) de beschikbaarheid en de toegewezen objecten op dagbasis bij. Die gegevens zijn in principe 100% redundant, maar 't maakt het beheren van de beschikbare objecten veel simpeler en overzichtelijker. En wanneer de database zelf die redundantie beheert is er niks mis mee.

/me programmeert al 9 jaar hotel-software, en dan heb je met hetzelfde vraagstuk te maken.

Ok, dus als ik het goed begrijp dan zou je in bv je DB 2 tabellen zetten.
In tabel 1 zet je de gegevens van de reservering. Dit zou ook het contract kunnen zijn.
In tabel 2 zet je de details van de dagen in. Deze link je dan.

De dagen vanuit tabel 2 zijn dan m.i. makkelijk te gebruiken voor een overzicht en voor andere zaken.

Zoiets?

[Verwijderd]

Klopt, en waarschijnlijk is een 3e tabel die op dagbasis de beschikbaarheid bijhoudt ook handig.
Stel, je hebt 20 verhuurobjecten van hetzelfde type. Op het moment van reserveren hoef je dan nog niet 1 specifiek object aan die reservering toe te kennen, dat doe je pas op het moment dat het object in gebruik wordt genomen. Maar je wilt wel graag weten hoeveel objecten je nog te verhuren hebt.

Al deze informatie is met slimme queries wel uit de reservering zelf te halen, maar dat kost je doodgewoon performance.
Praktijkvoorbeeldje: zonder tabel 2 kost het ophalen van de detailgegevens per dag van 500 hotelkamers over 2 weken (het roomrack, ieder hotelpakket kent dat) zo'n 10 seconden. Dat is onacceptabel lang aan de receptie van het hotel.
Met gebruik van tabel 2 staat 't resultaat direct op 't scherm van de receptioniste.

So much for 'redundancy is evil'...

[GdeKeijzer]

Hey thanx.

Hier kan ik wel wat mee.

[Verwijderd]

ach, dat probleem heb je overal
Ik heb het in een groot verlofsysteem.
Mensen vragen verlof (vakantie in Algemeen Nederlands ) aan van dag X tot dag Y.
En ik heb maar 1 record voor de hele period.

Voor het overzicht van een maand neem ik dan eerst een (fictieve) tabel met alle dagen van de maand en join die met de verlofaanvragen. Zo kan ik een mooi overzichtje geven van de maand met voor elke dag de aanwezigheid. Technisch is dat een

SQL:

WITH days AS (
   SELECT TRUNC(&p_month_date, 'MM') + (rownum -1) datum
      FROM grote_tabel -- tabel zelf wordt niet gebruikt, beter en sneller is de index
   WHERE TRUNC(&p_date_from) + (rownum -1) <= LAST_DAY(&p_month_date)
) select *
from days
inner join absences abs on days.datum BETWEEN abs.date_from and abs.date_to

Dat lijkt me het beste van de 2 werelden.
Die WITH vind ik leesbaarder maar dat kan ook gewoon als subkwerrie ingevoegd worden... ik denk dat MySQL dat ondertussen ook wel al kan (geen recente ervaring meer mee).

Er zijn ook vakantiedagen die er zowiezo uitgewipt worden zodat die niet als 'verlofaanvraag' getoond worden maar als 'vakantiedag'. Hetzelfde principe kun je gebruiken voor dagen waarin je niet verhuurt.

[ Voor 3% gewijzigd door Verwijderd op 26-03-2007 12:36 ]

[Verwijderd]

Nette oplossing voodoochile, maar helaas niet ondersteund in ieder gangbaar DBMS. En bovendien wil ik dit onze consultants niet aandoen: die jongens maken ook maatwerk-rapportage, maar zijn niet echt SQL-guru's.
Dan zijn een paar redundante tabellen wel zo handig en productief, en zolang development zorgt dat de triggers om die tabellen te onderhouden kloppen, zie ik er geen kwaad in.

[Verwijderd]

Verwijderd schreef op maandag 26 maart 2007 @ 19:33:
Nette oplossing voodoochile, maar helaas niet ondersteund in ieder gangbaar DBMS. En bovendien wil ik dit onze consultants niet aandoen: die jongens maken ook maatwerk-rapportage, maar zijn niet echt SQL-guru's.
Dan zijn een paar redundante tabellen wel zo handig en productief, en zolang development zorgt dat de triggers om die tabellen te onderhouden kloppen, zie ik er geen kwaad in.

Ook ik noem mezelf geen SQL goeroe.
Ben voornamelijk JAVA programmeur. Heb (pl/)SQL geleerd uit een Oracle boekje (Feuerstein).
Al bij al is het gewoon logisch nadenken, JAVA vond ik lastiger om leren (had'k dan ook al op school gezien).

Eneuhm, als MySQL die WITH bijvoorbeeld niet ondersteund kun je toch een andere querie bouwen met een tabel die alle datums van 1970 tot 2100 heeft? 1 kolom = index, kan toch zo traag niet zijn.

Achja, ik hoef me niet te moeien natuurlijk maar het lijkt me zo'n shit om te onderhouden als er na de creatie een wijziging moet gebeuren, moet je alles wissen wat er neit invalt en nieuwe crëeren voor een mogelijke verlenging. bah

(en ik bekijk het natuurlijk ook vanuit het oogpunt van 30.000 gebruikers die hun ziekte/vakantiedagen komen invoeren... voor kleinere hoeveelheden en loads is het wel iets anders).

[ Voor 7% gewijzigd door Verwijderd op 27-03-2007 09:51 ]

[Verwijderd]

Verwijderd schreef op dinsdag 27 maart 2007 @ 09:50:
Achja, ik hoef me niet te moeien natuurlijk maar het lijkt me zo'n shit om te onderhouden als er na de creatie een wijziging moet gebeuren, moet je alles wissen wat er neit invalt en nieuwe crëeren voor een mogelijke verlenging. bah

Als je eenmaal de triggers voor elkaar hebt die dat huishoudelijk onderhoud voor je doen, heb je er geen omkijken naar.

(en ik bekijk het natuurlijk ook vanuit het oogpunt van 30.000 gebruikers die hun ziekte/vakantiedagen komen invoeren... voor kleinere hoeveelheden en loads is het wel iets anders).

Voor kleinere hoeveelheden en loads is 't juist niet anders, dan is jouw oplossing veel gemakkelijker te gebruiken, en performt 't ook nog 's prima.
Maar (zonder een "wie heeft de langste" discussie te willen starten) bij een hotelketen met dik 30.000 kamers en een gemiddelde verblijfsduur van 1.3 tot 5 dagen en een bezetting van rond de 70+% (ik hoop niet dat je dat met je verzuim-programma haalt? ) werkte jouw oplossing in ons geval gewoon niet.
Een paar goedgerichte triggers en redundante tabellen maakten de klant weer heel blij...

Overigens werd nergens MySQL genoemd, wat TS gebruikt weet ik niet, maar wij gebruiken vooral MSSQL en InterBase.

[Verwijderd]

Verwijderd schreef op dinsdag 27 maart 2007 @ 23:21:
[...]
Als je eenmaal de triggers voor elkaar hebt die dat huishoudelijk onderhoud voor je doen, heb je er geen omkijken naar.

[...]
Voor kleinere hoeveelheden en loads is 't juist niet anders, dan is jouw oplossing veel gemakkelijker te gebruiken, en performt 't ook nog 's prima.
Maar (zonder een "wie heeft de langste" discussie te willen starten) bij een hotelketen met dik 30.000 kamers en een gemiddelde verblijfsduur van 1.3 tot 5 dagen en een bezetting van rond de 70+% (ik hoop niet dat je dat met je verzuim-programma haalt? ) werkte jouw oplossing in ons geval gewoon niet.
Een paar goedgerichte triggers en redundante tabellen maakten de klant weer heel blij...

Overigens werd nergens MySQL genoemd, wat TS gebruikt weet ik niet, maar wij gebruiken vooral MSSQL en InterBase.

héhé, 70% niet maar het is wel een verlofsysteem voor ambtenaren

Tja, bij ons onder ORACLE hebben we er (nog) geen performance problemen mee...
Maar wij werken wel met dagen (met period identifiers voor voor en namiddag). Niet met uren, want 1.3 dagen is bij ons niet in te geven... daar kan wel eens het verschil zijn, als ik die rownum nog eens door 24 moet delen om met uren te werken kan ik me wel voorstellen dat het performantie moet inboeten.

De reden waarvoor ik MySQL vernoemde was omdat jij zei dat niet elk systeem het aankan en dan vernoem ik het veelgebruikte maar weinig functionele (volgens mijn out of date ervaring) MySQL. (maar ik heb zoals gezegd enkel wat recente ervaring met Oracle).

Ik houd dit iig in het achterhoofd... moesten we ooit problemen krijgen.