[MYSQL] historische gegevens bewaren

[OCTheEagle]

Hoi,

Ik heb oa in de topic http://gathering.tweakers...///historische%2Cgegevens gekeken. Daarentegen ging het daar meer over welke database je moet gebruiken in plaats van hoe.

Anyway, ik heb een database met één tabel genaamd dossiers. Deze tabel bevat de volgende velden:
Dossiernr, Zaaknaam, Afdeling, Totale kosten, Medewerker, Datum doen, datum af, Status.

Uit deze tabel kun je veel informatie halen; bijv hoeveel dossiers een bepaalde medewerker heeft. hoeveel dossiers de status 'klaar hebben, wat de gemiddelde kosten op een bepaalde afdeling zijn etc.

Het probleem is dat deze informatie alleen maar 'op het moment' beschikbaar is. Het is bijv niet mogelijk om een overzicht te creeeren hoeveel dossiers een bepaalde medewerker van een datum x tot datum x heeft. Het is niet te zien hoeveel dossiers vorige week de status x hebben en deze week de status x.

Een mogelijkheid is een datawarehousesysteem. Daarentegen is dit erg duur en overkill voor mijn situatie (bedrijf van 40 werknemers met gemiddeld 3000 dossiers per jaar).

Mijn vraag is hoe je mijn gewenste situatie kunt uitvoeren mbv een MYSQL/PHP database. Reden dat ik voor zo'n type database heb gekozen is omdat ik daar de meeste kennis over heb en omdat deze als beste uit de bus kwam voor mijn situatie, uitgaande van de andere topic. Met deze database kan mijn doel bereikt worden, ik weet alleen (nog) niet hoe.

Mijn vraag is hoe ik het beste deze historische gegevens kan bewaren cq raadplegen.

[ Voor 4% gewijzigd door OCTheEagle op 22-11-2005 10:25 ]

[Varienaja]

Je kunt events gaan bijhouden van je dossiers, bijvoorbeeld:
- ingeboekt
- aan een medewerker gekoppeld
- in behandeling genomen
- afgehandeld
- gearchiveerd

Met queries kan je dan vrij eenvoudig management-info ophalen, zoals de gemiddelde verwerkingstijd van een dossier, etc.

[djluc]

In principe zou je steeds in plaats van het record met een UPDATE query aan te passen een nieuw record aan kunnen maken. Je voegt dan een veld "versionparent" toe waarin je de ID zet van het hoofdrecord. Je kan dan de complete wijzigingshistorie opzoeken middels een query als:
SELECT * FROM tabel WHERE versionparent=32

[Helox-in-a-box]

of met een datum veld erin:
SELECT * FROM table ORDER BY datumVeld ASC LIMIT 0,1
haal je altijd de nieuwste versie op

[OCTheEagle]

Varienaja schreef op dinsdag 22 november 2005 @ 10:30:
Je kunt events gaan bijhouden van je dossiers, bijvoorbeeld:
- ingeboekt
- aan een medewerker gekoppeld
- in behandeling genomen
- afgehandeld
- gearchiveerd

Met queries kan je dan vrij eenvoudig management-info ophalen, zoals de gemiddelde verwerkingstijd van een dossier, etc.

Dat klinkt interessant. Om dat even iets abstracter te vertalen. Je maakt een detail table genaamd 'status'. De link tussen de tabellen 'dossiers' en 'status' is het dossiernummer.

Je deelt de status van een dossier op in stappen waarbij elke stap een field is. Als je dan een bepaalde stap hebt gedaan (bijv ingeboekt) dan vink je dat aan waarbij de datum van aanvinken in de desbetreffende field van de tabel status terechtkomt. Mbv van een query kun je dan bijv achterhalen wat de gemiddelde verwerkingstijd tussen afgehandeld en gearchiveerd is (dmv die data die je ingevuld hebt) van bijv alle dossiers.

Grote voordeel hierbij is dat beide tabellen overzichtelijk blijven.


Over dat nieuwe record.

Op zich lijkt mij dat wel handig. Maar raakt dan het overzicht na verloop van tijd niet zoek? Als een dossier 8x van status kan veranderen, 3x van medewerker en de kosten kunnen 3x wijzigen dan heb je al snel 14 records nodig voor 1 dossier. Als er paar jaar 6000 dossiers rond gaan dan heb je dus 84.000 records nodig voor 1 jaar. Wat je kunt doen is dat alleen de actueelste versie van een dossier wordt getoond. (kan dat met die SELECT * FROM table ORDER BY datumVeld ASC LIMIT 0,1 query voor alle dossiers?)

Overigens ga ik van de statistieken (voorlopig) uit dat de gegevens per week worden bekeken.

[djluc]

Is veel records een probleem? Je kunt wijzigingen overigens ook in aparte tabellen e.d. op gaan slaan maar eenvoudig is dat niet. Je komt dan al snel op een moment waarop je simpelweg teveel abstractie in je code gaat krijgen. Dan zou je een API moeten bouwen welke dus je gegevens op gaat zoeken. Je krijgt dan bijvoorbeeld een tabel wijzigingen
id-tabel-veld-waarde-datum
1-dossiers-medewerksid-557-NOW()

Dan moet je dus constant alle wijzigingen over de originele velden gooien. Dat kan je een aantal rijen schelen in de originele tabel maar het is een stuk moeilijker. Ik zou gewoon voor de rijen-dupliceren manier gaan.

[djluc]

Welke aanpak ga je gebruiken? Ben je al wat gevorderd?