Beziehungstypen: 1:1, 1:N, M:N
Kurz gesagt: Eine Beziehung zwischen zwei Tabellen ist eins-zu-eins, eins-zu-viele oder viele-zu-viele. Ihr Typ entscheidet, wo der Fremdschlüssel hingehört und wann du eine Verbindungstabelle brauchst.
Problem
Eine Karte und Tags
Der Shop führt zwei neue Dinge ein. Das erste ist eine Kundenkarte: Jeder Kunde kann eine Karte mit Punkten haben. Das zweite sind Produkt-Tags wie „vegan“, „glutenfrei“ und „bio“, damit die Website danach filtern kann.
Wo speicherst du sie? Eine Spalte card_number in customers? Eine Spalte tags in products mit dem Text „vegan, bio“? Die Antwort hängt von einer Frage ab: Wie viele Dinge auf der einen Seite gehören zu wie vielen auf der anderen?
Die Idee
Eins zu viele (1:N)
Ein Kunde hat viele Bestellungen. Jede Bestellung gehört zu einem Kunden. Das ist eine Eins-zu-viele-Beziehung, geschrieben 1:N.
Der Schlüssel gehört auf die „viele“-Seite. Jede Bestellung sagt mit customer_id, wem sie gehört:
customers
- PK
idinteger nametext *emailtext *citytextjoined_attext *
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
Andersherum geht es nicht. Wie viele Spalten order_1, order_2 sollte ein Kunde haben? Maria hat 12 Bestellungen, und morgen hat sie 13.
Das ist die häufigste Beziehung. Im Shop sind auch eine Kategorie und ihre Produkte so verbunden, ebenso ein Produkt und seine Bewertungen.
Probier es aus
Produkte nach Kategorie
Wie viele Produkte hat jede Kategorie, die Produkte hat? Zeige den Namen der Kategorie und die Anzahl.
Lösung
SELECT c.name, COUNT(*) AS products
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name;Eine Kategorie und ihre Produkte sind 1:N, also verbindest du products.category_id mit categories.id und gruppierst nach Kategorie. Du bekommst 6 Kategorien. „Lebensmittel“ und „Haushalt“ fehlen: Das sind Kategorien der obersten Ebene ohne Produkte direkt darin, nur mit Unterkategorien.
Die Idee
Eins zu eins (1:1)
Jeder Kunde hat höchstens eine Karte, und jede Karte gehört zu einem Kunden. Das ist Eins-zu-eins, 1:1. Du speicherst es wie 1:N, mit einem Schlüssel zum Kunden, plus einer weiteren Regel: UNIQUE.
loyalty_cards
- PK
idinteger - FK
customer_idinteger *→ customers numbertext *pointsinteger *issued_attext *
Fünf Kunden haben eine Karte. Versuch, Maria eine zweite zu geben:
UNIQUE auf customer_id lässt keine zweite Zeile für denselben Kunden zu. Ohne es wäre die Beziehung 1:N.
Warum nicht einfach Spalten in customers? Das geht auch. Oft sind 1:1 einfach mehr Spalten in derselben Tabelle. Eine eigene Tabelle lohnt sich, wenn die meisten Zeilen den zweiten Teil nicht haben (15 der 20 Kunden haben keine Karte und hätten leere Zellen) oder wenn er ein Eigenleben hat: Die Karte geht verloren, wird ersetzt, hat eigene Punkte.
Probier es aus
Wer hat noch keine Karte
Das Marketing will jedem Kunden eine Karte anbieten, der noch keine hat. Zeige ihre Namen.
Lösung
SELECT c.name
FROM customers c
LEFT JOIN loyalty_cards l ON l.customer_id = c.id
WHERE l.id IS NULL;LEFT JOIN behält jeden Kunden, und bei denen ohne Karte sind die Spalten von loyalty_cards NULL. WHERE l.id IS NULL behält nur sie: 15 Kunden.
Die Idee
Viele zu viele (M:N)
Eine Bestellung hat viele Produkte. Ein Produkt steckt in vielen Bestellungen. Das ist Viele-zu-viele, M:N.
Hier kann der Schlüssel in keine der beiden Tabellen. Die Lösung ist eine dritte Tabelle, eine Verbindungstabelle, mit einer Zeile für jedes Paar. Im Shop ist das order_items: Jede Zeile ist ein Produkt in einer Bestellung, mit Menge und Preis.
products
- PK
idinteger nametext *- FK
category_idinteger→ categories pricereal *stockinteger *added_attext *
order_items
- PK
order_idinteger→ orders - PK
product_idinteger→ products quantityinteger *unit_pricereal *
In welchen Bestellungen steckt der Kaffee (Produkt 17)?
Mit Tags ist es dasselbe. Ein Produkt hat viele Tags, und ein Tag hängt an vielen Produkten:
tags
- PK
idinteger nametext *
product_tags
- PK
product_idinteger→ products - PK
tag_idinteger→ tags
Sieh es dir an
Das Paar ist der Schlüssel
Der Primärschlüssel von order_items ist das Paar (order_id, product_id). Versuch, das Brot ein zweites Mal zu Bestellung 60 hinzuzufügen, in der es schon ist:
Die Datenbank erlaubt dasselbe Paar nicht zweimal. Ein Produkt steckt einmal in der Bestellung, und mehr Stücke kommen in quantity. Sonst würde die Auswertung „wie oft wurde das Brot bestellt“ dasselbe Produkt aus einer Bestellung doppelt zählen.
Probier es aus
Tags auf der Website
Neben jedem Tag zeigt die Website, wie viele Produkte ihn haben. Zeige den Namen des Tags und die Anzahl.
Lösung
SELECT t.name, COUNT(*) AS products
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
GROUP BY t.id, t.name;Du gruppierst product_tags nach Tag: „vegan“ 10, „glutenfrei“ 11, „bio“ 6. Das sind zusammen 27 Paare, obwohl nur 13 Produkte einen Tag haben, weil die meisten mehr als einen haben.
Übung
Zwei Aufgaben
1. Die Seite der Äpfel zeigt ihre Tags. Welche sind es?
Die Tags des Produkts „Äpfel“:
Lösung
SELECT t.name
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
JOIN products p ON p.id = pt.product_id
WHERE p.name = 'Äpfel';Dieselbe Verbindungstabelle, von der anderen Seite gelesen: vom Produkt zu den Tags. „vegan“, „glutenfrei“ und „bio“.
2. Ein Kunde sucht Produkte, die vegan und glutenfrei sind. Zeige ihre Namen.
Die Produkte mit beiden Tags, „vegan“ und „glutenfrei“:
Lösung
SELECT p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name IN ('vegan', 'glutenfrei')
GROUP BY p.id, p.name
HAVING COUNT(*) = 2;WHERE t.name = 'vegan' AND t.name = 'glutenfrei' findet nichts, weil jede Zeile einen Tag hat. Du nimmst die Zeilen mit einem der beiden Tags, gruppierst nach Produkt und behältst die mit COUNT(*) = 2. Du bekommst 8 Produkte: Das Weißbrot ist nur vegan, und der Weißkäse ist nur glutenfrei.
Test