Historische Datenhaltung

[MartinHan]

Hi,
meine Auftraggeber sind verzückt, das die Ballettschule jetzt so eine gut funktionierende DB hat, also Access FE und MS Sql Be.
Alle funktioniert problemlos und performant...
Aber wie sagt der Engländer: Give them an inch, they take a mile!
Jetzt möchten sie historische Datenhaltung, also in der Historie wissen, was eine Kunde in der Schule alles so gemacht hat:
•   Änderungen der Personendaten
•   Änderungen der Kursbelegungen
•   Ggf. Zahlungsmoral
Ich musst da erstmal schlucken, denn ich denke das ist nicht ganz so trivial.
Technisch fallen mir dazu natürlich sofort Trigger ein, aber zunächst geht es erstmal um das Design.
Nehmen wir mal ein winziges Beispiel, an dem man aber das Prinzip gut erklären kann.
Es gibt eine Tabelle Person, der Einfachheit halber jetzt nur mit den Spalten PersonId (Pk), Nachname und Vorname.
Dann gibt es eine Tabelle Partner mit den Spalten personid(fk zu personid), RolleId und PartnerId (pk)
Dann gibt es eine Tabelle Konto mit einem Kontoinhaber. Diese Spalte Kontoinhaber ist aber keine Spalte aus der Personentabelle, sondern aus Partner, mit der RolleId, die der Rolle Kontoinhaber entspricht. Der Grund dafür ist, das die Person noch andere Rollen in der Schule einnehmen kann und darüber ist man sehr flexibel.
Ok.
Nun kommt die Historie ins Spiel. Man könnte z.B. eine Tabelle History_Person anlegen, die dann die Spalten PersonId (aus der ursprünglichen  Tabelle) NachName, Vorname und einen neuen PK PersonId_his sowie ein Spalte event, die die Werte insert, update, delete annehmen kann und da wird die Historie abgelegt, dazu kommt noch das Eventdatum.
Ähnlich wäre es mit der Tabelle Partner die dann so eine History Tabelle hätte : personid, partnerid, Rolleid, partnerid_his (pk), event, eventdatum.
Nur frage ich mich dann, wie dann die RI-Beziehungen zwischen den history Tabellen wären, sie können ja eigentlich nur zwischen den neuen Spalten sein...
Das ist alles noch ganz ins Unreine gesprochen und ich stehe noch ganz am Anfang und wollt nur mal ein paar Ideen sammeln.
Aber auch jeden Fall ein spannendes Thema.

[Bitsqueezer]

Hallo Martin,

SQL Server bietet dazu von Haus aus History-Tabellen an. Dazu muß man ein paar zusätzliche Felder in die betreffenden Tabellen einfügen zur History-Verwaltung und SQL Server handhabt dann Änderungen in Tabellen automatisch in die entsprechenden History-Tabellen.

Infos siehe hier:
https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables?view=sql-server-ver16
https://sqlspreads.com/blog/temporal-tables-in-sql-server/
https://www.google.de/url?sa=t&source=web&rct=j&opi=89978449&url=https://www.youtube.com/watch%3Fv%3D5Ohrm3GMD_A%26pp%3DygUPI2RldmJodW1pZGVvcmlh&ved=2ahUKEwi1rY67ipGLAxWr8LsIHeGmK1IQwqsBegQIHxAE&usg=AOvVaw2EcSkBw0BXaRGgGzzZq7QO

Die Bindung an die History kann allerdings Schwierigkeiten mit sich bringen, wenn es z.B. um das einzelne Kopieren von Tabellen geht.

Ich würde Dir empfehlen, Deine Auftraggeber auf die DSGVO hinzuweisen, da es um personenbezogene Daten geht und noch dazu mit einer Art von Profilbildung aus den Daten, was problematisch ist.
https://www.srd-rechtsanwaelte.de/blog/profiling-neuerungen-dsgvo-bdsg

Gruß

Christian

[PhilS]

Jetzt möchten sie historische Datenhaltung, also in der Historie wissen, was eine Kunde in der Schule alles so gemacht hat
:
•   Änderungen der Personendaten

Bzgl. der Personendaten wäre das wohl eine Änderungshistorie. - Das ist eine gängige Funktionalität und du wirst sicherlich bei einer Recherche zahlreiche Informationen und Beispiele dazu finden. Ein guter Startpunkt sind die von @Bitsqueezer erwähnten Temporal Tables.
Vorsicht bzgl. Datenschutz. In der Historie speicherst du Daten, die für die Geschäftsbeziehung mit dem Kunden eigentlich nicht mehr erforderlich sind. Für einen gewissen Zeitraum lässt sich das mit der Nachvollziehbarkeit von Änderungen rechtfertigen, aber nicht unbegrenzt lange.

•   Änderungen der Kursbelegungen
•   Ggf. Zahlungsmoral

Diese Daten sind eigentlich in sich bereits historisch.
Ein Kunde belegt einen Kurs für eine bestimmte Zeit (Von/Bis).
Eine Rechnung wird an einem Datum ausgestellt und an einem folgenden Datum, ggf. nach Mahnung, bezahlt.
Wenn du diese Informationen nicht absichtlich löscht, sind die bereits historisch nachvollziehbar.
Falls es dir hier um eine rein technische Änderungshistorie geht, siehe oben.

[MartinHan]

Hi,

danke für eure Tipps. ich bin erstaunt über mich, das ich das mit der Änderungshistorie bei den Personendaten schon hinbekommen habe. Die Zeiträume-Spalten definiert, bei einem Kunden eine Hausnummer geändert und schon wurde ein historischer Satz angelegt. Toll.
Ab wie das mit den Beziehungsdaten ist, ist mir noch schleierhaft. Anhand der Rechnungen wird es nicht gehen, es gibt viele Kurs, die das gleiche kosten und auf der Rechnung, die aber keine Rechnung ist, sondern nur eine Zeile in einem Lastschrifteinzug, sind die Kurse nicht vermerkt.
Zu einer Kursbelegung kommt man über Person>Partner>vertrag>Kursbelegung. Bei der Kursbelegung steht dann zu welchem Vertrag sie gehört und welcher Kurs besucht wird.
Aber lasst mich mal fummeln, vielleicht klappt es ja.

Ich werde berichten.

Und danke!

Martin

[PhilS]

Anhand der Rechnungen wird es nicht gehen, es gibt viele Kurs, die das gleiche kosten und auf der Rechnung, die aber keine Rechnung ist, sondern nur eine Zeile in einem Lastschrifteinzug, sind die Kurse nicht vermerkt.

Die Rechnung hatte ich für den Stichpunkt "Zahlungsmoral" erwähnt.

Zu einer Kursbelegung kommt man über Person>Partner>vertrag>Kursbelegung. Bei der Kursbelegung steht dann zu welchem Vertrag sie gehört und welcher Kurs besucht wird.

Da hast du dann ja die Information. Von wann bis wann ein Kurs belegt wird, musst du ja sowieso speichern. Es wird ja häufiger vorkommen, dass ein Kunde *jetzt gerade* einen Kurs belegt, aber auch jetzt schon klar ist, dass er diesen Kurs ab einen gewissen Stichtag nicht weiter belegen wird.

[MartinHan]

Hi,

ich kann auf keinen Fall erwarten, das alle die Abläufe in einer Ballettschuile kennen.
Die meisten Kurse sind Dauerkurse, es gibt kein definiertes Ende.
Einige Kurse gibt es schon seit 30 Jahren!
Die Schüler melden sich an und bleiben für unbestimmte Zeit in dem Kurs bis sie kündigen oder der Kurs mangels Masse aufgelöst wird, was aber selten vorkommt.
Das ist im Übrigen auch der große Unterschied zu Tanzschulen, da bucht man einen Kurs, der dann für n Monate läuft und dann bucht man den nächsten.
Das ist auch der Grund, warum es auf dem Markt fast keine Kaufsoftware für Ballettschulen gibt. Es gibt haufenweise für Tanzschulen. Es gibt zwar eine, aber die ist bezgl. Datenmodell so grottenschlecht, das ich mich entschieden habe es selbst zu machen.
Sie haben dieses Rollenmodell nicht verstanden. Im Extremfall kann es so sein:
  Eine Mutter meldet ihr Kind zu einem Kurs an, sie ist also Vetragspartner
  das Kind ist Kursteilnehmer und belegt einen Kurs
  Die Mutter belegt selbst auch einen Kurs und ist also neben Vertragspartner auch Kursteilnehmer
  Die Kursgebühren werden von der Oma bezahlt, die ist Kontoinhaber

Ich möchte natürlich jede Person nur einmal im System haben und muss dann die Rollen verwalten, was über die Partnertabelle schon seid vielen Jahren wunderbar klappt.
Die Kaufsoftware, die sich oben erwähnte, hat für die verschiedenen Rollen die Personen redundant gespeichert, da läuft es mir kalt den Rücken runter...
Aber ich will das Forum nicht langweilen mit meiner speziellen Problemstellung und werde mich ab morgen wieder der Historie widmen.

Cu, Martin

[Bitsqueezer]

Hallo Martin,

ist nicht langweilig, man kann immer nur dazulernen..
Ein Kurs für Balett über 30 Jahre? Da sollte man doch meinen, alles gelernt zu haben. Ist es dann nicht eher sowas wie eine Mitgliedschaft in einem Fitnessklub, also nur als Übung, um nicht aus derselben zu kommen? Kann mir nicht vorstellen, daß man da noch Neues lernt...

Gruß

Christian

[MartinHan]

Hi,

ja der Kurs bleibt, nur die Mitglieder wechseln natürlich. Der Vergleich mit dem Fitnessstudio passt schon, ich buche also eine Leistung auf unbestimmte Zeit.
Bei Kindern ist es etwas anders. Die durchlaufen, je nach Begabung, eine Reihe von Kursen: Tänzerische Früherziehung 1, TF 2 , Tf3...bis sie irgendwann "groß" sind und beim Ballett bleiben.
D.h. Die Kurse bleiben gleich und die Schüler durchlaufen sie, ähnlich wie Stufen in der Schule.
Die historische Datenhaltung soll eben genau das transparent machen, den Werdegang der Schüler durch die Schule und den Kursen.
Wenn man die hat, kann man die Schüler auch besser beraten, welche Möglichkeiten sie haben. Desweiteren kann man unternehmerisch sehen, welche Kurse gut laufen, und wo mehr Abgänge sind.

Wie das nun rechtlich zu bewerten ist, da werde ich drüber nachdenken.

MfG

Martin

[MartinHan]

Zu dem rechtlichen:

In unseren Verträgen steht:

Mit der Speicherung und Auswertung der Daten nur für den internen Gebrauch bin ich einverstanden.

Ich denke, das dürfte reichen oder?

Martin

[Knobbi38]

Hallo Martin,

diese Formulierung dürfte noch nicht reichen. I.d.R. wird eine solche Einwilligungserklärung so gestaltet, daß sie nochmal extra unterschrieben werden muß.

Aber hier noch ein paar zusätzliche Infos:
https://www.datenschutz.org/einwilligungserklaerung/

Gruß
Knobbi38

[MartinHan]

Ok...sehe ich mit an.

Jetzt habe ich die Temporalen Tabellen eingereichtet.
Wenn ich in Access eine Änderung in der Tabelle Person mache, kommt der Reservierte Fehler -7776
Im ssms kann ich aber einen Update durchführen.
Bei anderen Tabellen geht es, nur bei der personen Tabelle nicht
 Eine Idee?

Martin

[Bitsqueezer]

Hallo Martin,

also ich habe mal einen Test mit A2013 und SQL Server 2022 und ODBC Treiber 17 gemacht.

Die Testtabelle:

Code [Auswählen] Erweitern

CREATE TABLE Produkte (
    ProduktID INT PRIMARY KEY CLUSTERED,
    Produktname VARCHAR(100) NOT NULL,
    Preis DECIMAL(10, 2) NOT NULL,
    GueltigVon DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    GueltigBis DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (GueltigVon, GueltigBis)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProdukteHistorie));

Tabelle verlinken und neue Datensätze hinzufügen geht ohne Probleme.
Beim Ändern bestehender Daten gibt es dagegen ein Problem, hier scheint SQL Server mit seinen internen Triggern zur Verwaltung der History-Tabelle dann im Zusammenhang mit Access Probleme zu bekommen, daher der Fehler.

SSMS funktioniert mit .NET und hat daher eine Offline-Verarbeitung, was ganz anders funktioniert.

Aber man kann sich ja abschauen, was SSMS da macht und mit Hilfe des SQL Server Profilers findet man den passenden UPDATE-Befehl. SSMS verwendet dazu sp_executeSQL, aber das ist natürlich nicht nötig, man kann auch direkt per UPDATE arbeiten, auch von Access aus. Man muß es halt nur mit ADO machen, dann geht es.

Für die Beispieltabelle oben habe ich nach der Verlinkung schnell ein Auto-Formular erstellt mit folgendem Code:

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Not Me.NewRecord Then
        If UpdateProdukt(Me.ProduktID, Me.Produktname.OldValue, Me.Preis.OldValue, Me.Produktname, Me.Preis) Then
            ' Verwerfen der Access-Änderungen
            Cancel = True
            Me.Undo
            Me.Requery
        End If
    End If

End Sub


Private Function UpdateProdukt(ProduktID As Integer, alterProduktname As String, alterPreis As Double, neuerProduktname As String, neuerPreis As Currency) As Boolean

    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim strSQL As String

    On Error GoTo Fehlerbehandlung
   
    ' Verbindungsobjekt erstellen
    Set cn = New ADODB.Connection

    UpdateProdukt = False

    ' Verbindungsparameter
    Dim strConnection As String
    strConnection = "Driver={ODBC Driver 17 for SQL Server};Server=DeinServer;Database=DeineDatenbank;Trusted_Connection=Yes;"
   
    ' Verbindung öffnen
    cn.Open strConnection

    ' Command-Objekt erstellen
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cn

    strSQL = "UPDATE Produkte " & _
             "SET Produktname = ?, Preis = ? " & _
             "WHERE (ProduktID = ?) " & _
             "  AND (Produktname = ?) " & _
             "  AND (Preis = ?)"

    cmd.CommandType = adCmdText ' Wichtig: adCmdText für sp_executesql
    cmd.CommandText = strSQL
   
    cmd.Parameters.Append cmd.CreateParameter(, adVarChar, adParamInput, 100, neuerProduktname) '1. Parameter
    cmd.Parameters.Append cmd.CreateParameter(, adCurrency, adParamInput, , neuerPreis) '2. Parameter
    cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , ProduktID) '3. Parameter
    cmd.Parameters.Append cmd.CreateParameter(, adVarChar, adParamInput, 100, alterProduktname) '4. Parameter
    cmd.Parameters.Append cmd.CreateParameter(, adCurrency, adParamInput, , alterPreis) '5. Parameter
   
    ' Ausführen der Anweisung
    cmd.Execute

    Set cmd = Nothing
    cn.Close
    Set cn = Nothing

    UpdateProdukt = True

Ende:
    If Not cn Is Nothing Then
        If cn.State = adStateOpen Then cn.Close
    End If
    Set cmd = Nothing
    Set cn = Nothing
Exit Function

Fehlerbehandlung:
    MsgBox "Fehler beim Aktualisieren des Produkts: " & Err.Description, vbCritical
    Resume Ende
End Function
Das Beispiel ist natürlich Gaga, es sollte beim WHERE natürlich nur die PK-ID der Tabelle verwendet werden. Aber es zeigt, wie es im Prinzip funktioniert.

Der Trick ist, nur den UPDATE bei Access zu verhindern, was in Form_BeforeUpdate geschieht. Wenn es ein INSERT ist, dann geht es ohne Probleme mit Access, daher die Frage nach NewRecord.
Wenn es ein bestehender Datensatz ist, wird erst versucht, den UPDATE "hintenrum" zu erledigen und wenn das erfolgreich war, mit Cancel=True der UPDATE von Access verhindert und die Änderungen im Formular rückgängig gemacht, so daß es für den Access-User so aussieht, als wäre eine ganz normale Speicherung im Hintergrund gelaufen.

Mit der Methode funktioniert es einwandfrei auch mit system-versioned Tabellen.

Alternativ kannst Du das natürlich auch mit einer SP machen, aber das wäre dann schon wieder aufwendiger. Ein einfacher UPDATE-Befehl tut es völlig.

Requery: Eigentlich genügt normalerweise ein Refresh, wenn man nur einen UPDATE durchführt. Der Datensatzzeiger bleibt auf dem gleichen Datensatz, Refresh kann nur kein INSERT und DELETE aktualisieren. Leider funktioniert das hier nicht, ohne Requery gibt es dann die Fehlermeldung, daß ein anderer Benutzer Änderungen gemacht hat.

Da bei Requery wieder zum ersten Datensatz gesprungen wird, muß man sich dann die ID merken vor dem UPDATE und mit FindFirst wieder zum Datensatz zurückspringen. Wenn man Application.Echo abschaltet, sieht man das im Frontend auch nicht. Für Endlosformulare habe ich dazu mal eine Klasse geschrieben, findest Du auf meiner Downloadseite unter "CCReposition". Hierbei wird dann auch die Bildschirmposition des Datensatzzeigers nach einem Requery wiederhergestellt (ohne würde der Datensatz zum ersten in der angezeigten Liste werden, auch wenn der Datensatz vorher in der Mitte stand).

Gruß

Christian

[MartinHan]

Hi,

der Fehler hat sich erledigt. Durch viele Fehlversuche war da wohl was strubbelig.
Ich habe die DB platt gemacht un neu angelegt, un bei den Kerntabellen die Historie angeknipst.

Jetzt geht es weiter...Die Darstellung der Historien in Access Frontend...
Es gibt doch in Access doch auch diese Formulare mit den Reitern, die könnte man doch gut für die Historien nehmen wenn es nicht zu viele sind.

Dein Hinweis auf ADO ist gut, werde ich mir ansehen. Das Ganz muss aber intensiv getestet werden, ehe man das Produktiv macht.
Aber der Anfang ist jetzt gemacht.

Danke!

Martin

[MartinHan]

Hi,

die Darstellung der Versionen im Formular Personen ist nicht das Problem.
Ich muss es nur umstellen von einem gebundenen Formular zu einem unbebundenen und kann dann die historischen Daten, die ich in einer Listbox auswählen kann, in dem gleichen Formular darstellen.
Es werden auch nicht so viele Versionen sein, nur wenn mal jemand umzieht oder sich die Handynummer geändert hat, also überschaubar.
Spannend wird es, wenn das Beziehungsgeflecht komplexer wird...

Aber jetzt nicht mehr...hallo Rotwein!

Martin

[MzKlMu]

Hallo,

Jetzt möchten sie historische Datenhaltung, also in der Historie wissen, was eine Kunde in der Schule alles so gemacht hat:
•  Änderungen der Personendaten
•  Änderungen der Kursbelegungen
•  Ggf. Zahlungsmoral

Ich frage mich, was mit einer Datenhistorie bezweckt werden soll.
Wenn die Datenbank korrekt normalisiert aufgebaut ist, so sind doch die Daten an sich schon historisch.
Bei Änderungen der Personandaten, so ist das doch in einem neuen Datensatz abzubilden, sonst würden sich alte abhängige Datensätze auch auf neue Personendaten beziehen.
Wozu also Daten in Historietabellen verschieben ?
Die ganzen Zusammenhänge der Schlüsselfelder gehen doch verloren.
Und Speicherplatz wird doch da auch nicht gespart.
Wenn man alte Daten ausblenden will, so kann man doch z.B. in der Kunden/Personentabelle ein Feld einbauen (Archiv z.B.) um nicht mehr benötigte Daten nicht anzuzeigen. Die über Fremdschlüssel abhängige Daten werden dann ja auch nicht mehr angezeigt.
Und was den Datenschutz betrifft, können ja die Daten auch in den Originaltabellen anonymisiert werden.

Also, wozu Daten in extra Tabellen auslagern ?

Ich hoffe, ich habe das Ansinnen richtig verstanden und mein Beitrag ist nicht ganz daneben.