Entscheidungsschwäche

[Frau Zurbel]

Guten Tag,

es geht um Herrn Zwirbel. Herrn Zwirbel gehört Zwirbels Zwergenzimborium. Herr Zwirbel verkauft Zwerge, Gartenzwerge und andere Zwerge, mit und ohne Zipfel, Zwinge oder Zwickel.

Herr Zwirbel möchte seine Zwergenprodukte in einer Datenbank erfassen. Und jetzt hat er ein Entscheidungsproblem:

Herrn Zwirbels Zwerge bestehen immer aus einer Grundform, die in verschiedenen Ausschmückungen verkauft werden; den Lachzwerg gibt es mit roter oder grüner Zipfelmütze, den Bierzwerg mit großem und kleinem Krug. Ähnliches bei den 789 anderen Zwergenvarianten.

Herr Zwirbel ist nicht aus reinem Glück Multimillionär geworden, nein, sondern weil er in seinen 1580 Ladengeschäften stets die Preise in Schwung hält. Morgens kostet der Zwerg zum Beispiel 12 €, mittags 12,50 € und abends 11 €. Auf so eine gewiefte Preisgestaltung, für dasselbe Produkt zehnmal am Tag den Preis zu ändern, kommt sonst kein Mensch!

Um ganz nah am Kunden zu sein, werden die Geschäfte zudem nur mit den Grundformen beliefert. Das Bemalen der Zwerge oder ihnen einen Krug auf die Umme setzen müssen die Verkäufer selbst, ganz nach regionaler Kundenvorliebe. Herr Zwirbel macht da auch keine Vorgaben, da kann auch mal ein einzelner Wippzwerg mit karierter Hose verkauft werden.

Herr Zwirbel hat nun zwei Tabellen in seiner Datenbank:

Tabelle Zwerge enthält den Produktnamen, Tabelle Preise die einzelnen Preise und dazugehörigen Geschäfte. Wohin nun klugerweise mit der Produktvariante?

Auf den ersten Blick sicher in die Tabelle Zwerge:

Nummer:    1          2        3
Name:      Bierzwerg
Variante:             1 Liter  2 Liter
Grundform:            1        1

Grundform zeigt auf die Nummer des "Basiszwerges", das Feld Name bleibt bei den abgeleiteten Zwergen leer und wird aus der Grundform aufgefüllt.

Per select left join mit sich selbst (also der Tabelle, nicht mit Herrn Zwirbel) bekommt Herr Zwirbel sämtliche Zwerge:

select 
  ifnull(a.name, b.name) as name, 
  ifnull(a.variante, b.variante) as variante 
from zwerge as a
left join zwerge as b on a.nummer = b.grundform

Sollen alle Angebote einer Filiale angezeigt werden, wird's allerdings aufwändig:

select 
  preise.preis, 
  zwerge.name, 
  zwerge.variante 
from preise 
left join (select 
             ifnull(a.name, b.name) as name,
             ifnull(a.variante, b.variante) as variante 
           from zwerge as a 
           left join zwerge as b on a.nummer = b.grundform
          ) as zwerge
where filiale = "abc"

Die Bauchschmerzen fangen schon damit an, dass sqlite3 und MariaDB bei dieser Abfrage erstmal die komplette Zwergentabelle mit ihren 789 Varianten durcharbeiten. Um die Verarbeitung auf die tatsächlich in einer Filiale angebotenen zehn Varianten zu beschränken, muss der innere select-Teil mit dem where des äußeren bestückt werden.

Deutlich einfacher wäre die Abfrage, wären die Varianten in der Preistabelle untergebracht:

select 
  zwerge.name, 
  preise.preis,
  preise.variante 
from preise 
left join zwerge on preis.zwerg = zwerg.nummer
where filiale = "abc";

Die Produktvariante beim Preis unterzubringen statt beim Produkt bereitet Herrn Zwirbel aber Bauchschmerzen, schlimme Bauchschmerzen. Das findet er nicht schön und er grämt sich.

Würde es zeitlich einen nennenswerten Unterschied machen, wäre die Entscheidung klar. Macht es aber nicht, die Abfragen laufen ungefähr gleich schnell ab. Es läuft auf eine ästhetische Entscheidung hinaus, ja, vielleicht sogar eine moralische. Schöne Konstruktionen sind nicht immer einfache Konstruktionen. Auch eine Sanduhr erfüllt ihren Zweck, aber ist das komplexe Uhrwerk einer Taschenuhr nicht um ein Vielfaches erfüllender, nämlich die Seele des technikgeilen Betrachters?

Wer kann Herrn Zwirbel aus seinem Dilemma helfen?

Er hat schon drei Kilo abgenommen.

Wir machen uns Sorgen.

Vielen Dank,
Ziglinde Zwurbel
Zekretariat

[dedlfix]

Tach!

Wer kann Herrn Zwirbel aus seinem Dilemma helfen?

Vielleicht jemand, der nicht nur Joins kennt, sondern auch Correlated Subquerys. Damit bekommt man einzelne Werte aus Tabellen ins Ergebnis. Wenn man mehr als einen Wert braucht, sind Correlated Subquerys aber nicht mehr vorteilhafter als ein Join.

Sollen alle Angebote einer Filiale angezeigt werden, [...]

... dann empiehlt es sich, die Angebote, also die Zwerge, als Hauptquery zu nehmen und die zusätzlichen Daten zu joinen, oder im Falle nur eines Feldes eben mit Corellated Subquery zu arbeiten.

dedlfix.

[Frau Zurbel]

Sehr geehrte Frau Dedl-Fix!

Wer kann Herrn Zwirbel aus seinem Dilemma helfen?

Vielleicht jemand, der nicht nur Joins kennt, sondern auch Correlated Subquerys.

Ihren Vorschlag habe ich Herrn Zwirbel ausgedruckt und vorgelegt. Kaum hatte ich sein Büro verlassen, hörte ich einen lauten Rumms. Herr Zwirbel lag auf dem Boden, hatte glasige Augen und stammelte nur "NxM, NxM". Die lustige Ente auf seinem Bildschirm hatte ihn darauf hingewiesen, dass der Aufwand gegenüber einer einfachen Unterabfrage wohl massiv ansteigen täte - und das war ja nun nicht Sinn der Sache, es grämte ihn doch gerade das unnötige komplette Durcharbeiten der Zwergentabelle, obwohl nur wenige Zwerge in einer Filiale angeboten wurden.

Möglicherweise hat Herr Zwirbel sie aber auch falsch verstanden. Ein Beispiel wäre hilfreich gewesen.

So oder so, ich habe ihn vor die Wahl gestellt, "Herr Zwirbel", habe ich gesagt, "Herr Zwirbel, 60 Jahre bin ich ihre Sekretärin, so geht das nicht! Klöße oder Katheder, Kantine oder Krankenhaus!". Wir haben ihn dann in die Kantine zu Herrn Knubbel zum Kloßessen gekarrt, damit er endlich wieder auf die Beine kommt.

Was soll ich sagen, er kam beseelt lächelnd zurück.

Sollen alle Angebote einer Filiale angezeigt werden, [...]

... dann empiehlt es sich, die Angebote, also die Zwerge, als Hauptquery zu nehmen und die zusätzlichen Daten zu joinen

Nun, die Filialangebote sind doch eigentlich die Preise, nicht die Zwerge?

Wie dem auch sei, nach Ihrem Einwurf kam Herrn Zwirbel der Gedanke, dass er den Zwerg wohl von hinten aufgezäumt hatte. Er hat also nochmal ganz neu angefangen, mit den Preisen zuerst und dann die Zwerge:

select 
  preise.preis, 
  ifnull(a.name, b.name) as name,
  ifnull(a.variante, b.variante) as variante 
from preise 
left join zwerge as a on a.nummer = preise.zwerg
left join zwerge as b on a.nummer = b.grundform
where preise.filiale = "abc"

Nach genauerer Betrachtung stellte er fest, dass das dann doch die gleiche Abfrage ist wie vorher, nur ohne die aufwändige Unterabfrage. Aber sie tut, was sie soll, und die Datenbank meldet diesmal für jedes Teilstück, dass ein Index benutzt wird (insbesondere für Tabelle a, wo vorher ein "scan table" dreute). Das ist doch schön.

Möglicherweise war das nicht, was Sie im Sinn hatten, aber Herr Zwirbel freut sich jedenfalls erstmal und bedankt sich für den Denkanstoß.

Mit freundlichen Grüßen,
Ziglinde Zurbel

[dedlfix]

Tach!

Vielleicht jemand, der nicht nur Joins kennt, sondern auch Correlated Subquerys.

Möglicherweise hat Herr Zwirbel sie aber auch falsch verstanden. Ein Beispiel wäre hilfreich gewesen.

Eine Suche wäre hilfreich gewesen. Beispielsweise Correlated Subquery in der englischen Wikipedia. Auf deinen Fall passt das zweite Beispiel mit der Subquery im SELECT-Teil. Die haben dort mit AVG() eine Aggregatfunktion verwendet, aber man kann auch direkte Werte aus einzelnen Datensätzen abfragen, bei dir also den Preis.

... dann empiehlt es sich, die Angebote, also die Zwerge, als Hauptquery zu nehmen und die zusätzlichen Daten zu joinen

Nun, die Filialangebote sind doch eigentlich die Preise, nicht die Zwerge?

Kauft man Preise oder kauft man Produkte?

Wie macht man es denn ohne Datenbank, beispielsweise bei einer herkömmlichen Preisliste? Man listet erstmal alle Produkte auf, zunächst ohne Preise. In diesem Zustand kann man das Papier auch als Vorlage beiseite legen, wenn es mal neue Preise zu den Produkten gibt. Man kann es auch in der Form an die Filialen schicken, die ihre Preise eintragen können. Jedenfalls macht man nun seine Kalkulation und bestimmt die Verkaufspreise, die man zu den Produkten in die Zeile schreibt.

Genauso kann man das auch mit Datenbank machen. Erstmal eien Liste der Produkte erstellen, dann in einem zweiten Schritt die Subquery erstellen, die den Preis pro Produkt holt/ermittelt. Das kann man zunächst unabhängig von der Hauptquery machen, indem man als Bedingung für den Fremdschlüssel erstmal einen festen Wert nimmt. Anschließend fügt man die Subquery in die Select-Klausel ein und nimmt da dann den Wert aus der Hauptquery.

Man kann natürlich auch Joinen, aber ich finde es mit Subquery verständlicher, weil es den direkten Wunsch angibt. Ein Join ist eher ein indirektes Mittel zur Zielereichung.

dedlfix.

[Henry]

Hallo Frau,

so eine kreatives, feingeistiges und rundum gelungenes Essay. Und dies auch noch bei so einer trockenen Thematik, Respekt. Sehr schön.

Gruss
Henry

[TS]

Hello,

so eine kreatives, feingeistiges und rundum gelungenes Essay. Und dies auch noch bei so einer trockenen Thematik, Respekt. Sehr schön.

Und wenn ein Join nicht hilft, dann vielleicht ein Joint?

scnr

Liebe Grüße
Tom S.

[Rolf B]

Hallo Frau Zwurbel,

wirklich ein schönes Essay :)

Ich vermute, dass die Filiale in der Preise-Tabelle steht. Und dass die Korrelation Zwerg/Zwergvariante zu Preis über die Nummer gemacht wird.

Dieser Self-Merge für Zwerge und Varianten ist von der bösen Stiefmutter. Weil - da werden zwei Relationen in eine Tabelle vermengt, und der Self-Merge ist in dieser Form Unsinn. Die IFNULLs treiben dazu noch den Optimizer zum Apfelvergiften.

Korrekt ist eine Tabelle für Grundformen, eine weitere für Varianten, und die kann man mit LEFT JOIN verbinden ohne ein kartesisches Monsterprodukt zu erschaffen. IFNULL ist dann für die Namen auch nicht nötig (na gut, im Self-Join sind die IFNULL genauso überflüssig).

SELECT IFNULL(v.nummer, g.nummer) as nummer
     , g.name
     , v.variante
FROM zwerge g 
     LEFT JOIN zwergoide v ON v.grundform = g.nummer

Für die Angebote einer Filiale stellt sich nun die Frage, wie die Beziehung zwischen Preisen und Varianten/Grundformen ist. Das ist in der Anfrage nicht klar, der ON fehlt. Ich gehe aber davon aus, dass es zu einem Preis nur einen Zwerg gibt (eine Grundform oder eine Variante). Es ist nun ineffizient, den obigen Join zu verwenden, weil er dann tatsächlich erstmal den fetten Join machen muss. Statt dessen wäre ein UNION nützlich, um entweder nach Zwergen oder nach Zwergvarianten zu suchen.

SELECT z.name, z.variante, p.filiale, p.preis
FROM preise p
JOIN (Select g.nummer, g.name, null as variante FROM zwerge g
      WHERE g.nummer = p.zwergNummer
   UNION
      Select v.nummer, g.name, v.variante FROM zwerge g, zwergoide v ON v.grundform = g.nummer
      WHERE v.nummer = p.zwergNummer) z
WHERE p.filiale = 'Siebenbergen'

Damit kann er pro Preis einen Index-Seek in die Zwerge- und Zwergoide-Tabellen machen.

Schönen Gruß an Schneewittchen!

Rolf

[Frau Zurbel]

Sehr geehrter Herr Rolf!

Ich vermute, dass die Filiale in der Preise-Tabelle steht. Und dass die Korrelation Zwerg/Zwergvariante zu Preis über die Nummer gemacht wird.

Dies ist korrekt. Hatte ich es nicht erwähnt? Ich stand vermutlich neben mir, die vor Sorge eingefallenen Wangen meines lieben Herrn Zwirbel machten mich angst und bange.

Dieser Self-Merge für Zwerge und Varianten ist von der bösen Stiefmutter. Weil - da werden zwei Relationen in eine Tabelle vermengt, und der Self-Merge ist in dieser Form Unsinn. Die IFNULLs treiben dazu noch den Optimizer zum Apfelvergiften.

Korrekt ist eine Tabelle für Grundformen, eine weitere für Varianten

Herr Zwirbel möchte da widersprechen, denn dann hätte er ja letztlich für ein Produkt zwei Tabellen. Auch sein Freund, Bäckermeister Bock, hat nur eine Warentabelle, nicht je eine für Torten und eine für Törtchen, eine für Brot und eine für Brötchen.

Möglicherweise haben wir uns auch hier unklar ausgedrückt: Die Grundformen werden ja als solche ebenfalls verkauft. Man müsste in der Folge in der Preistabelle zwei Produktspalten haben, eine für Grundzwerge, eine für verfeinerte Zwerge, je nachdem, zu welchem Produkt der Preis gehört.

Wir danken Ihnen aber für Ihre Mithilfe und ich möchte um Nachsicht bitten, sollte ich das Problem ungenau beschrieben haben. Ich hätte Fräulein Zwo aus der Abteilung EDV (Elektronische Datenverarbeitung) beauftragen sollen, die schreibt immer sehr knackig, etwa so: "Tabelle funzt nicht!!".

Mit freundlichen Grüßen, Ziglinde Zurbel

[Rolf B]

Liebe Frau Zurbel,

Herr Zwirbel möchte da widersprechen, denn dann hätte er ja letztlich für ein Produkt zwei Tabellen. Auch sein Freund, Bäckermeister Bock, hat nur eine Warentabelle, nicht je eine für Torten und eine für Törtchen, eine für Brot und eine für Brötchen.

Nun ja, der gute Herr Zwirbel modelliert ja hauptberuflich Gartenzwerge. Würde er öfter Datenbanken modellieren, sähe er das bestimmt anders. Das knackige Fräulein Zwo aus der EDV weiß das sicherlich auch. Der Vergleich mit dem Bock, den er zum Gärtner Bäcker gemacht hat, ist da nicht sehr hilfreich, weil Torte und Törtchen bzw. Brot und Brötchen nicht Grundform und Variante sind.

Wenn man ein Datenmodell erstellt, tut man gut daran, scheinbar ähnliche Entitäten nicht zu vermischen. Zwerg und Zwergvariante sind ähnlich, aber nicht gleich; sie unterscheiden sich klar durch die Relation Variante->Zwerg. Darüber hinaus ist ein Zwerg eigenständig verkaufbar, eine Variante aber nur zusammen mit einer Grundform sinnvoll. Es sind verschiedene Entitäten und darum gehören sie konzeptionell in verschiedene Relationen (technisch: Tabellen). Unter gewissen Umständen ist es sinnvoll, ähnliche Relationen gemeinsam in einer Tabelle zu speichern. Das ist eine technische Optimierung, die man betreiben kann, wenn man damit Probleme löst. Im vorliegenden Fall löst man aber keine Probleme, sondern schafft welche, nämlich eine rekursive Beziehung, zu deren Auflösung der Zwerver mehr Daten verarbeiten muss als nötig, weshalb es ihn auch aus den Performance-Socken haut..

Übrigens hat mein Vorschlag den Abverkauf der Grundversionen durchaus berücksichtigt. Sowohl beim Hinweg (alle Zwerge mit ihren Varianten, 1. Query in meinem Posting), als auch beim Rückweg (ordne einem Preis das Produkt oder die Produktvariante zu, 2. Query in meinem Posting).

Ein Lücke hatte mein Vorschlag tatsächlich: Ich ging davon aus, dass ein Zwerg entweder variantenfrei ist, oder dass er Varianten hat und dann immer als Variante verkauft wird. Einen Bierzwerg ohne Einliter- oder Zweiliter-Krug zu verkaufen, das kam mir nicht in den Sinn. Ich habe ja auch nicht den Geschäftssinn des Herrn Zurbel und kein Zwergen-Zimborium. Das kann man aber lösen, wenn man dem Bierzwerg eine dritte Variante verpasst: „ohne Krug“. Damit erledigt man auch das Problem, dass es Zwergengrundformen geben könnte, deren Abverkauf nur zusammen mit einer Variante möglich ist. Ich denke da an den Trumpzwerg, den es in den Varianten „Dickkopf“, „Dummkopf“, „Laberkopf“ und „Querkopf“ gibt. Aber einen kopflosen Trumpzwerg - den hat man noch nicht gesehen!

Wenn Sie das knackige Fräulein Zwo aus Ihrer EDV da vielleicht einmal zu Rate ziehen möchten? Bestimmt versteht die diese Queries und sagt dann: „Oh, so funzt das ja. Und sogar schnell!“. Aber, hm, wenn Fräulein Zwo dieses Modell ursprünglich erstellt hat, sollte man sie nicht zu Rate, sondern an den Ohren ziehen und ihr sagen: „Nun gucken Sie mal, SO geht das!“

Rolf