[DB] structuur transactieverwerkend systeem *

[Verwijderd]

Deze database bezit eigenschappen van diensten, met aan elke dienst 1 of meer activiteiten, die op hun beurt weer uit 1 of meerdere activiteitenregels kan bestaan (waaronder financiele gegevens waarmee gerekent moet worden).

Zoals de database er nu uit ziet zou je verwachten dat deze alleen maar actuele gegevens bevat.

Echter, wanneer er een dienst, activiteit of een activiteitregel (meerdere keren) gewijzigt wordt, moet deze wijziging(en) in de database opgenomen worden, MAAR ook de oude situatie (ivm tijdsvergelijkingen).

Hoe kan ik dit het beste in deze database implementeren?

- Dmv een statuswijziging van de dienst/actieviteit/activiteitregel in de database zoals deze hierboven is. Dus de ouwe record (ouwe situatie) krijgt een status ‘archief’. Een nieuw record met de nieuwe situatie wordt opgenmomen en krijgt een status ‘actueel’.
- Of kunnen we het beste 1 tabel in de DB erbij opnemen, die alle velden van bovenstaande tabellen in zich heeft. En die, zodra er een wijziging plaatsvind, gevuld wordt met de ‘oude’ situatie? (bv. Een activiteit wordt gewijzigt. De activiteit en daarbij behorende activiteitregel(s) en ook de bijbehorende dienst wordt in de extra tabel opgenomen)
- Of is een oplossing schaduwtabellen? Dus DIENST_LOG, ACTIVITEIT_LOG en ACTIVITEITREGEL_LOG. (Zodra een wijziging wordt doorgevoerd, wordt de nieuwe situatie in de normale tabellen opgenomen. De oude situatie komt in de logtabellen te staan)


Welke manier zou het beste zijn? Of is er nog een andere betere (makkelijkere) manier?

[jcraane]

Je zou dit op kunnen lossen door aan elke wijziging in de activiteitenregel tabel een historie te hangen d.m.v. een datum en eventueel een tijdveld. Je tabel komt er dan als volgt uit te zien:

veld1, veld2, enz, datum, tijd

Waarbij het datum- en tijdveld de datum en tijd wordt waarop de wijziging plaatsvond. Je voegt dus als het ware een extra record in de tabel toe met de gewijzigde gegevens. Indien je alleen de datum nodig hebt (en geen tijd) moet je een extra volgnummer opnemen omdat het kan voorkomen dat een regel meerdere keren per dag gewijzigd wordt, dus:

veld1, veld2, enz, datum, volgnummer

Indien een regel meerdere keren per dag gewijzigd wordt blijft de datum gelijk en wordt het volgnummer opgehoogd.

Op deze manier weet je altijd wanneer welke wijziging heeft plaatsgevonden en heb je de originele data nog steeds tot je beschikking.

[ACM]

Ik denk dat er niet zomaar "wat is de beste" gezegd kan worden. Afwegingen die je moet maken zullen zijn:
- Hoeveel archief-records zullen er komen, tegenover hoeveel actuele?
Als er veel archief-records zullen onstaan is een extra/schaduw tabel wellicht wel zo handig, dmv mooie triggers kan je die vrij netjes en transparant bijhouden.
Als het er weinig zijn is het wellicht handiger het gewoon in één tabel te laten.

[Verwijderd]

jcraane schreef op 16 September 2003 @ 11:24:
Je zou dit op kunnen lossen door aan elke wijziging in de activiteitenregel tabel een historie te hangen d.m.v. een datum en eventueel een tijdveld. Je tabel komt er dan als volgt uit te zien:

veld1, veld2, enz, datum, tijd
......

Dit bedoel je in de 'activiteitregel-tabel' zelf of gekoppeld aan die tabel? Aan die optie met een extra tabel heb ik nml. ook al zitten denken. Maar dan krijg ik alsnog 3 extra tabellen en daarbij maakt dit gevens filteren (moet middels asp.net) weer extra lastig lijkt me zo.


De verwachting is dat een wijziging waarschijnlijk gemiddeld 4x per maand per activiteitregel plaatsvind, 2x per maand per activiteit en 1x per half jaar per dienst.
Verder zal de DB bestaan uit zo'n 50 diensten, waarbij elke dienst gem. 4 activiteiten heeft en elke activiteit gem. 3 activiteitregels.

[ Voor 11% gewijzigd door Verwijderd op 16-09-2003 11:39 ]

[jcraane]

de datum en tijd velden komen in de activiteitenregeltabel zelf (je krijgt dus geen extra tabellen). Dus alle tabellen waarvan je de gegevens historisch wilt bewaren moeten datum/tijd velden krijgen. Het record met de meest recentste datum is dus actueel.

[ Voor 8% gewijzigd door jcraane op 16-09-2003 11:49 ]

[ACM]

Verwijderd schreef op 16 September 2003 @ 11:36:
De verwachting is dat een wijziging waarschijnlijk gemiddeld 4x per maand per activiteitregel plaatsvind, 2x per maand per activiteit en 1x per half jaar per dienst.
Verder zal de DB bestaan uit zo'n 50 diensten, waarbij elke dienst gem. 4 activiteiten heeft en elke activiteit gem. 3 activiteitregels.

Als het er zo weinig zijn, zou ik me in eerste instantie gewoon richten op de simpele aanpak, met een actueel/archief veld en een eventuele datum om aan te geven van wanneer het veld was.

't Leuke van zo'n actueel/archief veld is dat je oudere items kan herstellen indien nodig, zonder afhankelijk van de datum te zijn. Die datum is dan vooral leuk om er historische overzichten van te kunnen tonen.
Je moet natuurlijk wel uitkijken dat er maar 1 item actueel is, maar dat kan je met een rule of trigger evt wel oplossen.

[Verwijderd]

ACM schreef op 16 September 2003 @ 11:54:
[...]

Als het er zo weinig zijn, zou ik me in eerste instantie gewoon richten op de simpele aanpak, met een actueel/archief veld en een eventuele datum om aan te geven van wanneer het veld was.

't Leuke van zo'n actueel/archief veld is dat je oudere items kan herstellen indien nodig, zonder afhankelijk van de datum te zijn. Die datum is dan vooral leuk om er historische overzichten van te kunnen tonen.
Je moet natuurlijk wel uitkijken dat er maar 1 item actueel is, maar dat kan je met een rule of trigger evt wel oplossen.

ok, een status-veld dus (icm. een datum/tijdveld), maar ik zit hier met 3 tabellen waarvan ik wijzigingen bij moet houden.. Waar moet ie dan? Status-veld in de dienst tabel? > Als ik in activiteitregel iets wijzig, wil dit toch zeggen dat ik dan de hele dienst moet archiveren of zie ik iets verkeerd?

[ Voor 3% gewijzigd door Verwijderd op 16-09-2003 12:05 ]

[ACM]

Dat weet ik allemaal niet

Als je bij een gewijzigd activiteit de hele dienst moet vervangen, dan kan je misschien beter een schaduw-tabel of een of andere set extra tabellen maken.

Als je de vernieuwde dienst niet als geheel hoeft op te slaan, dan kan je gewoon per tabel een status-veld maken en die losse elementen als archiefjes beschouwen.

[Verwijderd]

hmmm lastig lastig,

ik wil niet de hele dienst hoeven vervangen. Hoe zou de DB gestructureerd kunnen zijn:

Bijv. Wanneer ik activiteitregel ['code1','actID1','naam1', 'datum'] wijzig creeer ik een niewe activiteitregel ['code2','actID1','naam1', 'datum'] die nog steeds bij activiteit1 hoort. Dit klopt idd. zo wil ik het hebben..

Maar, wanneer ik een activiteit wijzig ['ID1','DienstID1','naam1', 'datum'] en een nieuwe activiteitregel ['ID2','dienstID1','naam1', 'datum'] creeer, krijgt deze een nieuw ID en zullen de activiteitsregels niet aan deze nieuwe record gerelatteerd worden. Hier ligt dus een (sleutel)probleempje

Toch? (zou ik dan toch een complete schaduw 'database' moeten gebruiken?)

Voor de 'duidelijkheid' ff mn SQLsrv diagram van de db:

[ Voor 154% gewijzigd door Verwijderd op 16-09-2003 13:29 ]

[Verwijderd]

Een juiste manier is om alles uit elkaar te trekken:

Tabellen:
Dienst (DienstID, FK versieID)
DienstVersie (versieId, Naam, Omschrijving, Ingangsdatum)

En voor de andere tabellen moet je dit ook doen. Op zo'n manier zal je wel je queries goed moeten aanpassen want je kan nu behoorlijk in de INNER JOIN's verdrinken...

Als je 'simpel' een datumveld oid aan je tabellen toevoegt, krijg je inderdaad het probleem dat de Foreign Keys naar de verkeerde velden gaan wijzigen. Dit is absoluut niet de juiste manier in jouw geval.

[ Voor 6% gewijzigd door Verwijderd op 16-09-2003 13:42 ]

[jcraane]

Je moet er op letten dat je niet de primaire sleutel van een tabel veranderd die de vreemde sleutel is in een andere tabel. Een oplossing zou kunnen zijn om in geval van de activiteittabel een nieuwe sleutel te introduceren die uniek is. Alleen deze sleutel wordt opgehoogd als er een record wordt toegevoegd. Het veld activiteitId blijft gelijk zodat de koppeling met de tabel activiteitregel behouden blijft. dus:

[nieuwe sleutel, actieviteitID, dientsID, enz, enz, datum]

[Verwijderd]

Volgens mij wil je je tabellen (tot op zekere hoogte) zoveel mogelijk genormaliseerd hebben. Dan moet je niet een extra sleutel introduceren in de bestaande tabellen, want dat zorgt voor redundantie. Daar hoort dan gewoon een nieuwe tabel bij, zie m'n vorige post.

[JaQ]

Je sleutel veranderd (zoals eerder geroepen).

Een schaduwtabel is alleen nuttig als je heel veel records hebt. De enige reden om het namelijk in een schaduwtabel te zetten is performance. Ik weet niet in welk rdbms je werkt, maar Oracle heeft zoiets moois als een gepartitioneerde tabel (ik weet zeker dat postgresql en mysql hier niet aan doen, maar zo te zien zit je aan een microsoft oplossing te werken). Je kan dan dus een regel toepassen waarin je zegt: wijzigingsdatum is not null > move to partition. (scheelt nogal wat in je performance).

Als je vervolgens te lui bent om je queries voor je app aan te passen kan je eventueel views toepassen om uit te querien (enkel records waarin wijziginsdatum is not null), maar dat is niet aan te raden. Een schaduwtabel is dus ook een optie als je niet al je queries wilt aanpassen, maar beide opties blijven plakband oplossingen (en je weet wat er gebeurd als je een bal met platbank over de vloer rolt.. dat wordt vies!)

@typhoon --> je snapt net niet helemaal wat redundantie is. Leg mij maar eens uit hoe je historie gaat bewaren, zonder redundantie te krijgen? Wil je een tabel gaan bijhouden met als layout: ID, gewijzigd_attribuut_naam, gewijzigd_attribuut_waarde en een datum?

edit:
Ok, nog een stukkie erbij:

Als je bij elke tabel die je hebt 1 kolom toevoegd en daar een datum/tijd (timestamp) datatype aan hangt, kan je al klaar zijn. Al je sleutels worden vervolgens uitgebreid met deze timestamp. Persoonlijk kies ik er altijd voor om het laatste record (het meest recente dus) geen timestamp te geven en dus een wijzigingsdatum bij te houden, ipv een toevoegdatum. Dit is een persoonlijke voorkeur.

Nogmaals: dit is NIET redundanter dan een tabel met een ID en 1 extra attribuut bijhouden i.c.m. met een versietabel. (er worden namelijk net zoveel data bijgehouden, ga maar eens regeltjes tellen, je hebt totaal net zoveel regels) Performancetechnisch gezien is het sneller (het scheelt een join in elke query) en logisch gezien is het beter te begrijpen (in 1 oogopslag dus) De door typhoon voorgestelde oplossing is er een uit het boekje, maar op deze situatie volledig mis (nofi).

[ Voor 41% gewijzigd door JaQ op 16-09-2003 14:25 ]

[Verwijderd]

Ja ik had idd ook mijn twijfels (wat redundantie/consistentie betreft) over je laatste post, jcraane...

mijn vraagje is iig een lekker hersenkrakertje...

[ Voor 14% gewijzigd door Verwijderd op 16-09-2003 14:08 ]

[Verwijderd]

neem een aanloopje en *schop*

Zou het volgende ook mogelijk zijn, zonder dat het fouten gaat veroorzaken:

Tabellen, zonder relaties!:

code:

DIENST            ACTIVITEIT        ACTIVITEITREGEL
---------------------------------------------------
*dID              *aID              *arID
code              code              code
                  dienstcode        activiteitcode
datum             datum             datum
status            status            status
naam              naam              naam
omschr            omschr            omschr
                  respons           budget
                  rapportage        actueel

Ik wil hier dus geen directe relaties tussen leggen (* = sleutel). Referentiele integriteit wil ik opvangen in mijn .net codering.

- HistorieGegevens filteren door uit te gaan van een activiteit en daarbij de activiteit regels te selecteren met hetzelfde ActiviteitID en de dichtstbijzijnde datum (NIET jonger). En natuurlijk ook de bijbehorende dienstrecord met dichtstbijzijnde datum (NIET jonger)
- Actuele gegevens filteren door te kijken naar de status (='actueel')

Op deze manier hoef ik maar 1 record van een tabel toe te voegen in geval van een wijziging.

Zou dit een goede manier zijn? Wat is jullie mening?

[ Voor 75% gewijzigd door Verwijderd op 29-09-2003 09:56 ]