Normalisierung von Datenbanktabellen [6]

Dazu muss man ja auch nur das WHERE-Statement erweitern - zusätzliche JOINs sind dazu gar nicht notwendig:

WHERE K.Kombi = 'trans'
AND W1.Wort = ? AND
(W1.Sprache = ? AND W2.Sprache = ?) OR
(W1.Sprache = ? AND W2.Sprache = ?)

Da kann etwas nicht stimmen. Vor und nach dem OR steht exakt der gleiche Ausdruck. Der OR-Teil ist redundant. Mit AND statt OR wäre es schon redundant, mit OR wird es nicht besser.

Wahrscheinlich muß so ein OR-Teil in den ON-Teil, dann wird in beide Richtungen durchsucht und zwar weil, so wie ich mir das gleich gedacht hatte, von der einen zur anderen Spalte genau wie von der anderen Spalte zur einen hin durchsucht wird:

SELECT W2.Sprache, W2.Wort, Z.Art, Z.Info,
FROM Worte AS W1
INNER JOIN Kombis AS K
ON W1.id = (K.ein_wort OR K.anderes_wort)
INNER JOIN Worte AS W2
ON (K.ein_wort OR K.anderes_wort) = W2.id
LEFT JOIN Zusatz AS Z
ON Z.id = W2.id
WHERE K.Kombi = 'trans'
AND W1.Wort = ? AND W1.Sprache = ?
AND W2.Sprache = ?

Oder etwa nicht? Obwohl mir das schon noch ein bißchen suspekt vorkommt... Aber wahrscheinlich nur, weil ich es nicht gewohnt bin, von einer normalisierten Datenbank her zu denken.
Ranma

Ach so. Mein Versuch der Erweiterung im ON-Teil funktioniert in meinem Skript nicht. Es ist mir nur unmöglich zu sagen, ob das an meiner Unfähigkeit in Sachen SQL oder meiner Unfähigkeit in Sachen PHP liegt. Es gibt keinerlei Fehlermeldungen, nur kein Resultat. Auch in dem Versuch ging es mir nur darum, beide Übersetzungsrichtungen zugleich abzusuchen. Weil es ja nach wie vor sein kann, daß die ID des gesuchten Wortes in der rechten oder in der linken Spalte von Woerterbuch.Kombis steckt. Die ID der gesuchten Übersetzung ist automatisch in der jeweils anderen Spalte. Allerdings wird auch mein Formular nicht richtig angezeigt (in einer früheren Version wurde es das), also kann der Fehler auch anderswo als in der Query liegen.

Die Regeln der Normalisierung sollten erfüllt sein und zwar auch WEIL die beiden Spalten in Kombis gleichartig und gleichberechtigt sind?
Ranma

Ja, eben. Einerseits sagst du, die Indices der zu übersetzenden Wörter sollen in ein_Wort und die Indices der jeweiligen Übersetzungen in anderes_Wort stehen. Von welcher Richtung in welche Richtung ein Benutzer eine Abfrage machen will, kann ich aber garnicht wissen. Darum können die beiden Spalten die Rollen jederzeit tauschen. Ganz ohne mein Zutun. Andererseits weist deine rhetorische Frage darauf hin, daß kein Kriterium gibt, ob ein Wort in der einen oder der anderen Spalte mit seinem Index vertreten ist. Falls die Frage nicht rhetorisch gemeint war, dann gibt es trotzdem kein solches Kriterium. Nachdem ich also auch sonst kein Kriterium habe, nach dem es einen wesentlichen Unterschied zwischen ein_Wort und anderes_Wort geben und das man einem Benutzer erklären könnte, bleibt garnichts anderes übrig als die Query für die Suche in beide Richtungen zu erweitern.

Sollten danach natürlich redundante Ergebnisse herauskommen, dann mache ich die ganze Zeit einen Denkfehler. Nur sagst auch du nicht, daß die Ergebnisse redundant wären, sondern nur, daß die Performance leidet. Es ist ja völlig klar, daß man die Ergebnisse viel schneller bekommt, wenn man auf die Hälfte davon verzichtet. Noch schneller ginge es, wenn man auf sämtliche Ergebnisse verzichtete. Aber dann braucht man kein Programm schreiben. Das gilt für alle Programme, führt aber nicht dazu, daß keine Programme mehr geschrieben werden.

Beim Versuch die Ergebnisse, die bisher noch garnicht geliefert werden, als HTML-Ausgabe darzustellen, ist mir noch etwas aufgefallen. Muß die Query nicht jeweils genau vier Ergebnisse pro Datensatz liefern? Also ich muß vier Variablen vorsehen, um die Ergebnisse daran zu binden? Die Zusatzangaben können pro Wort jeweils eine, keine oder mehrere sein. Das bekomme ich so nicht datensatzweise als Ergebnis geliefert, weil das Ergebnis an eine feste Anzahl von Variablen gebunden wird. Das heißt, die Query funktioniert so oder so nicht, die Diskussion darum ist müßig und ich muß sowieso die Abfrage auf mehrere Queries verteilen?

Dann ist mir noch eingefallen, daß ich noch eine weitere Spalte benötige. Die soll die Zuverlässigkeit einer Übersetzung repräsentieren. Die Daten aus den Dateien anderer Wörterbuchprojekte dürften ziemlich korrekt sein, nur die unvollständige Atomisierung erfordert noch Verbesserungen. Das heißt in der zusätzlichen Spalte sollte dafür ein hoher Wert stehen. Bekomme ich jemals meinen Crawler zum laufen, dann wird der nur die Wiktionary durchsuchen und die halte ich für eine sehr zuverlässige Quelle. Solche Datensätze brauchen nicht mehr korrigiert werden. In der Spalte für die Zuverlässigkeit wird dafür ein sehr hoher Wert eingetragen. Trägt ein anderer Bot etwas ein, dann wird das ziemlich sicher Späm sein und keine Übersetzung. Das sollte dann nicht als Übersetzung verwendet, sondern entfernt werden. Das ergibt einen sehr niedrigen Wert für die Zuverlässigkeit eines Eintrages. Die Vertrauenswürdigkeit menschlicher Benutzer ist irgendwo dazwischen. Darum sollten sie Übersetzungen korrigieren oder Späm entfernen können, wo der Wert sehr niedrig ist, während die Datensätze mit einem hohem Wert in der zusätzlichen Spalte nicht von jedem geändert werden dürfen. Die Übersetzung durch einen menschlichen Benutzer erhält einen mittleren Wert für die Zuverlässigkeit. Sollte ich mal eine Benutzerverwaltung einbauen, dann werden die Benutzer noch weiter nach ihren Fähigkeiten differenziert. Also Leute mit fortgeschrittenen Fremdsprachenkenntnissen können dann von Anfängern erstellte Übersetzungen korrigieren. Meine Frage dazu: Darf diese zusätzliche Spalte direkt hinter die Kombinationen oder sollte sie wieder in eine eigene Tabelle, wofür die Kombinationen dann noch einen extra Index bräuchten?

Übrigens habe ich gelesen, bei der Verwendung von Prepared Statements bräuchte man auch mysqli_real_escape_string() genau wie sämtliche anderen früher für die Sicherheit verwendeten Funktionen nicht mehr verwenden... Darf ich das glauben?
Ranma

Wenn alle Datensätze nochmal spiegelbildlich in der Tabelle stehen, dann wird die Tabelle ja doppelt so lang. Das braucht den doppelten Speicherplatz. Dann muß man bei jedem neuen Eintrag und jeder Verbesserung jeweils beide Datensätze aktualisieren. Genau das soll Normalisierung eigentlich verhindern? Der Umbau wäre nur, um etwas Zeit bei einer einzelnen Query einzusparen. Wenn ich nun noch für jede weitere Query eigene Umbauten vornähme...

Da ist es doch besser, die Query spiegelbildlich zu wiederholen und mittels UNION zu verknüpfen? Das wäre meine aktuelle Version, aber noch nicht ausprobiert, weil erstmal wieder nur FALSE geliefert wird. Vermutlich wegen der Anzahl der Variablen, die sich dadurch auch wieder ändert.

Jetzt habe ich erst herausgefunden, daß man Fremdschlüssel noch explizit als FOREIGN KEY definieren muß, sonst hat die Verwendung von Fremdschlüsseln und damit der ganze Normalisierungsprozeß garkeinen Sinn.
Ranma

Ich sehe es eher so, dass die Hälfte der zu übersetzenden Wörter fehlen. Das mit dem spiegelbildlich ist m.E. deswegen auch nicht ganz korrekt, weil manche Wörter in einigen Sprachen in anderen Sprachen gar nicht als Wort übersetzt werden (sondern als Suffix, Betonung etc.).

Ja, ich weiß. Über den Punkt sind wir eigentlich schon hinaus. Die nicht als Wort übersetzten bereiten mir auch etwas Kopfzerbrechen. Sie stehen oft als eingeklammerte Erklärung an der Stelle eines übersetzten Wortes. Darum kann ich nicht einfach alles, was eingeklammert ist, in die Tabelle für die Zusatzangaben verschieben. Aber das ist nur ein Problem für die Atomisierung der Angaben in den Tabellen für die Wörter und die Zusatzangaben. Das hat eigentlich nichts mit der Paarung der Schlüssel für die Übersetzungen zu tun, die sich ja in einer weiteren Tabelle befinden. Die Hälfte der zu übersetzenden Wörter fehlt dann, wenn die Tabelle in nur einer Richtung durchsucht wird. Wird sie in beide Richtungen durchsucht, dann fehlt genau keines. Darum wiederum soll meine Query in beiden Richtungen suchen.

Ich fände es so auch logischer - und das "etwas Zeit" würde ich hier nicht unterschätzen

Ich habe ein bißchen zur Optimierung gelesen. Vor allem JOIN und UNION scheinen Queries langsam zu machen. Nur meine vorherigen Versuche mit JOIN ohne Schlüssel oder einer Konstruktion aus Subqueries wären noch langsamer. Inzwischen dürfte sich die Query kaum noch schneller machen lassen. Schneller als ein UPDATE oder ein INSERT sollte auch eine komplexe SELECT-Query sowieso sein? Sie müßte jetzt halt nur noch Ergebnisse liefern. Was sie irgendwie nicht macht...

Eigentlich habe ich zur Optimierung etwas gelesen, um herauszufinden, ob sich die Stored Procedure, die meine Tabellen in normalisierte Tabellen umsortiert, schneller machen läßt. Die einzige Möglichkeit dazu wäre vielleicht jeweils hundert Einträge in einer Transaktion zusammenzufassen. Aber ein Cursor kann in SQL immer nur stur einen Datensatz nach dem anderen abarbeiten. Das heißt, der Umbau würde kompliziert. Wahrscheinlich muß ich eine Schleife um die vorhandene Schleife legen, zwei Variablen für einen LIMIT-Teil definieren und diese in der äußeren Schleife erhöhen. Die innere Schleife würde natürlich die beiden Cursor bei jedem Durchlauf neu ansetzen müssen. Das könnte auch schiefgehen und das Skript noch langsamer machen.

Ich würde nicht so vorgehen, weil es in deinem Modell kein sinnvolles Kriterium gibt, wann ein Wort in die Spalte ein_wort und wann ein Wort in die Spalte andere_wort einsortiert wird. In meinem Modell könnte man die Spalten vielleicht besser `suchwort` und `uebersetzung` nennen.

Zur Veröffentlichung benenne ich alles um. Vielleicht heißen die Spalten so. Vielleicht heißen sie Quetzal und Coatl. Ich bezweifle, daß sich das auf die Funktionsweise eines Programmes oder auch nur einer Query auswirkt. Die sollten eigentlich bezeichnungsinvariant sein. In meinem Modell gibt es deswegen kein sinnvolles Kriterium, welches Wort in welche Spalte kommt, weil es dafür objektiv kein sinnvolles Kriterium gibt. Bei einem zweisprachigem Wörterbuch hätte man natürlich schnell eines gefunden. Mein Wörterbuch, also die Tabelle Worte, enthält aber schon Wörter aus dem Deutschen, Englischen, Japanischen, Chinesischen (getrennt nach traditionell und vereinfacht), Türkischen, Niederländischen, Slowenischen, Russischen, Ungarischen, Afrikaans, Portugiesischen, Italienischen, Französischen und der obskuren Minderheitensprache Khasi. Die kann man nicht sinnvoll genau einer von zwei Spalten zuordnen. Es ist auch keine Beschränkung auf die genannten Sprachen vorgesehen. Die meisten Wörter stammen zwar aus Tabellen mit jeweils einer deutschen Übersetzung, aber manche sind nur mit japanischer Übersetzung vorhanden.

Will ich das alles mit deinem Modell erhalten, dann hängt dein Modell tatsächlich nur nochmal sämtliche Datensätze der Tabelle in spiegelbildlicher Form an die Tabelle an. Jede Aktualisierung müßte zweimal vorgenommen werden. Korrekturen sollen für häufige Aktualisierungen sorgen.

Ein FOREIGN KEY Constraint wird erst benötigt, wenn man z.B. verhindern möchte, dass nur Teile von verknüpften Datensätzen gelöscht werden.

Er verhindert also die Löschanomalie. Man kann die Normalisierung auch so erklären, daß damit die vier Anomalien (einfügen, löschen, ändern und aktualisieren) verhindert werden. Bei einer Anomalie geschehen die vorgenommenen Aktionen nur in einer Tabelle, obwohl sie auch andere Datensätze betreffen und daher dort Anpassungen vorgenommen werden müssen. Das heißt, die Normalisierung funktioniert erst mit dem FOREIGN KEY Constraint. Sonst war die gesamte für die Normalisierung getane Arbeit umsonst. Eine SELECT-Query fragt nur ab, was vorhanden ist, führt also nicht zu Anomalien. Das wollte ich auch nicht behauptet haben, sondern nur, daß die Normalisierung noch nicht abgeschlossen ist, solange der FOREIGN KEY Constraint noch fehlt. Trotzdem füge ich den noch nicht hinzu, weil ich befürchte, daß dadurch die Stored Procedure für die Normalisierung noch langsamer wird.
Ranma