Die Bäckerei, Etappe 1: Datenbank und Tabellen
Kurz gesagt: Etappe 1 des Projekts führt von der Beschreibung einer kleinen Bäckerei zum Tabellenplan und zu fünf CREATE-TABLE-Anweisungen mit den Regeln, die die Daten korrekt halten.
Problem
Die Bäckerei an der Ecke
Eine Bäckerei im Viertel möchte Bestellungen online annehmen. Sie verkauft Brot, Teilchen und Getränke. Kunden legen ein Profil mit Name, E-Mail und Stadt an. Eine Bestellung hat ein Datum, einen Status (neu, bezahlt, geliefert, storniert) und ein paar Zeilen: welches Produkt, wie viele Stück und zu welchem Preis. Die Bäckerei will außerdem wissen, wie viel von jedem Produkt auf Lager ist.
In diesem Projekt baust du die ganze Datenbank selbst: von der leeren Datenbank über die Tabellen, die Daten, die Abfragen und die Änderungen bis zur Sicherung und zum DROP am Ende. Jede Etappe beginnt mit dem, was du in den vorigen gebaut hast.
Die Idee
Der Plan
Vor dem ersten CREATE TABLE entscheidest du, welche Dinge es gibt und wie sie zusammenhängen. Hier sind es fünf:
| Tabelle | Was sie enthält | Zeigt auf |
|---|---|---|
categories |
die Kategorien: Brot, Teilchen, Getränke | |
products |
die Produkte mit Preis und Lagerbestand | categories |
customers |
die Kunden mit E-Mail und Stadt | |
orders |
die Bestellungen mit Datum und Status | customers |
order_items |
die Zeilen der Bestellungen | orders und products |
Jede Tabelle hat id INTEGER PRIMARY KEY, außer order_items: Dort ist der Schlüssel das Paar aus Bestellung und Produkt, denn ein Produkt ist eine Zeile in der Bestellung. Die Tabellen, auf die gezeigt wird, werden zuerst angelegt.
Die Idee
Die leere Datenbank
Auf deinem Computer legst du die Datenbank mit sqlite3 bakery.db an (in PostgreSQL mit CREATE DATABASE bakery; und \c bakery). Die Felder in diesem Projekt beginnen mit einer leeren Datenbank, ohne den Shop aus den anderen Lektionen:
Probier es aus
Die Kategorien
Lege die Tabelle categories an: id ist der Primärschlüssel, und name ist Pflicht und darf sich nicht wiederholen (es darf nicht zwei Kategorien „Brot“ geben).
Lösung
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);name TEXT NOT NULL UNIQUE. Die Prüfung versucht, zwei Kategorien „Brot“ und eine ohne Namen zu schreiben, und zählt, wie viele hineinkamen: Es muss eine sein. Ohne UNIQUE kommen zwei hinein, und ohne NOT NULL auch die Kategorie ohne Namen.
Sieh es dir an
Noch einmal
Eine Tabelle mit demselben Namen lässt sich nicht noch einmal anlegen:
Ein Skript, das mehr als einmal laufen darf, schreibt IF NOT EXISTS. Dann wird die vorhandene Tabelle nicht angefasst, auch wenn sie andere Spalten hat:
Probier es aus
Die Produkte
Füge die Regeln von products hinzu: name ist Pflicht, category_id ist Pflicht und zeigt auf categories, price ist Pflicht und größer als 0, und stock ist Pflicht, standardmäßig 0 und darf nicht unter 0 liegen.
Lösung
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category_id INTEGER NOT NULL REFERENCES categories(id),
price REAL NOT NULL CHECK (price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);category_id INTEGER NOT NULL REFERENCES categories(id), price REAL NOT NULL CHECK (price > 0) und stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0). Die Prüfung probiert ein Produkt mit Preis 0, eines mit Bestand -1 und eines ohne Namen aus, und alle müssen abgewiesen werden. Ein Produkt ohne stock muss 0 bekommen.
Genauer, für Neugierige
Die Preise sind hier REAL, wie im Shop des Kurses. Ein echtes Programm für Geld speichert Cent als ganze Zahl (price_cents INTEGER) oder nutzt in PostgreSQL NUMERIC(10, 2), damit es keine Summen wie 0,1 + 0,2 = 0,30000000000000004 gibt.
Probier es aus
Die Kunden
Füge die Regeln von customers hinzu: name und email sind Pflicht, email darf sich nicht wiederholen, und city darf fehlen.
Lösung
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
city TEXT
);email TEXT NOT NULL UNIQUE, und city TEXT ohne NOT NULL. Mit der E-Mail meldet sich der Kunde an, und zwei Personen mit einer E-Mail sind ein Fehler. Die Stadt ist keine Pflicht: Auch ein Kunde ohne Stadt kann bestellen.
Probier es aus
Die Bestellungen
Füge die Regeln von orders hinzu: customer_id ist Pflicht und zeigt auf customers, ordered_at ist Pflicht, und status ist Pflicht, standardmäßig 'new' und darf nur 'new', 'paid', 'delivered' oder 'cancelled' sein.
Lösung
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
ordered_at TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'new'
CHECK (status IN ('new', 'paid', 'delivered', 'cancelled'))
);status TEXT NOT NULL DEFAULT 'new' CHECK (status IN ('new', 'paid', 'delivered', 'cancelled')). Die Prüfung probiert den Status 'lost' und eine Bestellung ohne Datum aus, und eine Bestellung ohne Status muss 'new' werden.
Übung
Die Zeilen der Bestellung
Füge die Regeln von order_items hinzu: order_id zeigt auf orders, und wird die Bestellung gelöscht, werden ihre Zeilen mitgelöscht; product_id zeigt auf products; quantity ist Pflicht und größer als 0; unit_price ist Pflicht; der Primärschlüssel ist das Paar (order_id, product_id). Alle Spalten sind Pflicht.
Lösung
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES products(id),
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price REAL NOT NULL,
PRIMARY KEY (order_id, product_id)
);REFERENCES orders(id) ON DELETE CASCADE und am Ende PRIMARY KEY (order_id, product_id). Die Prüfung schreibt dasselbe Produkt zweimal in die Bestellung (die zweite Zeile muss der Schlüssel abweisen), eine Zeile mit 0 Stück, löscht dann die Bestellung und prüft, dass ihre Zeilen weg sind. Mit einer eigenen id für die Zeile könnte ein Produkt zwei Zeilen in einer Bestellung sein.
Die Idee
Die fünf Tabellen
Die Datenbank steht:
Jede Regel, die du hinzugefügt hast, ist ein Fehler, den kein Programm mehr schreiben kann: ein Produkt ohne Preis, zwei Kunden mit einer E-Mail, eine Bestellung mit einem Status, den es nicht gibt. Die nächste Etappe füllt die Tabellen.
Test