Datenbanken

[Uri]

Hallo,

ich bau gerade eine Datenbank und weiß nicht genau wie die Relationstabellen aussehen sollen.

Später will ich so joinen, dass ich für jedes Gebäude und Jahr eine Tabelle Jahresabrechnung( Bezeichnung, Kosten) projezieren kann.

Es macht mir jedoch etwas zu schaffen, dass die Kardinalitäten m:n sind.

1. Kann ich einen Nichtschlüsselattribut als Fremdschlüssel nehmen? Also Ausgabentyp-ID oder Gebäude-Id in Ausgaben?

2. Müssen Ausgabentyp-ID und Gebäude-ID in Ausgaben einzigartig sein?

Es ist übrigens die erste Datenbank, die ich entwerfe, die über 2 Tabellen hinausgeht. Hoffe mir kann einer Sagen wie das geht.

Gruß Uri

[Rolf b]

Hallo Uri,

du hast natürlich irgendwie eine m:n Beziehung zwischen Gebäude und Ausgabentyp, aber das ist eigentlich überhaupt kein Problem. Du kannst problemlos einen dreifachen Join über alle Tabellen machen, zwischen Gebäude und Ausgabe per Gebäude-ID, zwischen Ausgabe und Ausgabentyp über die Ausgabentyp-ID.

SELECT G.Gebäude-ID, AT.Bezeichnung, A.Jahr, A.Kosten
FROM Gebäude G 
     LEFT JOIN Ausgaben A ON G.ID=A.Gebäude-ID
     JOIN Ausgabentypen AT ON A.Ausgabentyp-ID=AT.ID

und natürlich kannst Du das gruppieren und summieren wie Du es brauchst. LEFT JOIN für Gebäude->Ausgaben deshalb, damit Du für Gebäude ohne Ausgaben eine "Fehlanzeige" hast.

Aber Ausgabentyp-ID und Gebäude-ID in Ausgaben einzigartig - das wäre nicht Sinn der Sache, nicht wahr? Wenn überhaupt, muss die Kombination (Gebäude-ID, Ausgabentyp-ID, Jahr) einzigartig sein. WENN das so ist, kannst Du das über einen unique index sicherstellen; der hilft Dir ohnehin, wenn Du für ein Gebäude alle Ausgaben sammeln willst.

Die ID in der Ausgaben-Spalte brauchst Du eigentlich nur für CRUD-Operationen, um den Satz schnell wiederzufinden. Du könntest auch drauf verzichten und statt dessen Gebäude-ID, Ausgabentyp-ID und Jahr als Primärschlüssel der Ausgaben verwenden, das hängt davon ab, ob diese Kombination in deinen Daten tatsächlich eindeutig ist. Wenn nicht - nagut, dann doch die eigene ID Spalte und ein non-unique Index.

Rolf

[Henry]

Hallo,

ich habe schon seit Ewigkeiten nichts mehr aufwendiges mit Mysql gemacht und bin daher etwas eingerostet. Habe mir nun aber ein paar alte Scripte von mir angeschaut und gesehen, dass ich in ähnlichen Fällen die Abfragen mit UNION erstellt habe. Kann mich aber leider nicht mehr erinnern, was dafür meine Ambition war. Warum ist Join in so einem Fall zu bevorzugen?

Gruss
Henry

[dedlfix]

Tach!

Warum ist Join [gegenüber Union] in so einem Fall zu bevorzugen?

Unterschiedliche Anforderungen erfordern unterschiedliche Werkzeuge. Ein Union nimmt man, um zwei gleich strukturierte Ergebnismengen zu einer großen Masse zu vereinen. Dabei stehen die beiden Ergebnismengen quasi getrennt untereinander (wenn man kein ORDER BY anwendet). Ein Join hingegen verknüpft die Zeilen der einen Ergebnismenge mit den Zeilen einer zweiten Ergebnismenge.

Hier könnte man die Ausgabentyp-Tabelle mit der Ausgaben-Tabelle joinen, um zur Nummer des Ausgabentyps den Namen herzuholen. Jeder Datensatz der Ausgaben-Tabelle wird mit dem zur ID passenden Datensatz der Ausgabentyp-Tabelle verknüpft. Alternativ könnte man den Namen aber auch über eine Subquery in die Ergebnismenge holen.

dedlfix.

[Rolf b]

Hallo Henry,

die OMA-kompatible Erklärung wäre: Ein JOIN stellt beliebige Relationen nebeneinander, ein UNION gleichartige Relationen untereinander.

Es gibt unterschiedliche Gründe für einen UNION. Zwei Beispiele:

• Mehrere Tabellen mit ähnlicher/gleicher Struktur, und Du brauchst ein übergreifendes Ergebnis (wobei dann natürlich zu diskutieren wäre, warum es überhaupt mehrere Tabellen sind)
• Eine Tabelle, aus der Du in mehreren Abfragen Datensätze herausholst, die Du gemeinsam verarbeiten willst. Die Abfragen sind strukturell unterschiedlich, so dass kein gemeinsamer WHERE möglich ist.

Einen JOIN machst Du, wenn Du Tabellen hast, die unterschiedliche Daten enthalten, aber inhaltlich in Bezug stehen.

• Klartext für einen Schlüsselwert - im Modell des OP: Ausgabenartschlüssel und Text dazu
• Stammdaten und mit mehreren Details zum Stammsatz - siehe das Modell des OP: Gebäude und Ausgaben

Klartextabfragen (oder generell JOINs, bei denen aus der zugeJOINten Tabelle genau ein Wert verwendet wird) kann man auch als Subselects formulieren. Einem guter SQL Optimizer sollte das wurscht sein, Subselects haben den Vorteil, dass man sie getrennt testen kann.

Mein SQL von oben mit Subselect:

SELECT G.Gebäude-ID, 
       (SELECT Bezeichnung FROM Ausgabentypen AT WHERE AT.ID=A.Ausgabentyp),
       A.Jahr, A.Kosten
FROM Gebäude G 
     LEFT JOIN Ausgaben A ON G.ID=A.Gebäude-ID

Edit: Dass Dedlfix das schon vor einer halben Stunde geschrieben hat, sehe ich jetzt erst 😉

Rolf

[pl]

Lege Konten für die Ausgaben an. Konto Müllentsorgung, Konto Entwässerung usw. jedes Konto ist eine Tabelle. Jede Bewegung auf einem Konto ist ein Eintrag in der Tabelle und referenziert das jeweilige Gebäude. Und für jedes Gebäude legst Du auch ein Konto an. Genauso wie das ein Buchhalter aufm Papier machen würde.

[dedlfix]

Tach!

Lege Konten für die Ausgaben an. Konto Müllentsorgung, Konto Entwässerung usw. jedes Konto ist eine Tabelle. Jede Bewegung auf einem Konto ist ein Eintrag in der Tabelle und referenziert das jeweilige Gebäude. Und für jedes Gebäude legst Du auch ein Konto an. Genauso wie das ein Buchhalter aufm Papier machen würde.

Ein Buchhalter hat auf dem Papier keine gescheite Möglichkeit, einen Filter anzulegen. In einem DBMS geht das aber sehr einfach. Ich sehe deshalb keine Notwendigkeit je Ausgabentyp eine eigene Tabelle anzulegen, wenn man stattdessen die Datensätze anhand eines Typ-Feldes auseinanderhalten kann.

dedlfix.

[pl]

Ein Buchhalter hat auf dem Papier keine gescheite Möglichkeit, einen Filter anzulegen. In einem DBMS geht das aber sehr einfach.

Aha. Aber weißt Du, genau deswegen will der OP seine Buchhaltung DB gestützt machen, Guck mal hier.

[dedlfix]

Tach!

Ein Buchhalter hat auf dem Papier keine gescheite Möglichkeit, einen Filter anzulegen. In einem DBMS geht das aber sehr einfach.

Aha. Aber weißt Du, genau deswegen will der OP seine Buchhaltung DB gestützt machen, Guck mal hier.

Ja, deshalb verstehe ich nicht, warum du die Papiervariante implementiert sehen möchtest, also pro Konto eine gleich aussehende Tabelle statt einer gemeinsamen Tabelle, die die Kontonummer lediglich als ein Kriterium enthält. Das wird doch unhandlich, wenn man eine Abrechnung erstellen möchte und tausend Tabellen per Join oder Union zusammenknüpfen muss, zuzüglich dass man für jeden neuen Ausgabentyp eine weitere Tabelle in die Datenbank holt und bei den Abfragen berücksichtigen muss.

dedlfix.

[dedlfix]

Tach!

Später will ich so joinen, dass ich für jedes Gebäude und Jahr eine Tabelle Jahresabrechnung( Bezeichnung, Kosten) projezieren kann.

Brauchst du wirklich die Gebäudedaten in jeder Zeile der Ausgabe, oder reicht es, wenn diese einmal als Gruppenüberschrift in der Ausgabe stehen?

Wenn letzteres, dann kannst du die Gebäude separat abfragen und einzelne Abfragen je Gebäude auf die Ausgabentabelle ausführen. Normalerweise macht man sowas nich, weil es dann viele statt einer Abfrage sind. Aber ist das denn in deinem Fall relevant? Verwaltest du alle Häuser einer Großstadt oder nur eine Handvoll?

Natürlich kannst du auch die Gebäudedaten in die Ausgabe joinen, aber wenn du sie nur für Gruppenbildung brauchst, musst du hinterher mit einem Gruppenwechsel rangehen, um das nächste Gebäude zu erkennen.

Es macht mir jedoch etwas zu schaffen, dass die Kardinalitäten m:n sind.

Eigentlich hast du nur zwei 1:n-Beziehungen: Ausgaben zu Gebäude und Ausgaben zu Ausgabetyp. Zwischen Gebäude und Ausgabetyp gibt es nicht wirklich eine Beziehung. Und selbst wenn du für jedes Gebäude eine Liste der Ausgabetypen erstellen möchtest, kannst du das über die beiden Einzelbeziehungen problemlos abfragen.

1. Kann ich einen Nichtschlüsselattribut als Fremdschlüssel nehmen? Also Ausgabentyp-ID oder Gebäude-Id in Ausgaben?

Ja, klar. Sonst wäre es unmöglich, eine 1:n-Beziehungen zu modellieren, wenn man nur Primärschlüssel miteinander verknüpfen könnte.

2. Müssen Ausgabentyp-ID und Gebäude-ID in Ausgaben einzigartig sein?

Nein. Können sie nicht, weil sie sich pro Jahr wiederholen. Und selbst pro Jahr müssen/können sie nicht eindeutig sein, wenn du mehrere Ausgaben vom selben Typ pro Jahr und Gebäude hast, so wie das ja in der Realität nicht unüblich ist.

Hoffe mir kann einer Sagen wie das geht.

Wenn man Daten miteinander verknüpfen möchte, kommt man meist sehr schnell auf Join als Antwort. Das ist aber nicht die einzige Möglichkeit, Daten aus anderen Tabellen heranzuziehen. Wenn wie bei dir der Ausgabetyp lediglich ein einzelner Name ist, der beim Abfragen der Ausgabentabelle statt der Ausgabentyp-ID angezeigt werden soll, kannst du den auch mit einer correlated subquery in die Ergebnismenge holen. Der Vorteil ist, dass du nicht erst ein Join-Monster bilden musst, für das die Syntax an mehreren Stellen der Query verteilt werden muss, sondern dass du eine Subquery gebündelt dastehen hast. Und diese kannst du bei Bedarf auch recht einfach rauskopieren und separat testen kannst, und das gilt sowohl für die Grund-Query als auch für die Subquery. Oder andersrum, dass du die Abfragen zunächst separat erstellst und prüfst und sie dann miteinander kombinierst. Bei Joins geht so eine getrennte Vorgehensweise nicht. Zudem kann man sich Probleme mit ungewollten kartesischen Produkten einfangen, wenn man nicht richtig joint.

Correlated Subquerys können aber immer nur dann verwendet werden, wenn lediglich ein einzelnes Feld für die Ausgabe relevant ist. Oder du schreibst für jedes Feld eine eigene Subquery, was aber die Query aufbläht.

Erst einzeln zum Testen

SELECT felder FROM Ausgaben

SELECT Bezeichnug FROM Ausgabetypen WHERE ID = 1

Dann miteinander verbinden:

SELECT felder,
(SELECT Bezeichnung FROM Ausgabetypen WHERE ID = a.Ausgabentyp)
FROM Ausgaben a

dedlfix.