Multiple Join - Intelligente Datenstruktur bei mehrsprachigkeit

[Mik3e]

Hi zusammen,

Hab eine kleine Logik-Frage (Special für HPVW :
Es geht darum, zu einem Hauptwert mehrere verschiedene Werte in einer anderen Tabelle zu speichern. Und zwar aufgrund von Mehrsprachigkeit. Klingt jetzt nicht logisch, nachfolgendes Beispiel sollte es allerdings erklären.

Angenommen es gibt eine Tabelle, in der Hersteller von Produkten gespeichert sind. Neben dem Namen des Herstellers (der in jeder Sprache gleich ist) gibt es auch noch eine Beschreibung sowie eine URL zu seiner Website, die natürlich in jeder Sprache anders sein wird.

tbl_hersteller:

herstellerID   |  name
-------------------------------
1     Mercedes Benz
2     Volkswagen

In dieser "Haupttabelle" sind nur jene Kerndaten gespeichert, die in jeder Sprache gleich sind. Nun gibt es für die Beschreibung eine zweite Tabelle, die dann auf den jeweiligen Datensatz, gebunden über die languageID, gejoined wird:

tbl_hersteller_description

descriptionID   | fk_herstelleID | fk_languageID | description
-------------------------------------------------------------------------------------
1   |   1  |   DE  |   Deutsche Beschreibung zu Mercedes Benz
2   |   1  |   EN  |   English description to Mercedes Benz
3   |   2  |   DE  |   Deutsche Beschreibung zu Volkswagen
4   |   2  |   EN  |   English description to Mercedes Benz

Soweit so klar.
ABER (das gibt es leider immer):
Es gibt nun ja auch noch die URLs zu den Herstellerwebsites, die für jede Sprache anders sein sollen. Natürlich könnte man nun genauso wie für die Beschreibung auch eine eigene Tabelle "tbl_hersteller_urls" anlegen, in der dann die URLs über die LanguageID gejoined werden.

Bevor ich das aber umsetze wollte ich mal hier in die Runde fragen, ob jemand eine elegante Lösung für diese Problemstellung hat, bei der man für alle Eigenschaften (Beschreibung, URL etc.) nur eine einzige Tabelle einsetzt.

Danke & Ciao,
Mike

Achja, obwohl hier relativ irrelevant:
DBMS: MySQL 5.0.15

 

[hpvw]

Hab eine kleine Logik-Frage (Special für HPVW :

Eigentlich eine Frage der Normalisierung, oder?

Angenommen es gibt eine Tabelle ...

Gibt es sie oder gibt es sie nicht? Bist Du Diplomvolkswirt?

Back to Topic:
Du hast verschiedene Eigenschaften, die bestimmten Abhängigkeiten unterliegen:
Zum einen wäre da der Firmenname, bei dem ich Dir bereits widersprechen muss. Einfaches Gegenbeispiel: Opel und Vauxhall
Ganz Volkswirt, nehmen wir mal an, dass es einen global gültigen Namen eines Unternehmens gibt, meinetwegen den Namen, den das Unternehmen in seinem Ursprungsland trägt.
Dieser ist eigentlich von gar nichts abhängig und könnte Primärschlüssel werden. Das will man aus Gründen der einfacheren Handhabung nicht, daher ist er abhängig von der ID.

Wir hätten also Deine Tabelle "hersteller".

Als nächstes hätten wir die Sprache. Aus gleichen Gründen ist hierfür eine Tabelle mit ID und Sprache anzulegen, ggf. mit weiteren Attributen, wie dem Identifier "de", "en" etc.

Nun haben wir weitere Attribute, die Beschreibung, die URL, den "Landesfirmennamen" etc.
Diese sind alle von dem Unternehmen und der Sprache abhängig, gehören also in eine Tabelle mit einem zusammengesetzten Primärschlüssel aus dem Unternehmen und der Sprache.

Eine sprachunabhängige "Haupt-URL" kann man natürlich auch in der Hersteller-Tabelle ablegen.

Gruß hpvw

 

[Mik3e]

Hi,

Genialer Spruch.. Kannte ich noch gar nicht
War vorhin mein Fehler. Natürlich gibt es bereits eine Tabelle "languages":

#tbl_language (vereinfacht):
id|key|bezeichnung

Dein Vorschlag ist nicht schlecht.. D.h. es gäbe (bleiben wir im Konjunktiv 1 ) insgesamt 4 Tabellen (inkl. Languages):

tbl_languages

languageID  |  key  |  bezeichnung
-----------------------------------
1  |  DE  |  Deutsch
2  |  EN  |  Englisch

tbl_hersteller (wie gehabt)

herstellerID   |  name
-------------------------------
1  |  Mercedes Benz
2  |  Volkswagen

tbl_hersteller_properties

propertyID  |  propertyName
----------------------------------------
1  |  Name des Herstellers
2  |  Beschreibung des Herstellers
3  |  URL zur Hersteller-Website

tbl_hersteller_description (mit composed Key)

descriptionID  |  fk_languageID  |  fk_herstellerID  | fk_propertyID | value
---------------------------------------------------------------------
1  |  1  |  1  |  1  |  Volkswagen
2  |  2  |  1  |  1  |  Public-Car
3  |  1  |  1  |  2  |  Beschreibung VW deutsch
4  |  2  |  1  |  2  |  Description VW english
4  |  1  |  1  |  3  |  www.volkswagen.de
4  |  2  |  1  |  3  |  www.publiccar.com

Hab ich Dich richtig verstanden? Diese Lösung funktioniert nämlich sicher auch..
Warum komme ich auf solche Dinge nie selbst? Composed Keys sind ja wohl so ziemlich das banalste was es gibt.. Ärgerlich ...

Gib halt bescheid, ob ich Dich richtig verstanden habe,
Danke & Ciao,
Mike

 

[hpvw]

Nehmen wir mal an, ich hätte geschrieben, dass Du dann einen zusammengesetzten Primärschlüssel aus drei Feldern (Hersteller-Id, Sprach-ID und Property-ID) hast, dann wäre die Description-ID überflüssig, aber ansonsten wäre Dein Beispiel genau die Lösung, die ich meinte.

Ich bin mir bezüglich der Description-ID etwas unschlüssig. Sie könnte das Handling vereinfachen, aber auch zu Inkonsistenzen führen. Wenn die drei fremden IDs nicht der Primärschlüssel sind, sollten sie wenigstens gemeinsam einen Unique-Index bilden.
Du bräuchtest die Description-ID nur, wenn ein Property auch ein Array sein könnte und somit die drei Fremdschlüssel keinen Unique-Index bilden können.

Gruß hpvw

 

[Mik3e]

So, schluss mit der Konjuntivität
Den Primary Key descriptionID habe ich eigentlich nur reingenommen, weil dann UPDATES und DELETES vereinfacht werden. Hat also mehr etwas mit bequemlichkeit zu tun

Wenn der Wert ein Array ist (was hier eigentlich nicht der Fall sein sollte) behelfe ich mir normalerweise mit string operatoren anstelle den Array wirklich in drei Zeilen einzutragen. (also value zB.: value1#value2#value3...). Funktioniert natürlich nur bei eindimensionalen Arrays.

Aber wieso denkst Du, dass der PK zu Inkonsistenzen führen könnte? Ich würde eine AUTO_INCREMENT Wert nehmen. Nachdem dieser Key nirgendwo gejoined wird, sollte es auch beim Löschen kein Problem geben (Kollationen)...

 

[hpvw]

So, schluss mit der Konjuntivität

Ok, Klartext

Aber wieso denkst Du, dass der PK zu Inkonsistenzen führen könnte? Ich würde eine AUTO_INCREMENT Wert nehmen. Nachdem dieser Key nirgendwo gejoined wird, sollte es auch beim Löschen kein Problem geben (Kollationen)...

Ohne den Unique-Index besteht die Gefahr, dass Du für eine Eigenschaft zu einem Hersteller in einer bestimmten Sprache mehr als einen Eintrag hast. Ich finde es immer angenehm, wenn die Datenbank sich selber vor Inkonsistenzen schützt und dies nicht in der Anwendung passieren muss.
Wenn ich einem Unique-Index einführe, ist mein erster Gedanke immer, dass etwas am Datenbankdesign nicht stimmt, da ein Unique-Index im Prinzip einem Primärschlüssel entspricht.

Nichts desto trotz gibt es Anwendungen, wo ein Unique-Index sinnvoll ist. Als Beispiel will ich mal eine Usertabelle mit Nicknames nehmen. Für mich kommt das aber nur in Frage, wenn ich dadurch vermeide, einen Char als Primäschlüssel zu nehmen, der möglicherweise nicht so sehr Primärschlüssel ist, wie es die Theorie erwartet. Ein Nickname kann sich nämlich auch ändern, obwohl er für alle verknüpften Datensätze zur eindeutigen Identifizierung genügt.

Wie angesprochen, würde ich in Deinem Beispiel auf einen künstlichen Schlüssel verzichten.

Gruß hpvw

 

[Mik3e]

Hm..
Ich könnte ja die drei Fremdschlüssel als Zusammengesetzten Schlüssel deklarieren:

- fk_manufacturer_id
- fk_language_id
- fk_manufacturer_property_id

Das ist möglich. Habe ich allerdings noch nie verwendet... Damit dürften eigentlich auch nur UNIQUE Werte möglich sein, da Keys ja sowieso Unique sein müssen.

Ich leg mal nen Screenshot von phpMyAdmin bei, wie das aussieht... Vl. hast Du ja Erfahrung damit und kannst mir sagen, ob das so passt.

 

[hpvw]

Ja, das passt so.

Gruß hpvw

 

[Mik3e]

Sehr fein...
Jetzt grübel ich gerade drüber nach, wie ich den SQL Query bilden kann. Dieser Query soll mir eine übersicht aller Hersteller mit den jeweiligen Attributen in der aktuellen Sprache (hier $_SESSION['sess_language_id']) liefern.

$sql = ' SELECT '
			. ' tbl_manufacturer.`pk_manufacturer_id` AS `pk_manufacturer_id` '
			. ' FROM `tbl_manufacturer` AS `tbl_manufacturer` '
			. ' INNER JOIN `tbl_manufacturer_description` AS `tbl_manufacturer_description` ON `tbl_manufacturer`.`pk_manufacturer_id`=`tbl_manufacturer_description`.fk_manufacturer_id '
			. ' AND `tbl_manufacturer_description`.`fk_language_id`='.$_SESSION['sess_language_id'].' '
			. ' WHERE tbl_manufacturer.`manufacturer_deleted` IS NULL '

			. ' LIMIT '.$this->_db->escapeSimple($startCount).', '.$this->_db->escapeSimple($showResultCount).' ';

Klarerweise werden mir hier pro Hersteller 3 Datensätze geliefert (und zwar einer für den Namen, einer für die URL der Website und einer für die Beschreibung).
Hast Du eine Idee, wie ich diese Daten mit einem einzigen Statement bekommen kann? Am besten als Array:

Hersteller => [url=test.com, name=name des herstellers, beschreibung=beschreibung]

Mir fällt im Moment nur eine Lösung mit zwei Statements ein:
Nach diesem Statement, dass mir alle Lieferanten zurückgibt ein zweites, dass dann getrennt davon die Werte der einzelnen Properties ausliest und in einen Array packt. Bei der Lösung gibt es dann im ersten Statement natürlich keinen INNER Join mehr.

Ciao,
Mike

 

[hpvw]

Vor allem, wenn Du die Property-Tabelle eingeführt hast, um auch Attribute hinzuzufügen, also eine dynamische Anzahl Eigenschaften hast, wüsste ich keine Möglichkeit, im Query ein solches Array zu erzeugen. Wenn es bei drei Eigenschaften bleibt, kannst Du natürlich wild Joinen.

Eine Alternative, ist die Gruppierung der Daten für die Ausgabe in PHP mit einer Hilfsvariable. Dazu musst Du im Query nach Hersteller sortieren.
Kurzes, hoffentlich ausreichend verständliches Beispiel:

$result=mysql_query("select ... join ...");
$tempHerstellerId=null;
while($row=mysql_fetch_assoc($result)) {
    if ($tempHerstellerId!=$row['herstellerId']){
        echo "<h1>".$row['herstellerName']."</h1>";
        echo "globale Eigenschaften";
        $tempHerstellerId=$row['herstellerId'];
    }
    echo $row['property'];
}

Anstatt das an der Stelle auszugeben, kannst Du Dir natürlich auch ein entsprechendes Array zusammenbauen.

Gruß hpvw