Nachvollziehbarkeit von Datenänderung

[bluestardev]

Hallo,

ich zerbrech mir nun seit geraumer Zeit den Kopf darüber, wie man am sinnvollsten Änderungen am Datenbestand protokollieren sollte/kann.

Ich habe bereits mehrere Foren zu diesem Thema durchstreift, allerdings keine befriedigende Antwort gefunden.

Es geht z.B. um eine Adresstabelle. Gegeben sei mal Vorname, Nachname, Straße, Plz und Ort.

Für eine Jahresstatistik - die monatlich aufgeteilt ist - ist es wichtig zu wissen, ob jemand Ortsfremd ist oder nicht, z.B. zieht eine Person im April in den Ort, dann ist diese Person von Jan-Mrz ortsfremd und von Apr-Dez eben nicht mehr.

Möglichkeit 1: Ich erstelle 2 Tabellen, z.B. Person (Id, Vorname, Nachname) und Adresse (Id, Straße, Plz, Ort, GültigAb, fkPersonId) - klassische 1:n-Beziehung. Für jede Adressänderung erstelle ich einen neuen Datensatz und bei der Abfrage lass ich dann nach GültigAb sortieren und mir den letzten Satz ausgeben.

Möglichkeit 2: Per Update-Trigger kopiere ich den Originalsatz in eine "Änderungstabelle". Ich müsste mich dann nur bei der Statistik mit mehrfach Datensätze rumschlagen.

Aus meiner Sicht, macht bei meiner Adresstabelle natürlich Möglichkeit 1 am meisten Sinn, da am einfachsten zu händeln.

Was mach ich aber, wenn ich z.B. auch die Änderung des Namens wissen muss, z.B. für eine Referenz auf eine Rechnung. Angenommen Frau Müller bestellt etwas, später wird der Name in Maier geändert, dann möchte ich natürlich die Rechnung noch immer mit Müller verknüpft wissen.

Ich habe bei Experts Exchange eine Hinweis auf einen zusammengesetzten Primärschlüssel gelesen, z.B. PersonId und Änderungsdatum. Leider wurde dort nicht genauer beschrieben, wie man das ganze umsetzt. Der Personen-Datensatz würde demnach mehrfach in der Tabelle vorkommen z.B. mit dem zusammengesetzten Primärschlüssel (1/20.01.2010) und (1/05.02.2010). Der jeweilige Schlüssel wäre dann in der Rechnungstabelle referenziert. Was mach ich dann aber mit meiner Adresstabelle? Die Adresse müsste ja beide Schlüssel referenzieren, d.h. ich bräuchte eine Interselektionstabelle (m:n), oder nicht?

Bin für jede Hilfe dankbar. Trotz Cross-Search gibts kein Cross-Posting

Gruß

[tinof]

Bin für jede Hilfe dankbar.

Ok, dann trau' ich mich hier mal mit meinem vielleicht etwas umständlichen Ansatz.
Ich habe die Variante 1 umgesetzt, also die zeitabhängigen Daten in eine eigene Tabelle 'ausgelagert' und 1:n mit der Basistabelle verknüpft.
Damit die Abfragen der Art 'welcher Datensatz war zum Zeitpunkt X gültig' einfacher und schneller werden, hat diese Tabelle folgende Struktur:
ID;giltAb,giltBis;[Daten]

Gefüllt wird sie so
1;NULL;01.01.2010;[Daten vom Anfang bis 01.01.2010]
1;02.01.2010;10.01.2010;[Daten vom 02.01. bis 10.01.2010]
1;11.01.2010;NULL; [Daten 11.01.2010 bis heute und ff]

Die 'normale' Abfrage sieht dann so aus

[highlight=sql]
SELECT * FROM [BasisTabelle]
LEFT JOIN [ZeitTabelle] ON [Basistabelle].[ID]=[ZeitTabelle].[ID] AND
ISNULL([Zeittabelle].giltab,{pDatum}) <= {pDatum} AND
ISNULL([Zeittabelle].giltbis,{pDatum}) >= {pDatum}
[/highlight]

Wobei pDatum der gewünschte Stichtag ist.

Um die Pflege giltab/giltbis kümmert sich eine Stored Procedure, die nach jedem Update in der Tabelle aufgerufen wird (Dank der tollen corelab - Komponenten sehr einfach und sicher zu bewerkstelligen).

Die Applikation fügt 'nur' einen Datensatz mit einem beliebigen [giltab] Datum ein und ruft die Funktion auf, die passt dann alle anderen Datensätze gemäß dem Schema automatisch an.

KKDetails = [Zeittabelle] aus obiger Erklärung
kkID = [ID Basisdaten]
kkdetID = [Primärschlüssel der Zeittabelle (Identity)]

[highlight=sql]
CREATE PROC WWS_AktualisiereKKDet
@kkID Integer
AS

declare @ab1 Smalldatetime, @bis1 SmalldateTime, @ab2 SmalldateTime, @bis2 SmalldateTime
declare @Id1 Int, @ID2 int
declare @cnt Int

declare curKKDet cursor for SELECT kkdetID, IsNull(giltab,0), IsNull(giltBis,0)
FROM KKDetails
WHERE KKId = @kkID
ORDER BY giltab,giltBis

OPEN curKKDet
-- erster Satz
SET @cnt = 1
FETCH curKKDet INTO @Id1, @ab1, @bis1

WHILE @@fetch_status = 0
BEGIN
--print cast(@cnt as varchar(5)) + '/' + cast(@id1 as varchar(5)) + ' / ' + CAST(@ab1 AS Varchar(15)) +' / '+ CAST (@bis1 AS Varchar(15))

IF (@CNT=1) AND (@Ab1 > 0)
BEGIN
UPDATE KKDetails
SET GiltAb = NULL
WHERE KKDetID = @ID1
END

FETCH curKKDet INTO @ID2, @ab2, @bis2

-- keine Datensätze mehr
IF @@fetch_status <> 0
BEGIN
UPDATE KKDetails
SET giltBis = null
WHERE kkDetID = @ID1
-- und weg
END
ELSE
-- nicht der letzte
BEGIN

-- print cast(@cnt as varchar(5)) + '/' + cast(@id2 as varchar(5)) + ' / ' + CAST(@ab2 AS Varchar(15)) +' / '+ CAST (@bis2 AS Varchar(15))

UPDATE KKDetails
SET giltBis = CASE
WHEN (@ab2) > ISNULL(giltAb,0) THEN @ab2 - 1
ELSE giltAb
END
WHERE kkDetID = @ID1

SET @ID1 = @ID2
SET @ab1 = @ab2
SET @bis1 = @bis2


END

set @cnt = @cnt + 1

END

CLOSE curKKDet
DEALLOCATE curKKDet


GO
[/highlight]

Vielleicht etwas umständlich, aber, wenn implementiert, prima abzufragen.

Viel Erfolg!
Tino

[StefanBO]

Ich würde auch zu Möglichkeit 1 tendieren.
Wenn Du diese Maier-Müller-Problematik abfangen willst, kannst Du natürlich auch mehrere 1:n-Tabellen erstellen.
Dann hast Du halt eine Kunden-Tabelle mit ID und "lagerst" sowohl Adressen als auch Namen in 2 Tabellen aus, in denen Du die Datensätze mit Gueltigkeits-Feldern versiehst.

[bluestardev]

Erstmal danke für die beiden Antworten.

Dachte es gibt irgendetwas eleganteres als alles auszulagern. Letzt endlich muss ich ja alle Eventualitäten berücksichtigen. Brauch man die Änderungen, brauch man sie vielleicht nicht?!

Das Maier/Müller Problem ließe sich natürlich dadurch lösen, dass man die Anschrift mit in die Rechnungstabelle aufnimmt, da ja letztendlich sie ein bestandteil der Rechnung sind. Auch wenn man letztendlich gegen die Normalisierungsregel verstösst.

Hat noch jemand ne Idee