Datenbank-Normalisierung

[Monger]

Hallo!

Ich bin in Visual Studio immer noch am rumprobieren, und ich bin gerade an dem Punkt, wo ich versuche eine vernünftige Datenhaltung aufzubauen. Wie schon in meinem vorigen Thread hier erwähnt nutze ich dazu ein Dataset.


Das Problem was ich jetzt habe, ist aber gar nicht so sehr technischer Natur, als dass ich einfach alles vergessen habe was ich mal über Datenbankennormalisierung gelernt habe! :ugly:
Ich muss also die Tabellen und die Relationen dazwischen aufbauen, und mich interessieren im Endeffekt ganz bestimmte Abfragen. Ich hoffe, mir kann jemand auf die Sprünge helfen.

Mal vereinfacht ausgedrückt, sieht mein Problem ganz ähnlich wie eine Bundesliga Tabelle aus. Ich will also folgende Informationen speichern:

- Name des Vereins
- Startnummer (also die Position, die der Verein zu Anfang der Saison in der Rangliste gehabt hat. Ist für Fußball zwar ziemlich irrelevant, für mich aber nicht)
- gewonnene Spiele
- verlorene Spiele
- alle Vereine gegen die dieser Verein bereits gespielt hat, und
- das genaue Ergebnis dieser Partie (kann ich mir dadurch vielleicht "gewonnene" und "verlorene Spiele" sparen?)

Aus diesen Informationen muss ich Runde für Runde dann folgendes berechnen:

- das Punkteverhältnis, also gewonnen zu verloren
- die Summe aller Punkte meiner bisherigen Gegner. Wenn also Verein A gegen Verein B und C gespielt hat, muss ich die aktuellen Punkte von B und C zusammenrechnen.


Vorallem die Abfragen machen mir Kopfzerbrechen. Egal in welche Tabellen ich die Informationen aufbreche - ich kann mir nur schwer vorstellen, wie ich daraus mit einer Abfrage die richtigen Informationen rausziehen soll. Wäre nett, wenn mir jemand wenigstens ein Stück weit auf die Sprünge helfen könnte.

[darph]

Oh Gott ist das lang her. Notation ist vermutlich völlig für'n Arsch, aber ich hoffe man versteht's. ;(

Team := {[id], [name], [startposition], [rank]}; // Startposition ändert sich ja nicht mehr, kann also hier bleiben. rank ist evtl. überflüssig und könnte durch eine kluge Query/View implementiert werden. Dann ist die Liste bereits sortiert.
Match := {[team_a_id], [team_b_id], [score_a], [score_b]}; //



gewonnene Spiele: SELECT * from Match m, Team t WHERE t.id = teama.id AND t.id = $id AND m.score_a > m.score_b ;

Verlorene Spiele genauso nur andersrum.

Ist nur blöd, weil komplizierter, wenn es Rückrunden gibt. ;(

[PHuV]

Da gibt es verschiedene Möglichkeiten.

Zuerst mußt Du im Primärschlüssel (Primary Key - PK, Secondary Key SD usw.) den Verein, Spieler, Trainer speichern, sei es direkt oder als Nummer, welche alle Infos zum Verein enthält. Regel, alles was sich nicht ändert, in eine Tabelle schreiben, für die Eindeutigkeit immer eine Nummer vergeben (z.B. Vereine, Spieler, Trainer). Alle sich veränderbaren Werte (Spiele) immer in separate Tabellen schreiben.

Tabelle Vereine

Vereinsnummer (PK) VereinsName Vereinssitz Gründungsdatum
01 Allemania Aachen Aachen 16. Dezember 1900
02
99 Würzburger Kickers 1907

Trennung von den veränderbaren Daten (Trainer, Vorstand, etc.).

Tabelle Trainer

TNr(PK)
Trainername (SK),
GebDatum,
aktueller Verein (nicht mehr aktiv = na)


Tabelle Spieler

SNr (PK)
Spielername
GebDatum,
aktueller Verein (nicht mehr aktiv = na)


Jetzt wird es schwierig, und ist von Deinen Ansprüchen abhängig. Führst Du die Spiele pro Verein, oder Jahr, oder pro Spielwoche?

Davon hängt dann später Deine Suche ab, und ein Tipp, lieber eine Information zuviel, als zu wenig, auch wenn es Redundanzen gibt (was nach Normalisierung eigentlich vermieden werden sollte). Es erleichtert die Abfragen und die Geschwindigkeit jedoch u.U. ernorm.

Man könnte die Tore jetzt gemeinsam in einer riesigen Spieltabelle unterbringen, oder man macht pro Spieltag und Jahr eine separate Tabelle, oder pro Verein eine Tabelle Spiel pro Jahr, oder eine Gesamttabelle mit allen Spielen. Jedoch hat das den Nachteil, für jedes Jahr eine neue Tabelle anlegen zu müssen, hat aber den Vorteil, pro Jahr verdichtet und schlank alle Daten zu finden. Machst Du feste Tabellen (z.B.) alle Spiele insgesamt, hast Du viele Redundanzen und irgendwann sehr viele Einträge, was die Tabellensuche groß und fett macht. Hier muß man dann geschickt mit Indizes arbeiten.

Man kann auch virtuelle Tabellen bilden, die jedes Mal neu erzeugt werden. usw. usw.

[Monger]

Ist nur blöd, weil komplizierter, wenn es Rückrunden gibt. ;(
Mach dir mal darum keine Gedanken. Gegen jeden Gegner wird nur ein einziges mal gespielt. Garantiert. In diesem Fall gibt es auch nicht sowas wie Heim- oder Auswärtsspiel.


Jetzt wird es schwierig, und ist von Deinen Ansprüchen abhängig. Führst Du die Spiele pro Verein, oder Jahr, oder pro Spielwoche?

Das mit der Bundesliga war nur ein Beispiel, weil wenn ich mit "Schweizer Turniersystem im Tischtennis" ankomme, wird damit kaum jemand was anfangen können.

Aber zu den Rahmenbedingungen:
Jedes "Jahr" wird getrennt betrachtet. Es geht hier nur um die Ausspielung eines einzigen Turniers, für jedes weitere mal wird ein völlig neuer Datensatz angelegt.
Die Begegnungen werden nach jeder Runde neu ausgehandelt, deshalb müssen mir nach jeder Runde alle Daten vorliegen. Reihenfolge sieht also etwa so aus:

- Begegnungen festlegen
- Ausspielen
- Ergebnisse eintragen
- neue Begegnungen festlegen etc.

Performance ist hier noch gar nicht so relevant. Wir reden hier über maximal ein paar hundert Datensätze, die halt nur relativ viele Kreuzbeziehungen haben. Das wird zur Laufzeit alles im Speicher liegen, und gelegentlich nur als XML raus- und wieder reinserialisiert. Sorgen um Zugriffszeiten o.ä. mach ich mir derzeit also nicht. Viel wichtiger ist mir, dass das ganze robust wird.


Mein erster Gedanke war dem von Darph ganz ähnlich. Nur: wie gehe ich mit zwei Vereinen in einer Query um? Ich kann die Reihenfolge nicht garantieren, ob jetzt also die Begegnung "TeamA vs TeamB" oder "TeamB vs TeamA" heißt, darf keinen Unterschied machen.

Mein zweiter Gedanke ging deshalb in Richtung:

Team := Startposition, Name, [sonstiges eindeutiges Pipapo]
Begegnungen := VereinNummer (selber Schlüssel wie Startposition), BegegnungNr.
Spielergebnisse := Id (gleichbedeutend mit BegegnungNr), Verein, erzielteTore

Im letzteren ist ja eine 1:2 Beziehung drin, d.h. jedes Spielergebnis hat zwei Vereine, die jeweils X Tore geschossen haben. Wenn ich also nach einer bestimmten Partie suche, kriege ich genau zwei Datensätze, die ich dann programmtechnisch auswerten könnte. Damit hätte man diese gegenseitige Doppeldeutigkeit raus, aber damit ist die Id als eindeutiger Schlüssel wieder nicht zu gebrauchen, oder?

[PHuV]

Mein erster Gedanke war dem von Darph ganz ähnlich. Nur: wie gehe ich mit zwei Vereinen in einer Query um? Ich kann die Reihenfolge nicht garantieren, ob jetzt also die Begegnung "TeamA vs TeamB" oder "TeamB vs TeamA" heißt, darf keinen Unterschied machen.

Mein zweiter Gedanke ging deshalb in Richtung:

Team := Startposition, Name, [sonstiges eindeutiges Pipapo]
Begegnungen := VereinNummer (selber Schlüssel wie Startposition), BegegnungNr.
Spielergebnisse := Id (gleichbedeutend mit BegegnungNr), Verein, erzielteTore

Im letzteren ist ja eine 1:2 Beziehung drin, d.h. jedes Spielergebnis hat zwei Vereine, die jeweils X Tore geschossen haben. Wenn ich also nach einer bestimmten Partie suche, kriege ich genau zwei Datensätze, die ich dann programmtechnisch auswerten könnte. Damit hätte man diese gegenseitige Doppeldeutigkeit raus, aber damit ist die Id als eindeutiger Schlüssel wieder nicht zu gebrauchen, oder?

Dann faße doch Begegnung und Spielergebnisse in einer Tabelle zusammen?

[Monger]

Dann faße doch Begegnung und Spielergebnisse in einer Tabelle zusammen?

Hm, also...

Spielergebnisse := Vereinsnummer, Begegnungsnummer, erzielte Tore


Wenn ich also nach der Vereinsnummer schaue, muss ich mir einfach nur alle Begegnungen ausgeben lassen, und weiß dann wieviel Punkte ich erzielt habe...

Klingt schonmal vernünftig. Aber wie finde ich raus, gegen wen mein Verein jetzt schon alles gespielt hat? Nach der Vereinsnummer filtern, und sich die jeweiligen Vereine ausgeben, oder? Wie sähe denn so eine SQL Query aus?

[Berni]

Ich würds so machen:
Vereine := VereinsID, Name [sonstiges eindeutiges Pipapo]
Begegnungen := Spieltag, VereinsID, BegegnungsID, Toregesch, Toreerhalten, Sieg (0 oder 1), Niederlage (0 oder 1), Heimspiel (0 oder 1)
Damit ist zwar ein bisschen Redundanz drin, aber die Auswertung für die Tabelle ist damit relativ einfach.

Die Tabelle eines Spieltags bekommst du dann folgendermaßen (ungetestet):
SELECT Vereinsname, sum(Toreerhalten)-sum(Toregesch) As Torverhaeltnis, sum(Toreerhalten) As TorerhaltenSumme,
sum(Toregesch) As ToregeschossenSumme, sum(Sieg) As Siege, sum(Niederlage) As Niederlagen
FROM Begegnungen LEFT JOIN Vereine USING(VereinsID) WHERE Spieltag<10 Group BY Vereinsname
ORDER BY Siege, Torverhaeltnis, ToregeschossenSumme, VereinsID
Musst dann nur noch die Zeilen abholen und ausgeben ;)

Über die BegegnungsID und Heimspiel bekommst du die genaue Begegnung und das Ergebnis raus. Eine Spieltagsübersicht für den ersten Spieltag z.B. über:
1. SELECT * from Begegnungen LEFT JOIN Vereine USING(VereinsID) WHERE Spieltag=1 ORDER BY BegegnungsID
2. Eine Begegnung besteht dann immer aus zwei aufeinanderfolgenden Datensätzen (wegen dem Order By). In deiner Programmiersprache kannst du das dann zusammenfügen, indem du aus dem ersten Datensatz erstmal alles rausholst an Infos, aus dem zweiten brauchst du nur den Teamnamen. Und je nachdem obs ein Heimspiel war oder nicht musst du halt die Teamnamen verschieden ausgeben. Also so in der Art (PHP, ungetestet):
$sql= "SELECT * from Begegnungen LEFT JOIN Vereine USING(VereinsID) WHERE Spieltag=1 ORDER BY BegegnungsID";
$res=$db->query($sql);
$lastnum=-1;
while ($row=db->fetch($res)){
if($row['Begegnungsid']!=$lastnum){
$lastnum=$row['Begegnungsid'];
$teama=$row['Vereinsname'];
$teamatore=$row['Toregesch'];
}else{
$teamb=$row['Vereinsname'];
$teambtore=$row['Toregesch'];
if($row['Heimspiel']==0) echo $teama.' vs '. $teamb. ' Tore: '.$teamatore.':'.$teambtore;
else echo $teamb.' vs '. $teama. ' Tore: '.$teambtore.':'.$teamatore;
}

[Berni]

Hat sich das mittlerweile erledigt oder wie hast dus nun gelöst?

[Monger]

Bin immer noch am basteln. Hab festgestellt, dass Visual Studio einem da doch etwas mehr Möglichkeiten lässt, wie man Relationen definiert. Ich hab mir so ein kleines Testprojekt angelegt, mit dem ich jetzt hier und da mal mit den Tabellen ein bißchen rumspiele, um ein bißchen sicherer darin zu werden.


Ich hätte halt ganz gerne eine unmittelbare Referenz von: Spieler A hat folgende Spiele...

Das Problem ist, dass das keine 1:n Relation ist, denn es haben nunmal zwei Spieler genau das selbe Spiel. Wie ich das vernünftig auflöse, ist mir immer noch nicht ganz klar.

[Trap]

n:m Relationen sind eigene Tabellen, die 2 Spalten mit Primärschlüsseln enthalten.

[wori]

Hier findet sich eine gute Beschreibung zu Datenbankdesign und Normalisierung:
http://www.cornelia-boenigk.de/pg/Datenbankdesign.pdf

[Monger]

Ich denke, ich habs gepackt. Ich hab mich jetzt für


Verein: Startposition, Name...
Spielergebnis: VereinA, VereinB, ErgebnisA, ErgebnisB...


entschieden.
Ich hab mehrmals versucht, zwischen Startposition und VereinA bzw. VereinB eine Beziehung zu schaffen, hab aber nicht verstanden dass ich das in Visual Studio auf zwei getrennte Verbindungen aufspalten muss.

Wenn man die erstmal hat, ist der Zugriff auf die jeweiligen Vater- bzw. Kindelemente erfreulich einfach. Muss dann halt von Hand berücksichtigen, dass ein Verein entweder auf der linken oder der rechten Seite stehen kann, aber es gibt schlimmeres. Ich hatte befürchtet, mich da erst mit komplizierten SQL Queries rumschlagen zu müssen, aber das ist jetzt glücklicherweise völlig überflüssig.