Strukturierung, versionierte Tabelle - schneller vs. schlicht

[Mario]

Hallo,

Ich möchte meine Datenbank optimaler (Geschwindigkeit) strukturieren. Andererseits ist mir
der bisher einfache Aufbau (mit nur einer Tabelle) ans Herz gewachsen.

Die Tabelle die ich habe, sammelt einfach nur Textdaten und ordnet hier Datei-/Seitennamen
zu. Es sollen aber mehrere Versionen gespeichert werden. D.h. jedes mal wenn eine Seite
geändert wird, kommt ein neuer Eintrag dazu. So kann man immer nachvollziehen, wer wann
was geändert hat.
Dazu hab ich in der Tabelle einfach ein Feld "version" (1, 2, 3, 4, ...). Bisher hat das
auch funktioniert. Es war aber recht langsam, jedesmal die aktuellste Version rauszusuchen.
Dafür ist immer ein ORDER BY oder verschachteltes SELECT nötigt, und so sind die Abfragen
viel umständlicher.

Die Lösung wär natürlich die aktuellste Version einfach nochmal in einer separaten Tabelle
abzulegen. Ich fand das aber ganz witzig und bisher ausreichend alles in einer einzigen
Tabelle zu haben. Der Anwendungszweck ist einfach genug, und ich hätte am liebsten die DB
genauso schlicht wie das Programm.

Daher hatte ich überlegt, ob es nicht reicht, die letzte Versionsnummer in DERSELBEN Tabelle
zu vermerken. Sähe dann ungefähr so aus:

CREATE TABLE seiten (
   name TEXT,
   version NUMERIC
   lastversion NUMERIC,
   ...
   textcontent TEXT,
   lastchanged NUMERIC,
   options NUMERIC,
   extradata TEXT,
);

Es gibt noch mehr Felder, aber das ist ja erstmal egal.
Die Daten wären dann z.B.:

name       | version | lastverson | textcontent      | ...
----------------------------------------------------------
startseite |       1 |          1 | Inhalt...
aktuelles  |       1 |          3 | Erster Entwurf
aktuelles  |       2 |          3 | Zweiter Entwurf
aktuelles  |       3 |          3 | Vorerst Fertig

Und die Abfragen und Suchen nach der aktuellen Version eines Eintrags wären recht einfach
mit: SELECT * FROM seiten WHERE name = ? AND version = lastversion

Im Prinzip funktioniert das genauso, wie mit einer zweiten lastversion-Tabelle (die ich sonst
bei der Seitensuche reinJOINen würde).
Der Unterschied -und Nachteil- (?) hier ist natürlich, dass ich den korrekten Wert für
lastversion vielfach speichere. Das Feld muss ich für jede Zeile eines Seiteneintrags
aktualisieren lassen, kommt mal ne neue Seite dazu.

Hier mach ich meiner Datenbank u.U. viel mehr Arbeit. So wahnsinnig viele Aktualisierungen
gibt es andererseits nicht. Und die Datenbank wird schon nicht kollabieren, falls mal doch 50
lastversion Felder aktualisiert werden müssten. Üblich sind eh mehr so 1 bis 10 Versionen;
und wenn der Textinhalt und die restlichen Felder 10-20KB ausmachen, fällt das beim Schreib-
vorgang dann ja nun auch nicht soooooo gewaltig ins Gewicht.

Worum's mir aber eher geht: Sind solche Tabellenstrukturen vertretbar?

Das verstößt sicher voll gegen die Regeln der 7. Normalform oder so, aber sieht für mich
halt nach der effizient-schlichtesten Lösung aus.

(Wenn ich wirklich auf Geschwindigkeit optimieren will, müßte ich ohnehin eher das name-
Feld auslagern und Datenbank-interne numerische Indizes einführen ... mit letztlich mehr
Aufwand FÜR MICH, dann gleich drei Tabellen konsistent zu halten.)

Oder kennt vielleicht jemand von Euch gute Beispiele für versionierte Datenbanken? MediaWiki
hat ja sowas, aber finde mich da kaum durch (zu viel Code, desw. unübersichtlich.) Bei Google
find ich nicht viel, und die Anleitungen im Self-Linkverzeichnis sind auch nur Einführungen.
(Links zu "SQL in 21 Tagen" und "Managing Data Integrity" fkt. derzeit scheinbar nicht.)

Beste Grüße,
Mario

[globe]

n'abend,

Worum's mir aber eher geht: Sind solche Tabellenstrukturen vertretbar?

In zwei unterschiedlichen Projekten verwenden wir zwei unterschiedliche Ansätze.

1. Eine Tabelle:
   Alle Daten, auch die alten Versionen, werden in der selben Tabelle gespeichert. Dabei hat die Tabelle die Spalten recfrom und rectill, welche Timestamps enthalten. recfrom gibt dabei an wann der Datensatz erstellt wurde, rectill gibt an wann der Datensatz durch einen neuen ersetzt wurde. Der aktuellste Datensatz hat dabei immer rectill = 0. Für einen einfacheren Umgang (lesen) gibt es einen VIEW: SELECT * FROM table WHERE rectill = 0; Man spart sich also das lästige und langsame Suchen nach der aktuellsten Versionsnummer.

2. Drei Tabellen
   Die aktuellen Daten sind in einer Tabelle gespeichert. In einer Tabelle sind die Versionsnummern, Autor der Änderung, Kommentare, etc gespeichert. Die veralteten Datensätze sind wiederum in einer eigenen Tabelle mit Referenz auf die Versions-Tabelle gespeichert. Für die Versionen wird eine eigene Tabelle eingesetzt, weil es viele versionierte Tabellen gibt und multiple Datensätze in der selben Revision verändert werden können.

Der erste Ansatz hat den großen Vorteil, dass die Datenbank überschaubar klein bleibt. Allerdings hat sie den Nachteil, dass das Spiel mit der referentiellen Integrität nicht so wirklich toll funktioniert. Was für eine saubere Datenstruktur irgendwie untragbar ist.

Der zweite Ansatz hat zwar den Nachteil, dass man auf einmal viel mehr Tabellen hat, bringt aber mehrere Vorteile mit sich. Zunächst ist die referentielle Integrität kein Problem. Auf den zweiten Blick kann man mit dieser Struktur viele Änderungsoperationen unter einer Revisionsnummer (mit lauter tollen Metadaten) ablegen (und notfalls wiederherstellen).

Datenbanksysteme, die Schemas kennen (z.B. PostGreSQL), sind für den zweiten Ansatz besonders geeignet. Praktisch die gleiche Struktur kann in getrennten Schemas abgelegt werden. Organisatorisch ist das ein Traum...

weiterhin schönen abend...

[Mario]

Hi globe,

Danke für deine Beispiele zum Thema!

Der aktuellste Datensatz hat dabei immer rectill = 0. Für einen einfacheren Umgang (lesen) gibt es einen VIEW: SELECT * FROM table WHERE rectill = 0;

Auf die Idee war ich gar nicht gekommen. Find ich aber wirklich raffiniert.

Ich hatte auch schon überlegt, mein versions-Feld auszusparen, denn letztlich erfüllt der lastchanged-Timestamp den selben Zweck. Andererseits muss ich die Versionsverwaltung auch irgendwo in der Benutzerschnittstelle unterbringen, und da machen sich kurz Versionsnummern (1, 2, .. 57) doch besser als lange Zeitstempel (1190712403). Aber es ist ja eh nicht so wichtig welches Feld ich nun einspanne.

Auf meinen Fall angewendet, benötige ich ja keine Zeitabschnitte, sondern einfach nur einen Indikator wie rectill=0. Ich könnte also einfach ein Feld nextversion (statt lastversion) verwenden.
Dann finde ich auch immer die aktuellste Version mit nextversion=0 oder IS NULL -- was in der DB sicher schneller geht als dutzende Vergleiche (version=lastversion?). Und obendrein spare ich mir damit die mglw. langsamen Aktualisierungen aller lastversion-Einträge bei Seitenupdates.

Der zweite Ansatz hat zwar den Nachteil, dass man auf einmal viel mehr Tabellen hat, bringt aber mehrere Vorteile mit sich. Zunächst ist die referentielle Integrität kein Problem. Auf den zweiten Blick kann man mit dieser Struktur viele Änderungsoperationen unter einer Revisionsnummer (mit lauter tollen Metadaten) ablegen (und notfalls wiederherstellen).

Hier war ich immer unsicher, ob MySQL sowas hinbekommt. Wenn die Datenbank natürlich sicherstellen kann, daß Fremdschlüssel _immer_ automatisch mit neu eingefügten oder geänderten Datensätze aktualisiert werden, wär das super.
PostgreSQL hab ich zwar hier laufen, aber bei meinem Provider nur MySQL. Und genaugenommen würd ich am liebsten SQLite ausprobieren. Und ich fürchte, es bliebe dann an mir, die Daten konsistent zu halten. Und an irgendeinem Punkt tauchen dann die Datenbank-internen Referenzschlüssel in der ganzen Applikation (oder schlimmer noch: in der Benutzeransicht) auf. Auch aus dem Grund wollte ich sowas eher ungern verwenden.

Aber das kann ich mir ja noch überlegen, und das Datenbankschema ist ja nicht in Stein gehauen. Falls die DB wirklich zu groß wird, kann man sie immernoch korrekt optimieren.

Wieauchimmer, danke erstmal für die tollen Tips!
Mario

[Frank (no reg)]

Hi,

das es langsam läuft, liegt vielleicht einfach nur daran, dass die Spalte "Version" nicht von einem Index erfasst ist und dadurch nicht schneller auf die Werte zugriffen werden kann.

Alle Versionen von allen Dokumenten inkl. deren "Inhalt" in einer einzigen Tabelle zu verwalten, ist genauso legitim, wie eine Tabelle für den Versionsbaum und eine Tabelle für den Inhalt zu verwenden.

Bei der Variante mit einer Tabelle solltest du natürlich auf einen sinnvollen Primärschlüssel bzw. einen sinnvollen UNIQUE-Schlüssel achtgeben, der ua. auch die Versionsnummer beinhaltet.

Es gibt dann verschiedene Ansätze die Versionsverwaltung zu implementieren, was ua. auch davon abhängt, wie die Versionsverwaltung funktionieren darf: linear oder verzweigt, mit Vorwärtszeiger (Folgeversion) oder Rückwärtszeiger (Vorgängerversion). Auch hängt es von den Möglichkeiten des Datenbanksystems ab.

Du kannst jeweils die Versionsnummer für neue Versionen eines Dokuments inkrementieren (nicht exkrementieren bitte ;)), oder die aktuellste Version (siehe globe's Beitrag) immer mit "0" führen und alle älteren versionen dann jeweils um 1 updaten (also zurückverlagern).

Ein mögliches Designmuster für Baumstrukturen in rleationalen Datenbanken sind auch "Nested Sets" welche etwas schreiblastig sind (1 Insert mit mehreren Updates) und dafür recht einfach zu lesen und zu analysieren sind. Wenn also nicht zuviel Schreiblast erfolgt, dann könnte sich das durchaus eignen.

CVS bzw Subversion haben z.b. eine etwas andere Struktur mit ua. mehrteiligen Revisionsnummern die auch eine Verzweigung darstellen können, die man natürlich auch implementieren könnte. Lineare Versionen haben fortlaufende Nummern, verzweigte Versionen bekommen dann eine Unterrevision und laufen dort auch wieder linear:

1 ->                         2
      1.1 ->         1.2
               1.1.1
                             .... 1.2.23

Dazu müsstest du dann eine gewisse Logik schreiben, die solche mehrteiligen Revisionsnummern verarbeiten kann.

Du siehst, viele Wege führen nach Rom. :)

Gruss, Frank

[Mario]

Hallo Frank,

Die lineare Versionsverwaltung reicht für meine Zwecke völlig aus. So komplexe Versions-Strukturen wie in CVS/Subversion wären definitiv overkill. Wenn man sowas ohne extra Aufwand bekommen könnte, wärs natürlich eine tolle Funktion, sofern man es doch mal irgendwann brauchen würde. (So viel Konjunktiv!!! hihi)

das es langsam läuft, liegt vielleicht einfach nur daran, dass die Spalte "Version" nicht von einem Index erfasst ist und dadurch nicht schneller auf die Werte zugriffen werden kann.

Die beiden Spalten name und version hatte ich schon als Schlüssel verwenden. Nur diese beiden kommen ja hier zwangsläufig in Betracht.
In MySQL hatte ich glaub ich dazu "PRIMARY KEY intern___ (name, version)" verwendet.

Das ist aber trotzdem ein interessanter Punkt. Ich frage mich nämlich, ob es wirklich hilft auf version (und nach globe's Posting auch nextversion) wirklich einen INDEX zu erzeugen. Denn letztlich liegen ja dann im Index hunderte Einträge mit 1 (respektive 0), die die aktuelle Version darstellen.

Ich bin mir nicht sicher, ob so ein Index mit extrem wenig unterschiedlichen Werten (z.B. 500 mal version=1, 50 mal version=2, 10 mal version=3, ...) die Suche wirklich beschleunigt.

Worauf ich hinaus will. Die Suche im *CHAR*-INDEX für name ist schon nicht besonders flink. Und mit dem INTEGER-INDEX von version wird das Durchforsten nicht besonders gut eingeschränkt (-> z.B. nur 500 statt 700 Stringsuchen).

Okay, aber eigentlich wollte ich mir wegen solcher Detailoptimierungen erstmal eh nicht den Kopf zerbrechen.... Egal.

Alle Versionen von allen Dokumenten inkl. deren "Inhalt" in einer einzigen Tabelle zu verwalten, ist genauso legitim, wie eine Tabelle für den Versionsbaum und eine Tabelle für den Inhalt zu verwenden.

Supi. Darauf wollte ich hinaus. Ich war mir halt nur nicht sicher, welche Nachteile (mal abgesehen von Schreibvorgängen) sowas bilden könnte.

Aber mittlerweile hab ich sogar doch was bei Google dazu gefunden. Nennt sich "SELF JOIN" - und, jetzt wirds witzig ;) - der erste Treffer lag bei:
http://aktuell.de.SELFhtml.org/artikel/datenbanken/fortgeschrittene-joins/selfjoin.htm

Du kannst jeweils die Versionsnummer für neue Versionen eines Dokuments inkrementieren (nicht exkrementieren bitte ;)), oder die aktuellste Version (siehe globe's Beitrag) immer mit "0" führen und alle älteren versionen dann jeweils um 1 updaten (also zurückverlagern).

Ich hatte mir schon überlegt, die Versionnummern negativ hochzuzählen, und die aktuelle einfach auf 0 zu lassen. Dann war mir aber eingefallen, daß ich in der Benutzeransicht doch irgendwo die aktuelle Versionsnummer nennen muss. Und wenn ich in der DB nur 0 stehen hab (statt der echten z.B. 27) hab ich hier wieder Aufwand mit dem Zuordnen. (Falls version "27" angefordert wird, muss ich erstmal erraten, daß 0 gemeint war. Wenn ich die 0 aus der Datenbank lese, muss ich zusätzlich den nächsten Wert suchen um die 27 anzeigen zu können.)

Du siehst, viele Wege führen nach Rom. :)

Das hab ich befürchtet. hihi

Aber ich nehm definitiv den Lösungsweg für Faule...(!)
 ;)
Beste!
Mario

[Vinzenz Mai]

Hallo Mario,

Aber mittlerweile hab ich sogar doch was bei Google dazu gefunden. Nennt sich "SELF JOIN" - und, jetzt wirds witzig ;) - der erste Treffer lag bei:
http://aktuell.de.SELFhtml.org/artikel/datenbanken/fortgeschrittene-joins/selfjoin.htm

hast Du auch den ersten Satz unter

Zu beachten:

auf dieser Seite gelesen?

Freundliche Grüße

Vinzenz

[Mario]

Hallo Vinzenz,

http://aktuell.de.SELFhtml.org/artikel/datenbanken/fortgeschrittene-joins/selfjoin.htm

hast Du auch den ersten Satz unter

Zu beachten:

auf dieser Seite gelesen?

Jetzt, wo du das so direkt ansprichst. - Nee.

Mir gings aber nur um die Thematik Zwei-Tabellen-in-Einer, und fand den Artikel halt interessant weil er schön zum Thema passt. (Man muss halt nur erstmal wissen, wie's heißt!)

Gerade in meinem Anwendungsfall wären ja extrem viele widerholende Werte für version vorhanden. Lohnt sich also daher nicht, hier die Datenbank (kartesisches Produkt, gelle?) eine virtuelle Liste erstellen zu lassen.

Bei mir reicht es völlig, die Tabelle an einem Kriterium zu filtern. Und da ist der Trick mit rectill/nextversion von globe sicher der schlaueste.

In speziellen Such-Anfragen (z.B. Text-Unterschiede zwischen Versionen finden) wärs aber vielleicht dennoch nützlich...

G!
Mario

[Vinzenz Mai]

Hallo Mario,

http://aktuell.de.SELFhtml.org/artikel/datenbanken/fortgeschrittene-joins/selfjoin.htm

[...]

Mir gings aber nur um die Thematik Zwei-Tabellen-in-Einer, und fand den Artikel halt interessant weil er schön zum Thema passt.

Danke, das lese ich gerne.

Freundliche Grüße

Vinzenz

[EKKi]

Mahlzeit,

Dazu hab ich in der Tabelle einfach ein Feld "version" (1, 2, 3, 4, ...). Bisher hat das auch funktioniert.

Sowas ähnliches habe ich hier auch gerade.

Es war aber recht langsam, jedesmal die aktuellste Version rauszusuchen.
Dafür ist immer ein ORDER BY oder verschachteltes SELECT nötigt, und so sind die Abfragen
viel umständlicher.

Ich weiß ja nicht, wie weit MySQL mittlerweile in dem Bereich ist, aber in Oracle kann ich z.B. relativ einfach mit Partitionen alle Seiten herausholen, die - bezogen auf ihre jeweilige "seitenid" - die höchste "version" haben:

SELECT MAX(s.version) OVER (PARTITION BY s.seitenid) AS max_version  
,      s.*  
  FROM seiten s  
 WHERE s.version = max_version  

Wenn Du dann - wie Frank anmerkte - dafür sorgst, dass auf der Kombination von "seitenid" und "version" ein UNIQUE-Index liegt, und Du Dir einen INSERT-Trigger bastelst, der beim Neuanlegen eines Datensatzes immer die nächste freie Version für die jeweilige "seitenid" sucht und in den Datensatz schreibt, sollte das recht einfach und auch flott funktionieren.

Wie gesagt: gesetzt den Fall, MySQL kann so etwas schon (dafür bin ich dort zu lange raus - hab in der letzten Zeit nur Oracle gemacht).

MfG,
EKKi