Struktur der Datenbank und Abfrage per SQL

[Tine]

Morgen!
Habe ein grundsätzliches Problem mit dem anlegen einer Datenbank unter Access 2007 (nicht mit dem Anlegen an sich, nur mit dem Verständnis).

Ich habe ein "Produkt" und dazu eine "Lieferadresse" und eine "Rechnungsadresse". Daraus habe ich eine Tabelle "Produkt" und eine Tabelle "Adressen" gemacht. Nun möchte ich per SQL herausfinden, welche zwei Adressen zu einem Produkt gehören.

Wie baue ich die Beziehungen auf?
  Produkt.Lieferadresse_ID  <->  Adressen.ID
  Produkt.Rechnungsadresse_ID  <->  Adressen_ID

Wie sieht die Abfrage aus?
  SELECT Produkt.Name, Adressen.Strasse(für Lieferung), Adressen.Strasse(für Rechnung)

Hoffe Ihr versteht was ich meine,
Tine

[Matze]

Hallo,

Wie baue ich die Beziehungen auf?
  Produkt.Lieferadresse_ID  <->  Adressen.ID
  Produkt.Rechnungsadresse_ID  <->  Adressen_ID

sieht nach einer n:m Beziehung aus.
"Auf beiden Seiten können beliebig viele Entitäten in Beziehung zueinander stehen."

Dafür solltest du eine weitere Tabelle erstellen in der die Zuordnungen gespeichert werden. Stichwörter "Primärschlüssel" und "Fremdschlüssel".

Wie sieht die Abfrage aus?

Du kennst den Artikel über fortgeschrittene JOIN-Techniken?
Der sollte dich ans Ziel bringen.

Grüße, Matze

[Tine]

Moin Matze !

Dafür solltest du eine weitere Tabelle erstellen in der die Zuordnungen gespeichert werden. Stichwörter "Primärschlüssel" und "Fremdschlüssel".

Goldrichtig! Danke!

Du kennst den Artikel über ...

Auch dafür Danke!

Tine

[Vinzenz Mai]

Hallo,

Wie baue ich die Beziehungen auf?
  Produkt.Lieferadresse_ID  <->  Adressen.ID
  Produkt.Rechnungsadresse_ID  <->  Adressen_ID

Du kennst den Artikel über fortgeschrittene JOIN-Techniken?
Der sollte dich ans Ziel bringen.

Nein, nicht wirklich. Diesen durchaus wichtigen Spezialfall habe ich nicht speziell angesprochen: unterschiedliche Joinspalten, aber zwei Joins zur gleichen Tabelle wie im Falle von Liefer- und Rechnungsanschriften.

Sollte ich vielleicht ergänzen.

Freundliche Grüße

Vinzenz

[Matze]

Hey Vinzenz,

Nein, nicht wirklich. Diesen durchaus wichtigen Spezialfall habe ich nicht speziell angesprochen: unterschiedliche Joinspalten, aber zwei Joins zur gleichen Tabelle wie im Falle von Liefer- und Rechnungsanschriften.

imho ist würde sich hier auch eher eine Beziehungstabelle anbieten anstatt 2 JOINS. Die Abfrage kann man dann wieder mit deinem Artikel erarbeiten :)

Sollte ich vielleicht ergänzen.

imho unnötig, aber das Thema mit den Beziehungstabellen vielleicht anreissen ;)

Grüße, Matze

[Vinzenz Mai]

Hallo,

Nein, nicht wirklich. Diesen durchaus wichtigen Spezialfall habe ich nicht speziell angesprochen: unterschiedliche Joinspalten, aber zwei Joins zur gleichen Tabelle wie im Falle von Liefer- und Rechnungsanschriften.

imho ist würde sich hier auch eher eine Beziehungstabelle anbieten anstatt 2 JOINS. Die Abfrage kann man dann wieder mit deinem Artikel erarbeiten :)

Ja und nein: ja, eine Beziehungstabelle bietet sich an (wenn eine reicht :-).
Nein, um die zwei Joins kommt man nicht herum.

Typischerweise hätte man:

Produkt
Lieferung
Lieferungsposition
Adressen

Ein Produkt kann mehreren Lieferungspositionen zugeordnet sein (muss aber nicht).
Einer Lieferungsposition ist genau ein Produkt zugeordnet.

Eine Lieferung kann mehrere Lieferungspositionen umfassen (mindestens eine).
Eine Lieferungsposition ist genau einer Lieferung zugeordnet.

Eine Lieferung benötigt genau eine Lieferadresse.
Jeder Adresse (als Lieferadresse) können beliebig viele Lieferungen zugeordnet sein.

Eine Lieferung benötigt genau eine Adresse als Rechnungsadresse.
Jeder Adresse (als Rechnungsadresse) können beliebig viele Lieferungen zugeordnet sein.

Du siehst die zweimalige Beziehung der Lieferung zur Tabelle Adressen. Deswegen benötigt man auf jeden Fall zwei Joins.

Freundliche Grüße

Vinzenz

[Ilja]

moin,

Nein, um die zwei Joins kommt man nicht herum.

man kann auch einen join bilden und es dann austypisieren, ob es sich um die lieferadresse oder die rechnungsadresse handelt.

Typischerweise hätte man:

Produkt
Lieferung
Lieferungsposition
Adressen

sieht gefährlich aus, vielleicht meinen wir aber beide das gleiche. für mich wäre das alles eine enität, zum beispiel eine rechnung, in der auch alle benötigten informationen stehen sollten.

Jeder Adresse (als Lieferadresse) können beliebig viele Lieferungen zugeordnet sein.

ich vermute, wir meinen doch anderen dinge. solch eine modellierung halte ich für sehr gefährlich. eine liefer adresse gehört nur zu einem dokument, genauso wie auch die rechnungsadresse und die produktdaten. das ist meiner meinung nach eine entität.

Eine Lieferung benötigt genau eine Adresse als Rechnungsadresse.
Jeder Adresse (als Rechnungsadresse) können beliebig viele Lieferungen zugeordnet sein.

jein, zwigend ist eine rechnungsadresse, aber nicht eine lieferadresse. gibt es keine, ist rechnugs und lieferadresse gleich.

Ilja

[Vinzenz Mai]

Hallo,

Ich habe ein "Produkt" und dazu eine "Lieferadresse" und eine "Rechnungsadresse". Daraus habe ich eine Tabelle "Produkt" und eine Tabelle "Adressen" gemacht. Nun möchte ich per SQL herausfinden, welche zwei Adressen zu einem Produkt gehören.

Wie baue ich die Beziehungen auf?
  Produkt.Lieferadresse_ID  <->  Adressen.ID
  Produkt.Rechnungsadresse_ID  <->  Adressen_ID

Wie sieht die Abfrage aus?
  SELECT Produkt.Name, Adressen.Strasse(für Lieferung), Adressen.Strasse(für Rechnung)

Du greifst zweimal auf die Tabelle Adressen zu. Um die Zugriffe unterscheiden zu können, verwendest Du Aliasnamen:

Im ersten Schritt joinst Du die Lieferadressen zu Deinen Produkten:

  
SELECT  
    Produkt.Name,  
    Lieferung.Strasse AS [Strasse für Lieferung] -- Aliasname für die Spalte,  
FROM  
    Produkt  
INNER JOIN  
    Adressen AS Lieferung     -- Aliasname für die Tabelle Adressen, um den  
                              -- Zugriff zu unterscheiden  
ON  
    Produkt.Lieferadresse_ID = Lieferung.ID  
                              -- Beachte: in der ON-Klausel musst Du den  
                              -- Tabellenaliasnamen benutzen.  

Im zweiten Schritt betrachtest Du das Statement aus der ersten Abfrage als "Tabelle" und joinst die Tabelle Adressen für die Rechnung erneut dazu:

SELECT  
    Produkt.Name,  
    Lieferung.Strasse AS [Strasse für Lieferung], -- Aliasname für die Spalte  
    Rechnung.Strasse AS [Strasse für Rechnung]  
FROM  
    (Produkt  
INNER JOIN  
    Adressen AS Lieferung     -- Aliasname für die Tabelle Adressen, um den  
                              -- Zugriff zu unterscheiden  
ON  
    Produkt.Lieferadresse_ID = Lieferung.ID)  
INNER JOIN  
    Adressen AS Rechnung      -- zweiter Aliasname für die Tabelle Adressen,  
                              -- zur Unterscheidung  
ON  
    Produkt.Rechnungsadresse_ID = Rechnung.ID  

Weitere Hinweise:

Da Du zwei INNER JOINs verwendest, ist die Reihenfolge der JOIN-Operationen im Prinzip gleichgültig. Soweit ich mich erinnere, will Jet-SQL (der SQL-Dialekt der DB-Engine hinter MS Access) explizite Klammerung der Ausdrücke. Bei OUTER JOINs läßt sich die Reihenfolge *nicht* einfach vertauschen, wie Du dem Artikel Fortgeschrittene Jointechniken entnehmen kannst.

Du kannst die Abfrage übrigens auch im Designer zusammenklicken. Dazu musst Du nur zweimal die Tabelle "Adressen" hinzufügen. Den Aliasnamen, den Access dabei vergibt, kannst Du in den Eigenschaften der Tabelle (im Abfragedesigner) ändern. Standardmäßig nimmt Access beim zweiten Hinzufügen einer Tabelle den Aliasnamen <Tabellenname>_1, beim dritten <Tabellenname>_2, ... Diese solltest Du auf alle Fälle durch aussagekräftige Aliasnamen ersetzen.

Freundliche Grüße

Vinzenz

[Tine]

Sieht auf den ersten blick nicht so einfach aus wie das mit der Zwischentabelle. Aber auch Dir herzlichen Dank!

Tine

[Vinzenz Mai]

Hallo,

Sieht auf den ersten blick nicht so einfach aus wie das mit der Zwischentabelle.

von der grundsätzlichen Idee her ist es richtig, dass ein Produkt mehrfach geliefert werden kann - und mehrfach an den gleichen Empfänger geliefert werden kann. Daraus folgte die Zwischentabelle, die hier den aussagekräftigen Namen Lieferung bekommen könnte.

Diese Tabelle könnte folgende Spalten enthalten:

• ID
• Produkt_ID
• Lieferadressen_ID
• Rechnungsadressen_ID
• Lieferdatum
  ...

Dieser Aufbau scheint mir sinnvoller als der von Dir skizzierte. Mit dieser Tabelle Lieferung musst Du dann genau so vorgehen, wie ich es Dir in meinem Beitrag geschildert habe: zwei Joins auf die Tabelle Adressen mit Verwendung von Aliasnamen.

Freundliche Grüße

Vinzenz

[Tine]

Euch beiden vielen Dank!
Also wenn ich Euch richtig verstehe, kann ich nun eine Zwischen/Beziehungstabelle (Matze) einrichten und dann
mit zwei Joins darauf zugreifen (Vinzenz)?

Tine

[Tine]

Also ich hab mir jetzt überlegt, ich mach eine Tabelle "Auftrag", eine "Produkt" und eine "Adressen".

Nun kann ein Auftrag mehrere Produkte und 2 Adressen enthalten. Wie mach ich das mit den "mehreren" Produkten? Muss ich in der Tabelle "Auftrag" unendlich viele Spalten (z.B. Produkt1, Produkt2, ...) vorhalten die dann evtl. ausgefüllt werden?

Tine

[Vinzenz Mai]

Hallo,

Also ich hab mir jetzt überlegt, ich mach eine Tabelle "Auftrag", eine "Produkt" und eine "Adressen".

Nun kann ein Auftrag mehrere Produkte und 2 Adressen enthalten. Wie mach ich das mit den "mehreren" Produkten? Muss ich in der Tabelle "Auftrag" unendlich viele Spalten (z.B. Produkt1, Produkt2, ...) vorhalten die dann evtl. ausgefüllt werden?

Nein, ich hab' da schon weitergedacht :-)

Wobei meine Erfahrung in mit Aufträgen die ist, dass

einem Auftrag mehrere Lieferungen zugeordnet sein können (mindestens eine) und
jede Lieferung genau einem Auftrag zugeordnet ist.

Das hat nichts damit zu tun, dass das Lieferunternehmen mir in einer *Anlieferung* mehrere Lieferungen, die zu verschiedenen Aufträgen gehören, ins Haus bringen kann.

Ob die Rechnungsstellung je Lieferung erfolgt oder je Auftrag, das musst Du wissen (oder in Erfahrung bringen).

Freundliche Grüße

Vinzenz

[EKKi]

Mahlzeit Tine,

Nun kann ein Auftrag mehrere Produkte und 2 Adressen enthalten. Wie mach ich das mit den "mehreren" Produkten? Muss ich in der Tabelle "Auftrag" unendlich viele Spalten (z.B. Produkt1, Produkt2, ...) vorhalten die dann evtl. ausgefüllt werden?

Nein (Bloß nicht!), Du brauchst - wie Vinzenz bereits schrieb - stattdessen eine Tabelle, die die Zuordnung aller Aufträge zu ihren Produkten (bzw. umgekehrt) enthält ... die z.B. "Lieferungsposition" heißen könnte und auf jeden Fall mindestens zwei Spalten enthalten sollte: einmal so etwas wie eine LieferungID und einmal so etwas wie eine ProduktID. Damit kannst Du dann jeder Lieferung beliebig viele Produkte zuordnen.

MfG,
EKKi

[Matze]

Hey EKKi,

und auf jeden Fall mindestens zwei Spalten enthalten sollte: einmal so etwas wie eine LieferungID und einmal so etwas wie eine ProduktID.

oder besser mindestens 3 um auch Rechnungsadressen von Lieferadressen zu trennen.
Es ist hier zwar möglich eine weitere Beziehungstabelle zu nutzen, "muss man aber nicht"[tm].

Grüße, Matze

[EKKi]

Mahlzeit Matze,

und auf jeden Fall mindestens zwei Spalten enthalten sollte: einmal so etwas wie eine LieferungID und einmal so etwas wie eine ProduktID.

oder besser mindestens 3 um auch Rechnungsadressen von Lieferadressen zu trennen.

Nein. Die Rechnungs- und Lieferadresse steht in Beziehung zur Lieferung - *nicht jedoch* zur Lieferungsposition!

MfG,
EKKi

[Matze]

Hey EKKi,

Nein. Die Rechnungs- und Lieferadresse steht in Beziehung zur Lieferung - *nicht jedoch* zur Lieferungsposition!

ja, aber dann müsste man wieder 2 Beziehungstabellen anlegen, oder?
Indem ich der 1 Tabelle 3 Spalten gebe, kann ich beide Beziehungen darin abbilden. Ob das sinnvoll ist, ist eine andere Frage, bei wenig Daten würde ich es aber machen.

Grüße, Matze

[Vinzenz Mai]

Hallo,

Nein. Die Rechnungs- und Lieferadresse steht in Beziehung zur Lieferung - *nicht jedoch* zur Lieferungsposition!

ja, aber dann müsste man wieder 2 Beziehungstabellen anlegen, oder?
Indem ich der 1 Tabelle 3 Spalten gebe, kann ich beide Beziehungen darin abbilden. Ob das sinnvoll ist, ist eine andere Frage, bei wenig Daten würde ich es aber machen.

selbstverständlich ist es sinnvoll. Es sind nur Spalten der Lieferungstabelle. Nicht Spalten der Lieferpositionstabelle. Schreib' ich doch schon die ganze Zeit.

Freundliche Grüße

Vinzenz

[Tine]

Ok. Ich habe das ganze einmal mit mehreren Spalten in der Auftragstabelle gemacht. Ergebnis nach der SQL Abfrage:
Auftragsnummer Produkt1 Produkt2
1              abc      def
2              ghi      jkl

Dann hab ich das mit einer Zwischentabelle gemacht. Ergebnis nach der SQL Abfrage:
Auftragsnummer Produkt
1              abc
1              def
2              ghi
2              jkl

Das erste sieht für mich "besser" aus...

[Matze]

Ok. Ich habe das ganze einmal mit mehreren Spalten in der Auftragstabelle gemacht.

Und woher willst du wissen wieviel Produkte dort gebraucht werden?
Du missbrauchst Spalten als Zeilen, damit wirst du nur Probleme bekommen.
Der Hinweis von Ekki ("Blos nicht!") war nicht umsonst.

Grüße, Matze

[Vinzenz Mai]

Hallo,

Ok. Ich habe das ganze einmal mit mehreren Spalten in der Auftragstabelle gemacht. Ergebnis nach der SQL Abfrage:

Auftragsnummer Produkt1 Produkt2
1              abc      def
2              ghi      jkl

das skaliert nicht :-)
Willst Du die Anzahl der unterschiedlichen Produkte je Auftrag künstlich begrenzen? Es kommen noch Anzahl der einzelnen Produkte, Preis hinzu. Nein, sowas ist *keine* gute Idee.

Dann hab ich das mit einer Zwischentabelle gemacht. Ergebnis nach der SQL Abfrage:
Auftragsnummer Produkt
1              abc
1              def
2              ghi
2              jkl

Das zweite ist definitiv besser.

Freundliche Grüße

Vinzenz

[Tine]

Das zweite ist definitiv besser.

Danke! Dann werd ich das wohl mit Beziehungstabellen angehen. Vielen Dank nochaml und ein schönes Wochenende!

Tine

[Matze]

Danke! Dann werd ich das wohl mit Beziehungstabellen angehen.

puh, das ist ja gerade nochmal gut gegangen ;)

Vielen Dank nochaml und ein schönes Wochenende!

Ebenso, Matze

[Tine]

Sorry aber eine kleine Frage hab ich dazu noch:

Mal angenommen ich möchte ein Produkt einem Bundesland zuordnen (warum auch immer). Das Produkt kann aber in mehreren Bundesländern liegen. Muss ich da dann eine Beziehungstabelle anlegen?

Tine

[EKKi]

Mahlzeit Tine,

Mal angenommen ich möchte ein Produkt einem Bundesland zuordnen (warum auch immer). Das Produkt kann aber in mehreren Bundesländern liegen. Muss ich da dann eine Beziehungstabelle anlegen?

Musst Du nicht. Sinnvoll wäre es aber schon ... :-)

Kleiner Tipp: Informiere Dich ausführlich zum Thema Normalisierung.

MfG,
EKKi

[EKKi]

Mahlzeit Tine,

Ok. Ich habe das ganze einmal mit mehreren Spalten in der Auftragstabelle gemacht. Ergebnis nach der SQL Abfrage:
Auftragsnummer Produkt1 Produkt2
1              abc      def
2              ghi      jkl

Und sobald jemand mehr als zwei Produkte bestellt, darfst Du sowohl Tabellen als auch Skripte/Programme, die darauf zugreifen ändern. Und wenn dann irgendwann später jemand mehr als die bei der ersten Erweiterung festgelegte Spaltenanzahl Produkte bestellt, wieder. Jedesmal! Willst Du das wirklich?

Dann hab ich das mit einer Zwischentabelle gemacht. Ergebnis nach der SQL Abfrage:
Auftragsnummer Produkt
1              abc
1              def
2              ghi
2              jkl

Das erste sieht für mich "besser" aus...

Das "Aussehen" ist dabei so etwas von irrelevant. Jeder vernünftige SQL-Dialekt und jede vernünftige Programmiersprache besitzt Möglichkeiten, diese durchaus korrekte und vernünftig strukturierte Datenmenge so umzuformen, dass Sie den Anforderungen an eine "besser" aussehende Rechnung genügt.

Im Fall von MySQL fallen mir dabei z.B. ganz spontan die Begriffe "GROUP BY" und "GROUP_CONCAT()" ein ... informiere Dich! :-)

MfG,
EKKi

[Tine]

Danke Dir!

[Matze]

Hallo Vinzenz,

Lieferung.Strasse AS [Strasse für Lieferung] -- Aliasname für die Spalte,

  
bei MySQL ist es erlaubt das `AS`{:.language-sql} wegzulassen.  
Ist das in anderen DBMS auch so? Interessiert mich gerade.  
  
  
Grüße, Matze

[Vinzenz Mai]

Hallo,

Lieferung.Strasse AS [Strasse für Lieferung] -- Aliasname für die Spalte,

  

> bei MySQL ist es erlaubt das `AS`{:.language-sql} wegzulassen.  
> Ist das in anderen DBMS auch so? Interessiert mich gerade.  
  
Ja, das ist üblich. Mir ist sogar einmal ein SQL-Dialekt unter die Finger gekommen, der AS nicht mag. Ich kann mich nur gerade nicht erinnern, welcher das war und welche Aliasnamen es betraf.  
  
Wie es in Jet-SQL aussieht, weiß ich nicht mehr. Access vermeide ich seit Jahren, so gut ich kann. Wenn ich unter Windows arbeite, dann typischerweise mit einer Edition des MS SQL-Servers.  
  
  
Freundliche Grüße  
  
Vinzenz

[Ilja]

moin,

Mir ist sogar einmal ein SQL-Dialekt unter die Finger gekommen, der AS nicht mag. Ich kann mich nur gerade nicht erinnern, welcher das war und welche Aliasnamen es betraf.

Oracle mag kein AS bei der verwendung von tabellen-alias namen

Ilja