Query mit 1:n-Relation

[Gsus]

Hallo,

und schon wieder stehe ich vor einem kleinen Problem.

Ich programmiere gerade ein kleines Backup-System für den Eigengebrauch und habe dort nun folgende Datenbankstruktur erstellt:

Tabelle 1: global_backups
Hier sind allgemeine Informationen zu den Backups enthalten

backup_id|datetime|...
1|2012-01-20 12:15:23|...
2|2012-01-19 15:24:26|...

Tabelle 2: global_backups_kinds
Hier sind die Backup-Arten zu den Backups gespeichert (MySQL oder FTP)

backup_id|kind
1|mysql
2|mysql
2|ftp

Tabelle 3: global_backups_projects
Hier sind die Projekte gespeichert die in den einzelnen Backups gespeichert sind

backup_id|project_name
1|Projekt 1
2|Projekt 1
2|Projekt 2

Aus diesen Tabellen brauche ich nun ein Array, das wie folgt aussieht:

array("backup_id" => [ID],
       "datetime" => [datetime],
       "kind" => array([0 => ftp],
                            [1 => mysql]),
       "projects" => array([0 => "Projekt 1"],
                                 [1 => "Projekt 2"])
)

Da ich hierfür per MySQL leider keine direkte Lösung gefunden habe, baue ich mir dieses nun per PHP zusammen:

$result = $mysql->query("SELECT `backup_id`, DATE_FORMAT(`datetime`, '%%d.%%m.%%Y %%H:%%i:%%s') AS `datetime`, `kind`, `project_name` FROM `global_backups` LEFT JOIN `global_backups_kinds` USING(`backup_id`) LEFT JOIN `global_backups_projects` USING (`backup_id`) WHERE `project_name` ORDER BY `datetime` DESC");
while($row = mysql_fetch_assoc($result)) {
	if(!isset($backups[$row['backup_id']])) {
		$row['color'] = ($i%2==0)?"dark":"light";
		$row['kind'] = array($row['kind']);
		$row['project_name'] = array($row['project_name']);
		$backups[$row['backup_id']] = $row;
		$i++;
	} else {
		if(!in_array($row['kind'], $backups[$row['backup_id']]['kind'])) $backups[$row['backup_id']]['kind'][] = $row['kind'];
		if(!in_array($row['project_name'], $backups[$row['backup_id']]['project_name'])) $backups[$row['backup_id']]['project_name'][] = $row['project_name'];
	}
}

Soweit so gut, nun führt diese Vorgehensweise allerdings zu einigen Problemen:

Problem 1:
Eine Blätterfunktion um die Einträge auf mehrere Seiten zu verteilen ist mit MySQL und LIMIT x, y nun nicht mehr möglich, da ich nicht weiß, welche Datenreihen zusammengehören, bevor ich das PHP-Script drüberlaufen gelassen habe. Ich müsste also generell alle Backups auslesen und dann mit PHP auf die verschiedenen Seiten verteilen.

Problem 2:
Eine Suchfunktion, die nach einzelnen Projekten sucht und dennoch alle in diesen Backups enthaltene Projekte ausgibt ist nun auch nicht mehr möglich, denn wenn ich eine WHERE `project_name` IN (...) einfüge, erhalte ich ja logischerweise auch im Result nur die Daten, nach denen ich gesucht habe und nicht mehr die anderen Daten. für eine derartige Suchfunktion müsste ich also auch wieder alle Daten auslesen und dann mit PHP diese Daten durchsuchen.

In der Hoffnung, nicht bereits alle mit dem ganzen Text abgeschreckt zu haben: Gibt es hierfür vielleicht eine intelligentere Lösung mit MySQL?

Vielen Dank im Vorraus.

mfg
Gsus

 

[joschilein]

Ich verstehe ehrlich gesagt deine Tabellenstruktur nicht so ganz. Was sollen denn z.B. diese Projektnamen aussagen? Wäre es nicht möglich wenigstens die Tabellen 2 und 3 in einer zu verschmelzen?

Und vielleicht ging es sogar mit einer Tabelle. Es könnte schließlich auch mehrere Backups geben. Zwei könnten den letztlich selben Zeitpunkt haben aber verschiedene Typen beinhalten.

Vermutlich wird das schon irgendwie ohne PHP-Umweg gehen, aber du müsstest eben mal erklären was der Zweck sein soll.

 

[theHacker]

So von Tabellennamen- und Inhalten würd ich von 2x n:m-Beziehung ausgehen. Also dass Kinds und ProjectNames sowas wie Tags für ein Backup sind. Falls ja, wären hier erstmal zwei neue zusätzliche Tabellen angebracht, um n:m ordentlich zu mappen.

 

[Gsus]

mhh eigentlich sind es zwei 1:n beziehungen

und zwar: ein backup kann mehrere (unbegrenzt viele) Projekte beinhalten. und ein backup kann auch ein oder zwei arten (kinds) nämlich ftp, mysql oder eben beides beinhalten.

dabei ist dann eben in der backups-Tabelle die ID der PK, und in den anderen 2 jeweils die Kombination aus ID und zweitem feld.

Das ganze soll dann den Sinn haben, dass man sowohl Backups von nur einem Projekt und einer Art (z.B. nur MySQL-backup eines Projektes) machen kann, oder auch von dem ganzen Server (alle Projekte, MySQL+FTP).

 

[theHacker]

mhh eigentlich sind es zwei 1:n beziehungen

und zwar: ein backup kann mehrere (unbegrenzt viele) Projekte beinhalten.

Und nachdem ein Projekt einmal gebackupt wurde, kann es dann nie mehr gebackupt werden (1:n) oder möchtest du vielleicht noch ein zweites, drittes, ... Mal sichern (n:m)?

und ein backup kann auch ein oder zwei arten (kinds) nämlich ftp, mysql oder eben beides beinhalten.

Da diese 2 fix sind, sind das zwei Bool-Attribute am Backup, also keine extra Tabelle. In MySQL kannst du das mit einem SET in einer einzigen Spalte abbilden.
Deiner Formulierung nach ist es nämlich nicht möglich, in einem Backup Projekt A nur MySQL und Projekt B mit MySQL+FTP zu sichern.

 

[Gsus]

Und nachdem ein Projekt einmal gebackupt wurde, kann es dann nie mehr gebackupt werden (1:n) oder möchtest du vielleicht noch ein zweites, drittes, ... Mal sichern (n:m)?

naja schon: 1 Backup : n Projekte, nicht n Backups : m Projekte
Darum liegt der PK auch auf der Kombination von Backup-Id und Projektname. Dadurch kann ja ein Projektname in beliebig vielen Backups vorkommen.

Da diese 2 fix sind, sind das zwei Bool-Attribute am Backup, also keine extra Tabelle. In MySQL kannst du das mit einem SET in einer einzigen Spalte abbilden.
Deiner Formulierung nach ist es nämlich nicht möglich, in einem Backup Projekt A nur MySQL und Projekt B mit MySQL+FTP zu sichern.

darüber habe ich auch nachgedacht, allerdings habe ich dann ja auch einige unnötige Werte abgespeichert. Eigentlich möchte ich nämlich nur abspeichern, welche Werte wahr sind und nicht auchnoch welche unwahr sind, denn das kann ich ja einfach aus dem wahren ableiten.

mfg
Gsus

 

[tobomator]

ich würde mal die Abfrage erweitern... (anders machen)
Also die Backup_ID ist ein PK?
Und wenn inden Tabellen jeweils der PK den gleichen Namen besitzt, so versteh ich das hier jedenfalls, dann kann man auch nen einfachen JOIN machen mittels ",".
Nun meine Variante mittels normalem JOIN (table1,table2,table3) : WHERE (tablename.Backup_ID = tablename2.Backup_ID AND tablename.Backup_ID = tablename3.Backup_ID)

Ich weißleider nicht mehr wo der unterschied zwischem dem inner join, left join, right outer join oder so liegt, aber bisher habe ich alles mit einem normalen join lösen können, wenn die Struktur der tabellen drunter mindestens die 3.NF erfüllt

 

[theHacker]

Darum liegt der PK auch auf der Kombination von Backup-Id und Projektname. Dadurch kann ja ein Projektname in beliebig vielen Backups vorkommen.

Hä? Versteh ich ned.
Backupst du Projekte oder Projektnamen? Ich mach immer Sicherungen von den Projekten. Der Name wird ja durch das Projekt vorgegeben, den muss ich also nicht sichern

Primary Key auf ID+Projektname is Quark. Eine ID-Spalte is implizit immer eine UID, also die is nie an ein anderes Datum geknüpft (außer an einen Foreign Key bei n:m).

Ich käme also auf:

• Project (ProjectID, Name)
• Backup (BackupID, Kind, DateTime)
• BackupProject (ProjectID, BackupID)

darüber habe ich auch nachgedacht, allerdings habe ich dann ja auch einige unnötige Werte abgespeichert.

Nochmal: Hä?
Wo werden unnötige Werte abgespeichert?

edit:
Solltest du so einen Fall doch noch konstruieren wollen, wie ich in Post #5 im unteren Absatz geschildert hab, könntest du Kind von Backup in BackupProject schieben.

 

[Gsus]

Okay, offensichtlich habe ich mich falsch ausgedrückt Ich versuche es noch einmal.

Meine aktuellen Tabellen:
backups(backup_id, datum,...)
backups_kind(backup_id, kind{mysql, ftp})
backups_projects(backup_id, project_name)

eine Projekte-Tabelle habe ich ebenfalls:
projects(project_name,...)

Darüber hinaus habe ich die nötigen foreign keys (die sich hier durch die Namensgebung der Tabellenspalten denke ich selbst erklären) vergeben und auf CASCADE gestellt.

Dadurch ist mein project_name sozusagen eine project_id (nur eben in anderen datenformat)!

Es ist möglich, dass:
a) ein Backup nur FTP, oder nur MySQL oder beides
b) ein Backup mehr als ein Projekt umfässt

Es ist nicht möglich, dass:
a) ein Backup für Projekt 1 nur FTP und für Projekt 2 nur MySQL-Daten enthält (auch nicht in anderen Kombinationen)

Daher kommt meine Struktur deiner doch schon sehr dicht, nur dass ich kind ebenfalls ausgelagert habe. Ich denke nun habe ich auch verstanden, was du mit deinen zwei bool Attributen meintest. Du meintest ein extra Feld ("kind") in der Bakup-Tabelle wo nun eine 0 (=nur FTP), 1 (=nur MySQL) oder 2(=FTP+MySQL) drinsteht? Habe ich das so richtig verstanden?

Ich verstand folgendes:
Zwei extra boolean spalten ("mysql" + "ftp") in denen dann jeweils eine 0 oder 1 drinsteht. dadurch habe ich unnötige Informationen gespeichert (nämlich ich speichere, was nicht in Backup enthalten ist, wenn ich eine 0 in der Tabelle habe. Diese Daten kann ich mir aber ableiten, wenn ich nur speichere, was da schon enthalten ist, denn alles andere ist bekanntlich nicht enthalten).

Mit diesem Ansatz, könnte ich die Tabellenanzahl aber in der Tat um 1 verringern. Das löst nur leider immernoch nicht das eigentliche Problem Vielleicht hast du dazu auch noch eine Idee?

mfg
Gsus

 

[theHacker]

Dadurch ist mein project_name sozusagen eine project_id (nur eben in anderen datenformat)!

Böse, reine Speicherplatzverschwendung, aber wenn du garantieren kannst, dass ein Projekt niemals seinen Namen ändert, vo DB-Design her ok.

Es ist nicht möglich, dass: [...]

Ok, verstanden. Dann bleibt dieses Attribut also an der Sicherung, nicht am n:m-Glied.

Du meintest ein extra Feld ("kind") in der Bakup-Tabelle wo nun eine 0 (=nur FTP), 1 (=nur MySQL) oder 2(=FTP+MySQL) drinsteht? Habe ich das so richtig verstanden?

Nicht, (0, 1, 2), sondern (1, 2, 3), aber ja. Spart auch wieder immens Speicher gegen deinen VARCHAR, den du da vermutlich hast.


Jetzt, wo das DB-Design klar is (ich geh also davon aus, dass deine kind-Tabelle verschwindet und als Attribut am Backup hängt), kann man sich dem Problem widmen.

Problem 1: Da du ne n:m-Beziehung hast, wirst du wohl zwei Abfragen machen. Der JOIN - wie du schon richtig festgestellt hast - bringt unterschiedlich viele Zeilen pro Backup und kann somit nicht nach Backup seitenweise geblättert werden.
Lösung:

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] [/FONT][FONT=Courier New]backup_id, [/FONT][FONT=Courier New]...
[B][COLOR=#9932cc]FROM[/COLOR][/B] backups
[B][COLOR=#9932cc]LIMIT[/COLOR][/B] x, y

[B][COLOR=#9932cc]SELECT[/COLOR][/B] ...
[B][COLOR=#9932cc]FROM[/COLOR][/B] backups_projects
[B][COLOR=#9932cc]WHERE[/COLOR][/B] backup_id [B][COLOR=#9932cc]IN[/COLOR][/B] (...)[/FONT]

Du kannst die Abfrage probieren, zu optimieren, wenn die erste Abfrage z.B. nach backup_id sortiert ist, kannst du mit deren Minimal- und Maximalwert statt dem IN (...) ein BETWEEN min AND max machen. Musst du aber probieren; kp, ob das überhaupt was bringt.

Problem 2: Die erste Abfrage aus Problem 1 wird um eine Unterabfrage erweitert.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] ... [B][COLOR=#9932cc]FROM[/COLOR][/B] backups
[B][COLOR=#9932cc]WHERE[/COLOR][/B] backup_id [B][COLOR=#9932cc]IN[/COLOR][/B] (
  [B][COLOR=#9932cc]SELECT[/COLOR][/B] backup_id
  [B][COLOR=#9932cc]FROM[/COLOR][/B] backup_projects
  [B][COLOR=#9932cc]WHERE[/COLOR][/B] project_name = 'dein Suchbegriff')
[B][COLOR=#9932cc]LIMIT[/COLOR][/B] x, y[/FONT]

Die Frage, ob sich das optimieren lässt, bleibt natürlich offen. Ich hab eben mal überlegt, ob da ein einfacher JOIN reicht, aber ich tippe drauf, dass du ne zweite Instanz von backups brauchst, da du (s.o.) die Spalte backup_id in zwei unterschiedlichen Kontexten brauchst. Probiers einfach aus. EXPLAIN hilft

 

[tobomator]

Hallo,

Tabelle 1: global_backups
snipp...

Aus diesen Tabellen brauche ich nun ein Array, das wie folgt aussieht:

array("backup_id" => [ID],
       "datetime" => [datetime],
       "kind" => array([0 => ftp],
                            [1 => mysql]),
       "projects" => array([0 => "Projekt 1"],
                                 [1 => "Projekt 2"])
)

...
Gsus

Ich würde folgendes Design wählen:
n ist eine ganze Zahl
ist DATETIME nicht ein reserviertes Wort bei sql ?

create table global_backups (
   backup_id  int(n),
   time_val datetime,
     primary key (baclup_id)
);

create table global_backups_info (
   backup_id int(n),
   kind int(n),
   project_name varchar(n),
   foreign key (backup_id) references global_backups(backup_id)
);

glaube das verstößt gegen die 3.NF, wenn du nun zu einer ID mehr als 1 project_name hast...
Kind vom Typ int, weil 1 = ftp, 2 = mysql, 3 = ... was in php dann einfach auszuwerten ist.

[B]select[/B] backup_id, time_val, kind, project_name 
  [B]from[/B] global_backups [B]as[/B] gb, global_backups_info [B]as[/B] gbi  
  [B]where[/B] gb.backup_id = gbi.backup_id;

Würde man eine 3 Tabelle machen, wo nur die Backup_id mit dem Namen drin stehen, dann müßte auch in der Info dort ein Foreign Key auf die ID in der 3 Tabelle erfolgen. Dann kann man einfach alle project_name's selecten unter einer bestimmten ID, die sicher auch sortierbar wären!

 

[Gsus]

Problem 2: Die erste Abfrage aus Problem 1 wird um eine Unterabfrage erweitert.

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] ... [B][COLOR=#9932cc]FROM[/COLOR][/B] backups
[B][COLOR=#9932cc]WHERE[/COLOR][/B] backup_id [B][COLOR=#9932cc]IN[/COLOR][/B] (
  [B][COLOR=#9932cc]SELECT[/COLOR][/B] backup_id
  [B][COLOR=#9932cc]FROM[/COLOR][/B] backup_projects
  [B][COLOR=#9932cc]WHERE[/COLOR][/B] project_name = 'dein Suchbegriff')
[B][COLOR=#9932cc]LIMIT[/COLOR][/B] x, y[/FONT]

Okay, vielen Dank! Dein Ansatz für mein erstes Problem klingt plausibel, das zweite wird hiermit jedoch noch nicht gelöst (denke ich zumindest) da ich auch die Möglichkeit haben möchte, nach Backups zu suchen, die sagen wir mal 5 bestimmte Projekte enthalten. Mit deinem Ansatz habe ich in der Subquery allerdings wieder nur einen Datensatz, der nicht mit den anderen verbunden ist.

mfg
Gsus

 

[theHacker]

Mit deinem Ansatz habe ich in der Subquery allerdings wieder nur einen Datensatz, der nicht mit den anderen verbunden ist.

Doch, du solltest alle kriegen. Du kannst aber nicht nach mehreren suchen.

Bsp:
Backup 1 enthält Projekte A, B, C.
Backup 2 enthält Projekte A, D.

Suchst du nach Projekt A, kriegst du Backup 1 und Backup 2.
Suchst du nach Projekt B, kriegst du nur Backup 1.
Suchst du nach Projekt D, kriegst du nur Backup 2.

Du kriegst deshalb alle, weil du ja explizit alle Projekte (unabhängig von einer Suche) aus der n:m-Beziehung abfragst (s. zweite Abfrage bei Problem 1).

edit:
Verständlicher: Die Suche schränkt nur nach den Backups ein, nicht nach den BackupProjects-Zeilen. Die werden ausgehend von den Backups nachgeladen.

 

[Gsus]

Und wieder habe ich mich unverständlich ausgedrückt

Ich meinte damit: Das result des Subquerys ist nicht mit den zum gleichen Backup gehörigen anderen Datensätzen der gleichen Tabelle verbunden, weshalb ich nicht nach mehreren Projekten suchen kann.

Um dein Beispiel zu benutzen:

Backup 1 enthält Projekte A, B, C.
Backup 2 enthält Projekte A, D.
Backup 3 enthält Projekte A, B, D.

Nun möchte ich nach A und B suchen können und Backup 1 und 3 erhalten, also alle Backups, in denen alle angegebenen Projekte (oder mehr) enthalten sind.

mfg
Gsus

 

[theHacker]

Nun möchte ich nach A und B suchen können und Backup 1 und 3 erhalten, also alle Backups, in denen alle angegebenen Projekte (oder mehr) enthalten sind.

Ohne groß nachzudenken würd ich jetzt im Subselect n Auto-JOIN auspacken. Je mehr Projekte im Suchausdruck sind, desto komplexer wird dann die Abfrage.

In dem Fall würde ich dann eher dazutenden, dann du alle ProjectBackups aus der DB holst, die zu einem gesuchten Projekt passen, also in deinem Beispiel

[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] backup_id
[B][COLOR=#9932cc]FROM[/COLOR][/B] backup_projects
[B][COLOR=#9932cc]WHERE[/COLOR][/B] project_name [B][COLOR=#9932cc]IN[/COLOR][/B] ('Projekt A', 'Projekt B')
[B][COLOR=#9932cc]ORDER[/COLOR][/B] [B][COLOR=#9932cc]BY[/COLOR][/B] backup_id, project_name [/FONT][FONT=Courier New][B][COLOR=#9932cc]ASC[/COLOR][/B][/FONT]

, wobei hier die Besonderheit die Sortierung nach der Backup-ID und danach nach dem Projektnamen is. In PHP gehst du dann der Reihe nach durch und wirst alle Backup-IDs raus, die nicht alle gesuchten Projekte enthalten. Die Abfrage selber würde nämlich nur das Ergebnis "Finde alle Backups, in denen Projekt A oder Projekt B drin is"; du willst aber ein Und.

Das baust du dir dann relativ einfach in PHP. Es kommen der Reihe nach alle Projekte eines Backups, bevor das nächste Backup beginnt. Und die Projektnamen sind auch sortiert, d.h. du weißt genau in welcher Reihenfolge was zu erwarten is.