Zwei Tabellen mit einander Verknüpfen

[Luckie]

Datenbaksystem: mySQL
Zugriff über: mysql.pas

Gegeben sei eine fiktive Lagerverwaltung eines Supermarktes. In diesem Lager gibt es nun verschiedene Produkte. Diese Produkte werden von verschiednen Lieferanten geliefert. Wobei ein Lieferant auch mehrere Produkte liefern kann. Ich habe mir das so überlegt, dass ich zwei Tabellen anlege, eine für die Lieferanten und eine für die Produkte:

Lieferanten:

Code:

LieferantenID
Firma
Strasse
PLZ
Ort
Telefon
Ansprechpartner
Durchwahl

Produkt:

Code:

ProduktID
Produktbezeichnung
Stückpreis
Menge_auf_Lager

Meine Frage jetzt: Wie bekomme ich diese Tabellen miteinander verknüpft? Was muss ich noch für ein Feld einführen oder sonst an den Tabellen ändern, dass ich jedem Produkt einen Lieferanten zu ordnen kann?

Wie gesagt, alles nur fiktiv. Soll lediglich für mich als Übung und zum Verständnis dienen.

[mikhal]

Über eine dritte Tabelle - einer Treffertabelle. Sie enthält möglicherweise nur die PK's der beiden Tabellen.

Grüße
Mikhal

[ibp]

Zitat:

Zitat von Luckie

Meine Frage jetzt: Wie bekomme ich diese Tabellen miteinander verknüpft? Was muss ich noch für ein Feld einführen oder sonst an den Tabellen ändern, dass ich jedem Produkt einen Lieferanten zu ordnen kann?

der Tabelle Produkt das Feld LieferantenID

[alcaeus]

Hallo Luckie,

nehmen wir an, der Artikel wird von einem bestimmten (und nur von dem) Lieferanten geliefert. Also führst du in der Produkt-Tabelle noch ein Feld ein, welches die ID des Lieferanten speicherst. Dieses Feld bezeichnen wir nun einfach mal als LieferantenID. Willst du nun die Daten des Lieferanten haben, welcher ein Produkt mit einer bestimmten ID geliefert hat, schreibst du folgendes:

SQL-Code:

SELECT L.* FROM Lieferanten L, Produkte P
WHERE L.LieferantenID=P.LieferantenID AND P.ProduktID=12345

Sollte so funktionieren (jedenfalls hat es das bei mir immer ;))

Greetz
alcaeus

[shmia]

Du musst in der Produkt-Tabelle noch einen Fremdschlüssel aufnehmen:
Tabelle Produkt:

Code:

ProduktID
LieferantenID   <= Foreign KEY
Produktbezeichnung 
Stückpreis 
Menge_auf_Lager

Mit einem INNER JOIN greifst du dann auf die Daten zu:
// suche alle Produkte mit Lieferanten, von denen weniger als 10 am Lager sind

SQL-Code:

SELECT Lieferanten.Firma, Lieferanten.Ort, Produkt.Produktbezeichnung FROM
Lieferanten INNER JOIN Produkt ON Lieferanten.LieferantenID=Produkt.LieferantenID
WHERE Produkt.Menge_auf_Lager < 10

Mag sein, dass MySQL die INNER JOIN Syntax nicht kann; dann gibt es eine andere (schlechtere) Schreibweise:

SQL-Code:

SELECT Lieferanten.Firma, Lieferanten.Ort, Produkt.Produktbezeichnung FROM
Lieferanten, Produkt
WHERE 
(Lieferanten.LieferantenID=Produkt.LieferantenID) AND
(Produkt.Menge_auf_Lager < 10)

[Luckie]

Also müsste das ganze dann so aussehen:
Lieferanten:

Code:

LieferantenID
Firma
Strasse
PLZ
Ort
Telefon
Ansprechpartner
Durchwahl

Produkt:

Code:

ProduktID
LieferantID
Produktbezeichnung
Stückpreis
Menge_auf_Lager

Muss dieses zusätzliche Feld irgend eine besondere Eigenschaft aufweisen bis auf die tatsache, dass es vom glöeichen datentyp sein muss? Also muss es indiziert sein oder sonst irgendwas?

[mikhal]

Expliziter:

1:n-Verknüpfung: Ein Artikel wird von einem (und genau nur einem Lieferanten) geliefert: Die LieferantenID als Foreign Key in die Tabelle Artikel integrieren. Dieser Fall wird aber leider selten auftreten.

n:m-Verknüpfung: Ein Artikel kann von mehreren Lieferanten geliefert werden: eine solche Problematik ist nur über eine dritte Tabelle aufzulösen. Kleiner Nebeneffekt: ich kann die gleiche Treffertabelle auch für dfolgende Fälle verwenden: welche Lieferanten liefern mir denn den Artikel überhaupt und welche Artikel liefert mit der Lieferant.

Grüße
Mikhal

[alcaeus]

Zitat:

Zitat von shmia

Mag sein, dass MySQL die INNER JOIN Syntax nicht kann; dann gibt es eine andere (schlechtere) Schreibweise:

SQL-Code:

SELECT Lieferanten.Firma, Lieferanten.Ort, Produkt.Produktbezeichnung FROM
Lieferanten, Produkt
WHERE 
(Lieferanten.LieferantenID=Produkt.LieferantenID) AND
(Produkt.Menge_auf_Lager < 10)

Nur weil es mich interessiert: ist die Abfrage ohne INNER JOIN langsamer oder so, oder warum ist sie schlechter? Ich verwende nämlich fast nie INNER JOINs, sondern immer die "schlechtere" Variante.

Greetz
alcaeus

[Zottel]

jep. wenn du das so machst, kannst du beliebig viele produkte an einen lieferanten hängen.

was mich jetzt mal interressiert:
ist das ein standartmäßiger aufbau von tabellen, oder gibt es da ausser den normalformen noch andere richtlinien?
denn wie Lukie die tabellen jetzt aufgebaut hat, baue ich meine auch auf. Gibt es noch andere alternativen?

btw.: ich arbeite mit MSSQL.

[Luckie]

@mikhal: Ja das ist mir klar, aber ich will erstmal von dem einfachen Fall 1:n ausgehen. Ich fange ja gerade damit an mich mit Datenbakne und SQL zu beschäftigen. Und ich pflege etwas in kleinen, überschaubaren Schritten zu lernen.

[mikhal]

Zitat:

Wobei ein Lieferant auch mehrere Produkte liefern kann

Deine Fragestellung läßt aber nur eine n:m-Verknüpfung zu!

[edit] Ist eine Frage der Sichtweise und wie man ein Projekt erweitern möchte...[/edit]

Grüße
Mikhal

[Zottel]

hmmm....
ein lieferant = 1
mehrere Produkte = n

ergibt 1:n

wo ist da eine n:m verknüpfung?

nur zum verständnis gefragt. vielleicht sehe ich das bisher auch aus der falschen perspektive.
danke ;-)

[Luckie]

Ja:

Lieferant1: Eis und Schokolade
Lieferant2: Brot und Eier
Lieferant3: Gummihandschuhe und Seife

Aber ein vierter Lieferant kann jetzt keine Schokolade und Eier liefern. Ein Produkt kann immer nur von einem Lieferanten bezogen werden, aber ein Lieferant kann mehrere Produkte liefern. So war es gemeint.

Oh Mann, ich habe gerade erst angefangen und bekomme schon ein Knoten in mein Hirn. :roll:

[Zottel]

imho reicht dann dein letzter aufbau der tabellen vollkommen aus.

[Luckie]

Es ist der Begriff "Fremdschlüssel" gefallen. Ist das nur eine Bezeichnung von euch oder muss ich in der Tabellen dem Feld diese Eigenschaft zuweisen?

[mikhal]

Erfahrung: Ich habe noch kein Datenmodell, dass sich mit einer Lager- oder Warenwirtschaft befaßt hat, gesehen, bei dem nicht mindestens ein Produkt von mehreren Lieferanten geliefert wurde. Oft ist es gerade im kaufmännischen Bereich so, daß vor einer Bestellung eine Preisermittlung stattfindet und dann erst bei dem günstigsten Anbieter bestellt wird. Habe ich dann nur eine 1:n-Verknüpfung bleibt mir wohl nichts anderes übrig, als den Artikel mehrfach anzulegen. Plane ich das aber von vorne herein in meinem Datenmodell ein, bereitet mir die "Erweiterung" keine Kopfschmerzen mehr.

Grüße
Mikhal

[Luckie]

Wie gesagt, das ist nur für mich zum Lernen gedacht. Und da gehe ich erstmal vom einfachsten Fall für zwei Tabellen aus.

[mikhal]

Fremdschlüssel = Foreign Key

Auf einem SQL-Server kannst du Foreign Keys als Constraints definieren. Dabei kannst du zusätzlich Regeln festlegen, was geschehen soll, wenn sich a) der Datenstz, auf den der Foreign Key verweist geändert wird oder b) wenn der Datensatz, auf den der Foreign Key verweist gelöscht wird.

Ich kenne jetzt MySQL nicht, deshalb kann ich dir nicht sagen, ob und wie du diese Constraints definieren kannst.

Grüße
Mikhal

[Luckie]

Was sind den jetzt Contrains? :gruebel:

[Tyrael Y.]

@Zottel

Das ist eine n:n -Beziehung da...

Jeder Lieferant ein oder mehrere Produkte haben kann sprich 1:n

und jedes Produkt kann von einem oder mehreren Lieferanten kommen daher auch 1:n

daraus ergibt sich eine n:n -Beziehung zwischen den Tabellen

daher ist eine dritte Tabelle ratsam die folgende Struktur ermöglicht


Lieferant <-> Lieferant/Produkt <-> Produkt
___1:n________n:1___1:n___________n:1______


PS: mehr Infos darüber überall im I-Net unter dem Stichwort
"Normalisierung" erhältlich


mfg

[Tyrael Y.]

Contraints sind Eigenschaften von Tabellenfeldern wie zum Beispiel das

NotNull - Constraint

[shmia]

Zitat:

Zitat von alcaeus

Nur weil es mich interessiert: ist die Abfrage ohne INNER JOIN langsamer oder so, oder warum ist sie schlechter? Ich verwende nämlich fast nie INNER JOINs, sondern immer die "schlechtere" Variante.

Beide Syntax Varianten werden Datenbank intern auf den gleichen Ausführungsplan zurückgeführt.
Vom Ergebnis und der Geschwindigkeit sind sie also gleich.

Der Vorteil der INNER JOIN Syntax liegt darin
* man erkennt sofort, dass ein JOIN zwischen 2 (oder mehr) Tabellen vorliegt
* die WHERE Bedingung bleibt sauber; man kann also leichter im Program zur Laufzeit Where-Bedingungen anfügen
* bei JOINs über 3 (oder noch mehr) Tabellen fällt es wesentlich leichter alle Keyfelder anzugeben
Nachteil der "alten" Syntax
* bei JOINs über 3 (oder noch mehr) Tabellen vergisst man bestimmt irgendeine WHERE Bedingung.
man merkt den Fehler oft erst im Echtbetrieb, wenn plötzlich 8 Datensätze da sind, wo man nur 1 erwartet hat

[Luckie]

Ach so. Muss einem ja auch mal gesagt werden. :zwinker:

Ich glaube, dann hätte ich erstmal alles, was ich brauche. Die eiegntliche Verknüpfung der Datensätze in den Tabellen erfolgt dann über die Abfrage mittels eines INNER JOINs, sehe ich das richtig?

[Zottel]

Zitat:

Zitat von Tyrael Y.

PS: mehr Infos darüber überall im I-Net unter dem Stichwort
"Normalisierung" erhältlich

jep. das weiss ich. aber ich gehe davon aus, das laut aufgabenstellung luckie sich noch nicht mit den normalformen beschäftigen will.
also reicht eine 1:n beziehung für lernzwecke vollständig aus. Wenn er dann hinterher eine normalisierung vornehmen will und doppelnennungen entfernen will, kann er das ja immer noch mit einer n:n machen. aber ich glaube, ihr habt ein wenig weiter gedacht, als die aufgabe von lukie gedacht war. vielleicht bin ich aber auch auf dem holzweg. kann ja auch sein....

aber recht gebe ich euch, das wenn man keine doppelnennungen haben will, man um eine n:n nicht rumkommt. aber das hieße 3 tabellen zum verknüpfen. was für rookies zum lernen schon wieder ein schritt weiter ist.

[Tyrael Y.]

eine n:n - Beziehung in zwei 1:n -Beziehungen aufzulösen und eine Zwischentabelle zu nutzen macht man nicht zum Spaß, sondern um

1. die Datenbank konsistent zu halten

2. weniger Arbeit bei der Implementierung zu haben


mfg

[Luckie]

Ich markiere das hier mal als für mich erledigt.

[Strophi]

Hi,

die 'schlechte' Variante ist ein Cross-Join, also jeder mit jedem, und dann werden die DS aus dem Result-Set gestrichen, die dem Where-Ausdruck nicht entsprechen.
Der Inner-Join macht keinen Cross-Join, ist deswegen performanter.

mfg

Strophi

[Sharky]

Zitat:

Zitat von Luckie

Was sind den jetzt Contrains? :gruebel:

Auch wenn "das Thema für dich erledigt ist" noch etwas zu den Fremdschlüsseln. Diese werden von mySQL seit Version 3.23.44 unterstützt (aber nur bei InnoDB-Tabellen) und müssen dann natürlich auch als solche angegeben werden. Hier mal zwei Tabellen:

SQL-Code:

CREATE TABLE lieferanten
                    (id INT auto_increment,
                    name VarChar(100),
                    PRIMARY KEY (id)
) TYPE=INNODB;

CREATE TABLE artikel
                   (id INT auto_increment, li_id INT,bezeichnung VarChar(100),
                   PRIMARY KEY (id),
                   INDEX li_ind (li_id),          
                   FOREIGN KEY (li_id) REFERENCES lieferanten(id)
                     ON DELETE CASCADE
) TYPE=INNODB;

Wie man sieht ist die Tabelle artikel über einen FOREIGN KEY mit einer Referenz auf die ID aus lieferanten versehen.
Als bedingung ist eingetragen "ON DELETE CASCADE". Was hat dies zu bedeuten fragt sich nun das Luckie *g*
Ganz einfach. Wenn aus der Tabelle lieferanten ein Datensatz gelöscht wird, werden automatisch alle Arikteldatensätze gelöscht bei denen das Feld li_id den gleichen Wert hat wie die ID des gelöschten Lieferanten-DS.

Ausser "Cascade" gibt es noch andere Dinge wie man machen kann:

• RESTRICT -> Ein "Master" Datensatz kann nicht gelöscht werden wenn es noch abhängige DS gibt
• CASCADE -> Siehe oben
• SET NULL -> Das Feld des FOREIGN Keys (li_id) wird auf NULL gesetzt
• NO ACTION -> nix
• SET DEFAULT -> Das Feld wird auf den angegebenen Defaultwert gesetzt.

[Luckie]

Ah ja. das war doch schon mal eine wertvolle Information. :thumb:

[Luckie]

Doch noch mal eine Frage. das mit den Fremdschlüssel spielt aber nur beim Löschen eine Rolle oder? Bei Abfragen spielt es keine Rolle oder?

[Sharky]

Zitat:

Zitat von Luckie

Doch noch mal eine Frage. das mit den Fremdschlüssel spielt aber nur beim Löschen eine Rolle oder? Bei Abfragen spielt es keine Rolle oder?

Hai Luckie,
bei Abfragen spielt das keine Rolle. Es gibt aber neben dem ON DELETE das selbe auch noch als ON UPDATE (kann beides für den selben F-Key verwendet werden).