[database ontwerp] urenstaat

[wizl]

Ik wil een online urenstaat gaan fabrieken. Nu breek ik me al een aantal dagen het hoofd over een bruikbaar/zinnig databaseontwerp, en ik kom er maar niet uit. Misschien dus dat de welwillende tweaker zin heeft om er zijn gedachten over te laten gaan . . .

Als uitgangspunt heb ik eigenlijk steeds de uiteindelijke representatie genomen. Ik wil horizontaal de uursoorten gaan weergeven, en dan verticaal het aantal gemaakte uren voor die uursoort . . .

Ik wil uiteindelijk (per werknemer) de data dus als volgt gaan representeren:

code:

uursoort       ma  di  wo  do  vrij
uursoort a    2   8   8   2    8
uursoort b    4      2    
uursoort c    2      4    

totaal      8   8   8   8    8   -> 40

[Nielsz]

table projecten (id,naam,zooi)
table gebruikers (id,naam,pass,zooi)
table data (userid,projectid,[tijd | begintijd & eindtijd]

oid.

[eborn]

heb je al iets op papier staan? En dan bedoel ik qua database-ontwerp?

[Verwijderd]

Wat is precies dat uursoort a, b en c? Als je dat weet is het makkelijker een ontwerp maken. Een tabel met de kolommen uursoorta, uursoortb.. is niet goed lijkt mij.

[wizl]

Wat is precies dat uursoort a, b en c?

Uursoort is de soort gemaakte uren. Op een dag kun je dus bijv. 2 uur programmeren, 3 uur helpdesken etc.

De tabel met de uiteindelijke urenregels had ik ongeveer als volgt bedacht:

code:

datum, gebruiker_id, uursoort_id, aantal_uren, description

De gebruiker_id, en de uursoort_id, komen dan natuurlijk uit een ander tabelletje.

Echter als ik de data op deze manier opsla, dan wordt het vgl. mij heel moeilijk/onmogelijk om de gegevens te gaan representeren op de manier zoals hierboven bedoeld.
Op een gegeven moment heb ik nl. een aantal regels:

code:

23-02-2002, wizl, 1010, 4, foo geprogrammeerd
23-02-2002, wizl, 1020, 2, installatie voorbereid
23-02-2002, wizl, 1010, 2, foobar geprogrammeerd
24-02-2002, wizl, 1010, 8, weer iets geprogrammeerd

Als ik nu de gegevens opvraag

code:

order by uursoort, datum

dan is de volgorde verkeerd om ze in een tabel zoals hierboven bedoeld te laten zien . . .

[eborn]

Je moet dan gaan groeperen op Uursoort (en natuurlijk dat weer per werknemer). Dan tel je alle uren bij elkaar op. Het kan zowiezo niet in één database query.

[wizl]

probleem is dan meer dat ik, als ik de regels in een html table ga opbouwen, de volgorde niet klopt eigenlijk, want ga van links naar rechts, en kom dan per uursoort een datum tegen die ik moet vullen . . .

[PdeHoog]

Een mogelijke oplossingsrichting is de volgende (ervanuitgaande dat je gebruik kan maken van stored procedure's):

Je maakt vier tabellen aan:
project - projectstamgegevens
werknemer - werknemergegevens
uursoort - uursoortgegevens
uren - geschreven uren (werknemer_id, project_id, uursoort_id, datum, aantal uur)

Om gegevens op te vragen schrijf je een SP die, op basis van een ingevulde begindatum, per dag de data selecteert en deze wegschrijft in een tijdelijke tabel.

Ervanuitgaande dat je een werkweek van vijf dagen hebt, krijg je dan vijf tijdelijke tabellen, nl. de begindatum, begindatum + 1 dag, begindatum + 2 dagen etc. etc.

Deze tabellen join je aan elkaar op de volgende wijze:
SELECT U1.Project_id,
U1.Werksoort_id,
U1.Datum,
U1.Uren,
U2.Datum,
U2.Uren,
...
FROM TMP_UREN1 U1,
TMP_UREN2 U2,
...
WHERE U1.PROJECT_ID = U2.PROJECT_ID
AND U1.WERKSOORT_ID = U2.WERKSOORT_ID
...

Dit is de manier waarop ik aan de slag zou gaan en volgens mij de enige manier waarop je dit met behulp van SQL zou kunnen oplossen.

In mijn werk heb ik dit soort oplossingen vaker gezien en toegepast om 'draaitabel-functionaliteit' te creeeren.

Ik hoop dat je er wat aan hebt. Als je nog vragen hebt hoor ik het graag.

Succes!

[dusty]

Op dinsdag 02 april 2002 16:12 schreef eborn het volgende:
Je moet dan gaan groeperen op Uursoort (en natuurlijk dat weer per werknemer). Dan tel je alle uren bij elkaar op. Het kan zowiezo niet in één database query.

Wedden?

[eborn]

Op woensdag 03 april 2002 14:14 schreef dusty het volgende:

[..]

Wedden?

Ok, laten we het dan zo zeggen: ik kan het niet in één query (denk ik). Nou laat ik het dan nog moeilijker maken: ik kan het in MySQL (denk ik) niet

[Verwijderd]

Nee in MySQL kan het ook wel, denk ik, alleen in msSQL is het gewoon makkelijk werken. Werken met Stored Procedures, Diagrams en Views is gewoon onwijs snel en zeer makkelijk

[bigtree]

Op woensdag 03 april 2002 14:14 schreef dusty het volgende:

[..]

Wedden?

Ik wil wel zien hoe jij een query maakt die zowel de afzonderlijke uursoorten weergeeft als de totalen. En dan de totalen netjes als laatste rij.

[Verwijderd]

Heb je wel eens de Query bouwer in msSQL gebruikt, dan kan je echt alles aan mekaar knopen. En je kan teminste goed tabellen Joinen, daarom gebruik ik geen mysql!

[Goodielover]

De totalen in de laatste kolom zijn geen probleem in 1 query.
De totalen van de dag onderaan het formulier worden lastiger om op tenemn in dezelfde query. Dit zou alleen kunnen als het concept week zou worden gemodelleerd er er dus een tabel zou zijn:
weeknr
activiteitcode
uren_maandag
:
uren_zondag

Op woensdag 03 april 2002 16:11 schreef bigtree het volgende:

[..]

Ik wil wel zien hoe jij een query maakt die zowel de afzonderlijke uursoorten weergeeft als de totalen. En dan de totalen netjes als laatste rij.

[Crazy D]

Op woensdag 03 april 2002 22:16 schreef Goodielover het volgende:
De totalen van de dag onderaan het formulier worden lastiger om op tenemn in dezelfde query. Dit zou alleen kunnen als het concept week zou worden gemodelleerd er er dus een tabel zou zijn:

Als de uren d.m.v. van- en tot-datum/tijd worden opgeslagen, kun je voor het weeknummer eventuele ingebouwde datumfuncties gebruiken.

[PdeHoog]

Als je de oplossing die ik heb aangedragen gebruikt zou je ook ervoor kunnen kiezen het select-statement in een tijdelijke tabel weg te schrijven met een INSERT INTO.

Vervolgens kun je de totalen per dag laten berekenen door een GROUP BY en deze laat je ook wegschrijven in de tijdelijke tabel.

Daarna geef je een SELECT op de tijdelijke tabel terug als resultaat van de SP. Deze kun je dan zo uitlezen en omzetten naar een HTML-tabel.

[Goodielover]

wizl, heb je nou een formaterings probleem of een retrieval probleem? Wat ik denk dat je je SQL probleem moet scheiden van hoe je het op scherm wilt opnemen.
Je tabel met persoon, datum, uursoort, aantal en omschrijving voldoet om je info op te slaan.
Als je de presentatie op het scherm direct uit 1 SQL wil halen moet je inderdaad gaan trucken met een tussentabel waarin je gaat inserten.
Mijn voorstel zou zijn om eerst het weekoverzicht zonder onderste totalen op te bouwen en weer te gegeven en daarna met een aparte query de onderste totalen op te halen en te presenteren.

even de pseudocode van het SQL-statement:

code:

select weeknr(datum) as weeknummer
    ,uursoort
    ,sum(if(weekday(datum)="maandag",aantal_uren,0)) uren_ma
    ,sum(if(weekday(datum)="dinsdag",aantal_uren,0)) uren_di
    ,sum(if(weekday(datum)="woensdag",aantal_uren,0)) uren_wo
    ,sum(if(weekday(datum)="donderdag",aantal_uren,0)) uren_do
    ,sum(if(weekday(datum)="vrijdag",aantal_uren,0)) uren_vr
    ,sum(if(weekday(datum)="zaterdag",aantal_uren,0)) uren_za
    ,sum(if(weekday(datum)="zondag",aantal_uren,0)) uren_zo
    ,sum(aantal_uren) totaal_week
from   werkuren
where  pernr = 12
group by uursoort, weeknummer
order by 2,1

In oracle-termen spreek je hier van een max-decode constructie. Je groupeert, maar daarin neem je selectief records mee. Hierdoor kan je meerdere records "platslaan" tot attributen van een nieuw te vormen "record"

voor de totalen onderaan het overzicht gebruik je hetzelfde statament, maar dan zonder de group by op uursoort.

[kaandorp]

http://www.devshed.com/Server_Side/PHP/TimeIsMoney/TimeIsMoney1/print
http://www.devshed.com/Server_Side/PHP/TimeIsMoney/TimeIsMoney2/print

TADA! Alles wat u wilde weten

[PdeHoog]

Op donderdag 04 april 2002 09:35 schreef Goodielover het volgende:
even de pseudocode van het SQL-statement:

[...]

code:

select weeknr(datum) as weeknummer
    ,uursoort
    ,sum(if(weekday(datum)="maandag",aantal_uren,0)) uren_ma
    ,sum(if(weekday(datum)="dinsdag",aantal_uren,0)) uren_di
    ,sum(if(weekday(datum)="woensdag",aantal_uren,0)) uren_wo
    ,sum(if(weekday(datum)="donderdag",aantal_uren,0)) uren_do
    ,sum(if(weekday(datum)="vrijdag",aantal_uren,0)) uren_vr
    ,sum(if(weekday(datum)="zaterdag",aantal_uren,0)) uren_za
    ,sum(if(weekday(datum)="zondag",aantal_uren,0)) uren_zo
    ,sum(aantal_uren) totaal_week
from   werkuren
where  pernr = 12
group by uursoort, weeknummer
order by 2,1

Strakke oplossing. Had ik zelf nog niet aan gedacht.

[Goodielover]

Op donderdag 04 april 2002 10:44 schreef PdeHoog het volgende:

[..]

Strakke oplossing. Had ik zelf nog niet aan gedacht.

Dankjewel.
Ik mis trouwens in het ontwerp het concept project.
Is dit gecodeerd in de activiteitcode?
Ik zou het mooi vinden als je dit wel apart zou modelleren. (zoals ook gedaan in de links hierboven)

[PdeHoog]

Op donderdag 04 april 2002 11:59 schreef Goodielover het volgende:

[..]

Ik mis trouwens in het ontwerp het concept project.
Is dit gecodeerd in de activiteitcode?
Ik zou het mooi vinden als je dit wel apart zou modelleren. (zoals ook gedaan in de links hierboven)

Ik geloof niet dat ik geheel begrijp wat je bedoelt.

In mijn oorspronkelijke posting ben ik uitgegaan van de volgende resultaatset:
project, uursoort, dag1, uren1, dag2, uren2 ...

Ik heb dus het project betrokken bij mijn oplossing. Bedoel je dat?

Zo niet, kun je dan uitleggen wat je bedoelt?

[Goodielover]

Sorry was niet voor jou bedoeld maar de topic starter, of ben jij dat ook? Meerdere persoonlijkheden, je weet maar nooit?

[PdeHoog]

Op donderdag 04 april 2002 13:13 schreef Goodielover het volgende:
Sorry was niet voor jou bedoeld maar de topic starter, of ben jij dat ook? Meerdere persoonlijkheden, je weet maar nooit?

Nee....dat was ik niet. Gelukkig beschik ik over slechts een persoonlijkheid . Daar is mijn vriendin allang gelukkig mee (hoop ik)!

[wizl]

Ik mis trouwens in het ontwerp het concept project.
Is dit gecodeerd in de activiteitcode?
Ik zou het mooi vinden als je dit wel apart zou modelleren. (zoals ook gedaan in de links hierboven)

Ik heb het concept project niet echt nodig. Ik neem nu per regel (dus niet in dit databaseontwerp, maar op de houtje-touwtje manier waarop het nu gaat ) een 'veld' relatiecode op. M.a.w. de eigenlijke project gegevens maken (nog) geen deel uit van de urenverantwoording . . .

edit:

b.t.w. dat devshed verhaal (time is money) gaat i.i.g. de heeeeele goede kant op

[kaandorp]

Op vrijdag 05 april 2002 10:11 schreef wizl het volgende:
b.t.w. dat devshed verhaal (time is money) gaat i.i.g. de heeeeele goede kant op

Natuurlijk. Devshed gaat altijd de goede kant op.
Maar serieus... Ik denk dat je daar zeker wat mee kan. Ook de database structuur e.d. wordt uitgelegd. Je hoeft het alleen maar aan te passen.