[SQL] Database met mutation logging

[JeRa]

Ik heb op dit moment 22 tabellen in een administratieve relationele (genormaliseerde) database waarvan ik van elke tabel wil vastleggen wat er verandert, wie er iets verandert en wanneer dit gebeurt. Als ik echter kijk naar performance of normalisatie komen er nooit echt 'mooie' oplossingen dagen. Wat ik al had bedacht:

1) Ik zou twee tabellen kunnen maken, logs en logtypes. In logtypes sla ik de verschillende typen logs op, dit komt in de praktijk neer op de verschillende tabellen. Vervolgens wordt er bij elke mutatie een nieuw record aangemaakt in logs waarbij er wordt verwezen naar een logtype en een primary key van de betreffende tabel.

Voordelen:
- maar twee extra tabellen benodigd
- benadeelt performance andere tabellen niet

Nadelen:
- in de tabel logs zit een foreign key die niet echt een foreign key is; dus lelijk
- inhoud logs verschilt per tabel; geen consistentie

2) Ik kan de tabellen uitbreiden met een extra veld, revision_id. Ik hou dan in feite revisies van records bij, waarbij ik erg gemakkelijk terug in de geschiedenis kan om te zien hoe een record er twee weken geleden uitzag.

Voordelen:
- uniforme manier van logging
- biedt meer mogelijkheden dan gereduceerde logs

Nadelen:
- performancetechnisch een ramp
- veel opslagruimte benodigd voor een feature die niet ontzettend veel gebruikt gaat worden

3) Ik maak 22 nieuwe tabellen aan waarin alle records worden opgeslagen voordat ze worden gewijzigd.

Voordelen:
- uniforme manier van logging
- biedt meer mogelijkheden dan gereduceerde logs
- performance verschilt niet van het oorspronkelijke design
- opslagruimte is te beperken door de scheiding van tabellen

Nadelen:
- het aantal tabellen wordt verdubbeld

Wat ik niet heb behandeld is een lagere vorm van logging; ik zou bijvoorbeeld alle SQL-statements (INSERT, UPDATE, DELETE) kunnen loggen in een apart bestand, tezamen met een tijdstip en de persoon die de wijziging uitvoert. Ik wil het echter op databaseniveau houden, zodat men simpel in het administratieprogramma de logs van een bepaald record kan opvragen.

Graag lees ik jullie voorstellen voor oplossingen voor dit obstakel

[Gerco]

De performancenadelen van dat revision_id vallen wel mee hoor. Met een index op dat nummertje zul je het waarschijnlijk niet eens opmerken. Dan blijft alleen de opslagruimte over en het feit dat je dat in al je queries moet toevoegen, dat moet met een goede DAL geen probleem zijn.

Wel is het waarschijnlijk handig om 1 speciaal revisienummer te gebruiken voor "huidige revisie", -1 ofzo. Op die manier hoef je niet steeds HAVING revisie_id = MAX(revisie_id) te gebruiken en dat scheelt natuurlijk bakken performance.

Archieftabellen zijn een beter idee als je dat log niet vaak gaat gebruiken. Anders "vervuil" je je hoofddatabase ermee. Je zou dat archief zelfs in een andere database kunnen zetten om verschillende redenen.

[ Voor 16% gewijzigd door Gerco op 07-05-2007 20:27 ]

[DaCoTa]

En een audit trail logfile aanmaken? Je zegt dat deze feature niet veel gebruikt gaat worden, is het dan belangrijk dat je eventuele queries snel moet kunnen zoeken? Om hoeveel mutaties gaat het?

Oh, even ook het einde van de TS lezen. Nouja... Ik heb zelf een audit trail logfile aangemaakt voor een gedeelte van een datamodel, inclusief systeem om het per tabel via het framework en log4j snel aan/uit te kunnen zetten. Dit, in combinatie met WinGrep of soortgelijke tools geven vrij snel alle info die nodig is.

Als toevoeging: de logfile slaap op of het een U, I of D statement is, via het sessiemanagement haal ik de gebruikersnaam erbij en tot slot alle veldnamen met waardes in key-value paren. Eenvoudiger kan bijna niet.

[ Voor 17% gewijzigd door DaCoTa op 07-05-2007 20:31 ]

[JeRa]

Gerco schreef op maandag 07 mei 2007 @ 20:24:
De performancenadelen van dat revision_id vallen wel mee hoor. Met een index op dat nummertje zul je het waarschijnlijk niet eens opmerken. Dan blijft alleen de opslagruimte over en het feit dat je dat in al je queries moet toevoegen, dat moet met een goede DAL geen probleem zijn.

Wel is het waarschijnlijk handig om 1 speciaal revisienummer te gebruiken voor "huidige revisie", -1 ofzo. Op die manier hoef je niet steeds HAVING revisie_id = MAX(revisie_id) te gebruiken en dat scheelt natuurlijk bakken performance.

Daar heb je natuurlijk gelijk in. Bij bijvoorbeeld de tabel accounts (id, revision_id, name, etc) heb ik al een PK index op (id, revision_id) en met een aparte partiële index op revision_id (WHERE revision_id = -1) is ook snel een overzicht van de huidige records op te vragen. Nadeel hierbij is dat ik op élke tabel drie velden moet toevoegen om de revisies, het tijdstip en de mutator (account) bij te houden. Mutaties zijn er niet verschrikkelijk veel, maar ik wil voorkomen dat de hoofdadministratie dichtslibt door de hoeveelheid oude revisies die wordt opgeslagen.

[JeRa]

DaCoTa schreef op maandag 07 mei 2007 @ 20:27:
En een audit trail logfile aanmaken? Je zegt dat deze feature niet veel gebruikt gaat worden, is het dan belangrijk dat je eventuele queries snel moet kunnen zoeken? Om hoeveel mutaties gaat het?

Oh, even ook het einde van de TS lezen. Nouja... Ik heb zelf een audit trail logfile aangemaakt voor een gedeelte van een datamodel, inclusief systeem om het per tabel via het framework en log4j snel aan/uit te kunnen zetten. Dit, in combinatie met WinGrep of soortgelijke tools geven vrij snel alle info die nodig is.

Als toevoeging: de logfile slaap op of het een U, I of D statement is, via het sessiemanagement haal ik de gebruikersnaam erbij en tot slot alle veldnamen met waardes in key-value paren. Eenvoudiger kan bijna niet.

Klinkt goed, maar wat is het voordeel op mijn eerste voorstel in de TS?

[DaCoTa]

JeRa schreef op maandag 07 mei 2007 @ 20:36:
[...]

Klinkt goed, maar wat is het voordeel op mijn eerste voorstel in de TS?

Belast de database niet en eenvoudiger onderhoud. Maar het hangt nogal af om hoeveel mutaties het gaat.

En bij je voorstel snap ik nog niet echt hoe je bijvoorbeeld een insert opslaat. Sla je dan iedere kolomwaarde in een nieuw record op? Of heb je een soort open tabel waar je in iedere kolom alles in kan stoppen?

[ Voor 26% gewijzigd door DaCoTa op 07-05-2007 20:56 ]

[JeRa]

DaCoTa schreef op maandag 07 mei 2007 @ 20:55:
[...]

Belast de database niet en eenvoudiger onderhoud. Maar het hangt nogal af om hoeveel mutaties het gaat.

En bij je voorstel snap ik nog niet echt hoe je bijvoorbeeld een insert opslaat. Sla je dan iedere kolomwaarde in een nieuw record op? Of heb je een soort open tabel waar je in iedere kolom alles in kan stoppen?

Ja, bijvoorbeeld. Wel in een béétje uniform formaat natuurlijk, zodat wijzigingen terug te draaien zijn. Een INSERT wordt simpelweg opgeslagen als een log record die zegt dat die en die persoon het record heeft aangemaakt. Vervolgens wordt bij elke wijziging de oude waarde van een/de veld(en) opgeslagen maar ik neig nu meer naar het revisiesysteem zoals hierboven beschreven.

[DaCoTa]

JeRa schreef op maandag 07 mei 2007 @ 20:58:
[...]

Ja, bijvoorbeeld. Wel in een béétje uniform formaat natuurlijk, zodat wijzigingen terug te draaien zijn. Een INSERT wordt simpelweg opgeslagen als een log record die zegt dat die en die persoon het record heeft aangemaakt. Vervolgens wordt bij elke wijziging de oude waarde van een/de veld(en) opgeslagen maar ik neig nu meer naar het revisiesysteem zoals hierboven beschreven.

Nadeel van een revisie systeem is dat je je software moet aanpassen, omdat updates en deletes anders gaan werken. Je hebt, naast de revision_id, ook nog een delete flag nodig, daar moet je rekening mee houden, met alle selects moet je rekening houden, joins werken niet echt lekker meer en er is geen support in standaard frameworks om hiermee om te gaan. Ik zou het ten sterkste afraden, TENZIJ je alle revision records in een alternatieve database gaat opslaan, zodat je gewone productiewerk losstaat. Maar dan zit je dicht bij je optie 3.

[JeRa]

DaCoTa schreef op maandag 07 mei 2007 @ 21:22:
[...]

Nadeel van een revisie systeem is dat je je software moet aanpassen, omdat updates en deletes anders gaan werken.

Dat is vooralsnog minimaal aangezien het merendeel van de queries nog geschreven moet worden

Je hebt, naast de revision_id, ook nog een delete flag nodig, daar moet je rekening mee houden,

Niet als je (revision_id = -1) als een speciaal geval hebt. Zodra er geen record meer is met (revision_id = -1) kun je die als verwijderd beschouwen, terwijl de oudere revisies gewoon nog bestaan.

met alle selects moet je rekening houden, joins werken niet echt lekker meer en er is geen support in standaard frameworks om hiermee om te gaan.

De frameworks zijn het probleem niet, omdat de applicaties die ik gebruik ofwel zelfgeschreven zijn ofwel support hebben voor customized queries. De joins zien er niet meer uit, maar als ik join op (id, revision_id) join ik gewoon op een PK dus daar zie ik het probleem niet?

Ik zou het ten sterkste afraden, TENZIJ je alle revision records in een alternatieve database gaat opslaan, zodat je gewone productiewerk losstaat. Maar dan zit je dicht bij je optie 3.

Dat wil ik dus ook voorkomen, mede omdat ik dan twee vrijwel identieke databases moet gaan bijhouden.

Het probleem bij jouw methode is dat ik neig naar redundantie ik wil in mijn administratiebeheer snel kunnen zien wanneer een account is aangemaakt of het laatst is gewijzigd, zou ik dat volledig genormaliseerd doen dan zou dat uit de logs moeten komen (aangezien dat de enige plek is met timestamps en identificatie van de mutator). Ik wil dat medewerkers in de toekomst met één druk op de knop kunnen zien wat er in het verleden aan een record is veranderd, zonder dit te grof te hebben (tekstlogs) maar het hoeft ook niet op het niveau van complete revisies. Wat dat betreft staat er nog veel open

[DaCoTa]

JeRa schreef op maandag 07 mei 2007 @ 21:32:
[...]
Niet als je (revision_id = -1) als een speciaal geval hebt. Zodra er geen record meer is met (revision_id = -1) kun je die als verwijderd beschouwen, terwijl de oudere revisies gewoon nog bestaan.

Dat zou kunnen werken idd. Maar dan zou ik de revision_id gewoon nullable maken, lijkt me iets fraaier. Enige waar je mee zit is dan overhead qua dataopslag. Pas wel op dat grote indexen op grote tabellen duur zijn met updates/inserts. Weet je al iets van aantallen in combinatie met retentie tijd? Want die combinatie is doorslaggevend lijkt me in dit geval.

[JeRa]

DaCoTa schreef op maandag 07 mei 2007 @ 22:12:
[...]

Dat zou kunnen werken idd. Maar dan zou ik de revision_id gewoon nullable maken, lijkt me iets fraaier.

Waarom nullable maken als je een verwijderactie ook kunt uitvoeren als een update zonder dat er een nieuw "-1-record" wordt aangemaakt? Of bedoel je dat ik (revision_id = NULL) als huidige revisie zou moeten nemen?

Enige waar je mee zit is dan overhead qua dataopslag. Pas wel op dat grote indexen op grote tabellen duur zijn met updates/inserts. Weet je al iets van aantallen in combinatie met retentie tijd? Want die combinatie is doorslaggevend lijkt me in dit geval.

Over aantallen kan ik niets zeggen, retentietijd is oneindig

[Gerco]

JeRa schreef op maandag 07 mei 2007 @ 21:32:
De frameworks zijn het probleem niet, omdat de applicaties die ik gebruik ofwel zelfgeschreven zijn ofwel support hebben voor customized queries. De joins zien er niet meer uit, maar als ik join op (id, revision_id) join ik gewoon op een PK dus daar zie ik het probleem niet?

Daar zou ik toch niet op joinen, want dan link je bijvoorbeeld een persoon aan zijn adres op tijdstip T1. Als je dan dat adres gaat updaten op T2, staat je persoon nog steeds naar adres op T1 te wijzen.

Je moet dan juist niet joinen op revisionid dus Je queries worden er wel onhandig van aangezien je van elk record moet toevoegen: WHERE revision = rev_current (waar rev_current staat voor de meest recente revisie of een speciale waarde daarvoor).

[Verwijderd]

Wanneer 't puur om rapportage/tracing van de wijzigingen gaat is 't vaak wel handig om de boel niet te ver te normaliseren.

B.v. 1 tabel:

ID             autoincrement (PK)
tabelnaam      char
original_ID    de PK van het gewijzigde record
veldnaam       char       
vorige_waarde  varchar, eigenlijk redundant, maar rapporteert wel handig
nieuwe_waarde  varchar                        
gewijzigd_op   datetime of timestamp
gewijzigd_door user ID (char, integer, of wat jij wilt)

Dan een leuke ON UPDATE trigger op je tabellen die die log tabel vult wanneer een veld dat je wil loggen gewijzigd wordt (je wil meestal niet elke wijziging loggen). En met een extra compound index op tabelnaam en original_ID performt 't ook nog 's prima.

Er zijn meer wegen die naar Rome leiden...

[JeRa]

Gerco schreef op maandag 07 mei 2007 @ 22:22:
[...]

Daar zou ik toch niet op joinen, want dan link je bijvoorbeeld een persoon aan zijn adres op tijdstip T1. Als je dan dat adres gaat updaten op T2, staat je persoon nog steeds naar adres op T1 te wijzen.

Je moet dan juist niet joinen op revisionid dus Je queries worden er wel onhandig van aangezien je van elk record moet toevoegen: WHERE revision = rev_current (waar rev_current staat voor de meest recente revisie of een speciale waarde daarvoor).

Joinen gebeurt altijd op basis van de foreign key gecombineerd met (revision_id = -1), dus als ik iemands adres ga updaten krijgt het nieuwe adres revision_id -1, het oude adres krijgt een nieuw revision_id en de persoon ziet gewoon het nieuwe adres

[JeRa]

Verwijderd schreef op maandag 07 mei 2007 @ 22:28:
Wanneer 't puur om rapportage/tracing van de wijzigingen gaat is 't vaak wel handig om de boel niet te ver te normaliseren.

B.v. 1 tabel:

ID             autoincrement (PK)
tabelnaam      char
original_ID    de PK van het gewijzigde record
veldnaam       char       
vorige_waarde  varchar, eigenlijk redundant, maar rapporteert wel handig
nieuwe_waarde  varchar                        
gewijzigd_op   datetime of timestamp
gewijzigd_door user ID (char, integer, of wat jij wilt)

Dan een leuke ON UPDATE trigger op je tabellen die die log tabel vult wanneer een veld dat je wil loggen gewijzigd wordt (je wil meestal niet elke wijziging loggen). En met een extra compound index op tabelnaam en original_ID performt 't ook nog 's prima.

Er zijn meer wegen die naar Rome leiden...

Dat is zeg maar exact voorstel 1 uit de TS in plaats van tabelnaam (redundant) gebruik ik een logtype.

[ Voor 3% gewijzigd door JeRa op 07-05-2007 22:30 ]

[Verwijderd]

JeRa schreef op maandag 07 mei 2007 @ 22:30:
Dat is zeg maar exact voorstel 1 uit de TS in plaats van tabelnaam (redundant) gebruik ik een logtype.

Nou je 't zegt...
Werkt trouwens in de praktijk prima.

[JeRa]

Verwijderd schreef op maandag 07 mei 2007 @ 22:37:
[...]
Nou je 't zegt...
Werkt trouwens in de praktijk prima.

Ik ben weer meer aan het neigen naar ontwerp 1 omdat dat zowel simpeler te implementeren is en niet persé minder effectief is - ik vroeg me alleen af hoe je die ON UPDATE combineert met het userid in de logtabel?

[DaCoTa]

JeRa schreef op maandag 07 mei 2007 @ 22:19:
[...]
Of bedoel je dat ik (revision_id = NULL) als huidige revisie zou moeten nemen?

Ja.

Over aantallen kan ik niets zeggen, retentietijd is oneindig

Hmm, denk dan nu wel vast na over eventuele data opschoon acties, een database die alleen maar groeit, dat gaat op een gegeven ogenblik fout.

En je hebt toch wel een idee over wat voor soort mutaties er plaats gaan vinden? Is dat dagelijks, wekelijks of iedere 10 seconden? En je hebt toch ook wel een beetje een idee wat het aantal gebruikers is? En hoe lang data actueel blijft en hoe vaak die data in zijn lifetime gemuteerd is?

Je moet alleen heel erg rekening houden met de kosten van a) de trigger en b) de update. Die 2e vooral als je veel data hebt. Als je een record wilt updaten en je PK index segmenten (en eventuele andere index segmenten) passen niet meer in de cache van de database, dan betaal je voor iedere update meerdere lees- en schrijfacties om de index(en) bij te werken. Dan gaat je performance heel erg hard naar beneden.

[ Voor 47% gewijzigd door DaCoTa op 07-05-2007 22:48 ]

[JeRa]

DaCoTa schreef op maandag 07 mei 2007 @ 22:43:
[...]

Hmm, denk dan nu wel vast na over eventuele data opschoon acties, een database die alleen maar groeit, dat gaat op een gegeven ogenblik fout.

Ik denk dat dat wel mee gaat vallen; de frequentie van de mutaties zal in ieder geval niet hoger liggen dan de postfrequentie hier of op FOK!

DaCoTa schreef op maandag 07 mei 2007 @ 22:43:
En je hebt toch wel een idee over wat voor soort mutaties er plaats gaan vinden? Is dat dagelijks, wekelijks of iedere 10 seconden? En je hebt toch ook wel een beetje een idee wat het aantal gebruikers is? En hoe lang data actueel blijft en hoe vaak die data in zijn lifetime gemuteerd is?

Ja, die heb ik, en het gebruik zal de komende 2 jaar vrij laag blijven. Het gaat hier over een vrij statische administratie die zo nu en dan door klanten of door admins gewijzigd wordt.

Je moet alleen heel erg rekening houden met de kosten van a) de trigger en b) de update. Die 2e vooral als je veel data hebt. Als je een record wilt updaten en je PK index segmenten (en eventuele andere index segmenten) passen niet meer in de cache van de database, dan betaal je voor iedere update meerdere lees- en schrijfacties om de index(en) bij te werken. Dan gaat je performance heel erg hard naar beneden.

Daarom ben ik nu ook weer de optie van één centrale log aan het bekijken

[ Voor 57% gewijzigd door JeRa op 07-05-2007 22:50 ]

[Verwijderd]

ik vroeg me alleen af hoe je die ON UPDATE combineert met het userid in de logtabel?

Wanneer je 't via triggers wil regelen (goed plan!) zijn er 3 mogelijkheden:

- Zorg dat iedere user van je applicatie(s) een eigen login op de database heeft. Vaak niet handig omdat je users zowel in je applicatie als op de database moet onderhouden. Goed te doen in een omgeving met goede IT-afdeling, maar een drama wanneer in het MKB de boekhouder ook de automatisering onderhoudt...

- Login op de database laten regelen door bv. Windows of LDAP authentication (als je DB dat ondersteunt). Prima oplossing wanneer iedereen z'n eigen werkplek heeft, maar een tikkie lastig bij een balie-PC die door meerdere mensen wordt gebruikt.

- In al je tabellen een 'gewijzigd_door' veld opnemen die door de applicatie wordt ingevuld. Die kun je prima uitlezen in je trigger, en gebruiken in je log. Helemaal safe is dat ook niet (iemand zou buiten je applicatie om een record kunnen wijzigen), maar over 't algemeen werkt dit prima.

Op m'n werk gebruiken we vooral optie 3, en bij een paar grote klanten optie 1.

[ Voor 4% gewijzigd door Verwijderd op 07-05-2007 23:07 ]

[JeRa]

Verwijderd schreef op maandag 07 mei 2007 @ 22:59:
[...]

Wanneer je 't via triggers wil regelen (goed plan!) zijn er 3 mogelijkheden:

- Zorg dat iedere user van je applicatie(s) een eigen login op de database heeft. Vaak niet handig omdat je users zowel in je applicatie als op de database moet onderhouden. Goed te doen in een omgeving met goede IT-afdeling, maar een drama wanneer in het MKB de boekhouder ook de automatisering onderhoudt...

Er is absoluut geen reden waarom je de synchronisatie tussen admins in de administratie en databasegebruikers niet meteen zou automatiseren, al is het maar ten behoeve van de integriteit van je administratie (fouten worden gemaakt) probleem daarmee is dat klanten straks ook zelf hun gegevens kunnen aanpassen, en ik wil niet per klant een databasegebruiker.

- Login op de database laten regelen door bv. Windows of LDAP authentication (als je DB dat ondersteunt). Prima oplossing wanneer iedereen z'n eigen werkplek heeft, maar een tikkie lastig bij een balie-PC die door meerdere mensen wordt gebruikt.

Dit gaat waarschijnlijk niet van pas komen. De administratie vanuit de klanten is webbased, vanuit het personeel via een Java applicatie die direct contact legt met de database. In beide gevallen moet gelogged worden.

- In al je tabellen een 'gewijzigd_door' veld opnemen die door de applicatie wordt ingevuld. Die kun je prima uitlezen in je trigger, en gebruiken in je log. Helemaal safe is dat ook niet (iemand zou buiten je applicatie om een record kunnen wijzigen), maar over 't algemeen werkt dit prima.

Dit is op zich ook een optie, maar de oplossing vind ik nogal smerig

[DaCoTa]

JeRa schreef op maandag 07 mei 2007 @ 22:47:
[...]
Ik denk dat dat wel mee gaat vallen; de frequentie van de mutaties zal in ieder geval niet hoger liggen dan de postfrequentie hier of op FOK!
[...]
Ja, die heb ik, en het gebruik zal de komende 2 jaar vrij laag blijven. Het gaat hier over een vrij statische administratie die zo nu en dan door klanten of door admins gewijzigd wordt.
[...]
Daarom ben ik nu ook weer de optie van één centrale log aan het bekijken

Met dit soort aantallen heb ik niks gezegd. Dan moet je voor de meest overzichtelijke/eenvoudigst te implementeren optie gaan

[Verwijderd]

JeRa schreef op maandag 07 mei 2007 @ 23:13:
Dit is op zich ook een optie, maar de oplossing vind ik nogal smerig

Net als een hoop andere smerige oplossingen werkt 't in de praktijk als een tiet.