# Flashcard Deck: Datenbankdesign & Solution2-Patterns **Thema**: Relationales Datenbankdesign mit echten Solution2-Beispielen **Schwierigkeit**: Anfaenger -> Fortgeschritten **Karten**: 20 --- ## Karte 1 | Basis **Q:** Was ist ein Primary Key und welche Konvention verwendet Solution2? **A:** Ein Primary Key ist ein eindeutiger Bezeichner fuer jeden Datensatz. Solution2-Konvention: Der Primary Key heisst meistens wie die Tabelle + Suffix, z.B.: - Tabelle `orders` -> PK `orders` (INTEGER) - Tabelle `parts` -> PK `parts` (INTEGER) - Tabelle `company` -> PK `company` (INTEGER) Die Werte werden ueber Nummernkreise (Counter-Tabellen) vergeben, NICHT ueber PostgreSQL-Sequences. --- ## Karte 2 | Basis **Q:** Was ist ein Foreign Key? Nenne ein Beispiel aus Solution2. **A:** Ein Foreign Key verknuepft zwei Tabellen. In Solution2 z.B.: - `orderitems.orders` -> verweist auf `orders.orders` (Welchem Auftrag gehoert die Position?) - `orderitems.parts` -> verweist auf `parts.parts` (Welcher Artikel?) - `customerinvoices.company` -> verweist auf `company.company` (Welcher Kunde?) Der FK stellt sicher, dass nur existierende Auftraege/Artikel/Kunden referenziert werden koennen. --- ## Karte 3 | Basis **Q:** Was bedeutet Normalisierung und warum ist sie wichtig? **A:** Normalisierung vermeidet Datenredundanz. Beispiel aus Solution2: **Falsch (unnormalisiert):** In `orders` den Kundennamen direkt speichern -> bei Namensaenderung muessten ALLE Auftraege aktualisiert werden. **Richtig (normalisiert):** `orders.company` enthaelt nur die Company-ID -> der aktuelle Name wird aus `company` gelesen. Solution2 ist voll normalisiert: Stammdaten (Adressen, Artikel) werden an EINER Stelle gepflegt und ueberall referenziert. --- ## Karte 4 | Basis **Q:** Was ist der Unterschied zwischen `INTEGER`, `BIGINT`, `SERIAL` und `TEXT` in PostgreSQL? **A:** - `INTEGER`: 32-bit Ganzzahl (-2 Mrd bis +2 Mrd) — Standard fuer Solution2 IDs - `BIGINT`: 64-bit Ganzzahl — fuer sehr grosse Zaehler - `SERIAL`: Kein echter Datentyp! Kurzform fuer `INTEGER + automatische Sequence` - `TEXT`: Unbegrenzt langer String — in PostgreSQL genauso schnell wie VARCHAR - `BOOLEAN`: true/false — z.B. `valid`-Spalte in Solution2 - `TIMESTAMP`: Datum + Uhrzeit — z.B. `created`, `modtime` in Solution2 - `NUMERIC(p,s)`: Exakte Dezimalzahl — fuer Geldbetraege (z.B. Rechnungssummen) --- ## Karte 5 | Basis **Q:** Warum verwendet Solution2 `NUMERIC` statt `FLOAT` fuer Geldbetraege? **A:** `FLOAT` hat Rundungsfehler: `0.1 + 0.2 = 0.30000000000000004`. Bei Geldbetraegen ist das inakzeptabel — eine Rechnung ueber 100,00 Euro darf nicht als 99,99999 gespeichert werden. `NUMERIC(15,2)` speichert exakt 15 Stellen mit 2 Nachkommastellen. Solution2 nutzt dies fuer alle Betrags-Felder (Preise, Summen, Steuern). Die `orders`-Tabelle hat z.B. ueber 20 NUMERIC-Spalten fuer verschiedene Betraege. --- ## Karte 6 | Basis **Q:** Was ist ein INDEX und wann braucht man einen? **A:** Ein Index beschleunigt Abfragen — wie ein Stichwortverzeichnis in einem Buch. PostgreSQL muss nicht die ganze Tabelle durchsuchen. **Solution2-Beispiel:** Die `orders`-Tabelle hat ~100 Spalten und kann tausende Zeilen haben. Ohne Index auf `company` muesste PostgreSQL JEDEN Auftrag pruefen um die eines Kunden zu finden. ```sql CREATE INDEX idx_orders_company ON orders(company); -- Jetzt: SELECT * FROM orders WHERE company = 42 ist schnell ``` Indizes kosten Speicher und verlangsamen INSERTs — nur dort anlegen wo oft gesucht wird. --- ## Karte 7 | Mittel **Q:** Wie sieht die Tabellenstruktur eines typischen Solution2-Geschaeftsprozesses aus? **A:** Am Beispiel Auftragsabwicklung: ``` orders (Auftragskopf) |-- orderitems (Auftragspositionen) | +-> deliverynotes (Lieferscheine) |-- deliverynoteitems | +-> customerinvoices (Rechnungen, 90 Spalten!) |-- customerinvoiceitems ``` Jede Ebene referenziert die vorherige per FK. Ein Auftrag kann mehrere Lieferscheine haben, ein Lieferschein kann zu einer Rechnung fuehren. Das ist die klassische **Auftrags-Lieferschein-Rechnung-Kette**. --- ## Karte 8 | Mittel **Q:** Warum hat die `orders`-Tabelle in Solution2 ueber 100 Spalten? **A:** Solution2 speichert am Auftragskopf alle relevanten Informationen zum Zeitpunkt der Erstellung: - Kundenreferenz, Lieferadresse, Rechnungsadresse - Zahlungsbedingungen, Waehrung, Wechselkurs - Steuerinfos (MwSt-Saetze, Steuer-IDs) - Rabatte, Zuschlaege, Frachtkosten - Status-Flags, Genehmigungsstatus, Prioritaet - Audit-Felder (created, modtime, revisor, valid) Das ist ein bewusstes Design: Zum Zeitpunkt der Rechnung muessen die Original-Konditionen verfuegbar sein, auch wenn sich Kundendaten seitdem geaendert haben. --- ## Karte 9 | Mittel **Q:** Was ist eine Nummernkreis-Tabelle und warum nutzt Solution2 diese statt PostgreSQL-Sequences? **A:** Solution2 hat eine Tabelle `nextnumber` die Zaehler fuer verschiedene Nummernkreise verwaltet: - Auftragsnummern, Rechnungsnummern, Artikelnummern etc. - Jeder Nummernkreis hat einen eigenen Eintrag mit dem naechsten freien Wert. **Vorteile gegenueber Sequences:** Lueckenlose Nummernfolgen (gesetzlich erforderlich bei Rechnungen!), flexible Formate (z.B. "RE-2026-0001"), verschiedene Nummernkreise pro Geschaeftsjahr. **Nachteil:** Muss in einer Transaktion gesperrt werden um Duplikate zu vermeiden. --- ## Karte 10 | Mittel **Q:** Was ist ein Composite Key? Gibt es Beispiele in Solution2? **A:** Ein Composite Key besteht aus mehreren Spalten die zusammen eindeutig sind. In Solution2 z.B.: - `orderitems`: Kombination aus `orders` (Auftrags-ID) + `position` (Positionsnummer) - `additionalfields`: Kombination aus `tablename` + `fieldname` (Zusatzfeld-Definition) - `inventoryquantities`: Kombination aus `parts` + `warehouse` (Bestand pro Artikel pro Lager) In PostgreSQL: `PRIMARY KEY (orders, position)` oder `UNIQUE (parts, warehouse)`. --- ## Karte 11 | Mittel **Q:** Was ist `EXPLAIN ANALYZE` und wie nutzt man es fuer Solution2? **A:** `EXPLAIN ANALYZE` zeigt den tatsaechlichen Ausfuehrungsplan einer Query: ```sql EXPLAIN ANALYZE SELECT o.*, c.name1 FROM orders o JOIN company c ON c.company = o.company WHERE o.valid = true AND o.status = 'A'; ``` **Worauf achten:** - `Seq Scan` auf grosse Tabellen = Index fehlt! - `Nested Loop` bei vielen Zeilen = ineffizient, besser Hash/Merge Join - `actual time` vs `rows` = Verhaeltnis zeigt Effizienz Typischer Fix: `CREATE INDEX idx_orders_status ON orders(status) WHERE valid = true;` --- ## Karte 12 | Mittel **Q:** Was ist ein Partial Index und wann ist er in Solution2 sinnvoll? **A:** Ein Partial Index indexiert nur einen Teil der Tabelle: ```sql CREATE INDEX idx_orders_active ON orders(company, status) WHERE valid = true; ``` Da Solution2 Soft-Delete nutzt (`valid = false`), sind oft 80%+ der Datensaetze "geloescht" aber noch in der Tabelle. Ein Partial Index auf `WHERE valid = true` ist viel kleiner und schneller als ein voller Index. **Best Practice:** Fuer alle haeufigen Queries auf aktive Datensaetze Partial Indices verwenden. --- ## Karte 13 | Mittel **Q:** Was sind die wichtigsten PostgreSQL-Datentypen die Solution2 NICHT standardmaessig nutzt, die aber nuetzlich waeren? **A:** - `JSONB`: Strukturierte Daten ohne festes Schema — ideal fuer Konfigurationen, API-Responses. Kann indexiert werden! - `ARRAY`: Mehrere Werte in einer Spalte — z.B. Tags, Kategorie-Listen - `UUID`: Universell eindeutige IDs — besser als INTEGER fuer verteilte Systeme - `TSTZRANGE`: Zeitbereiche — z.B. Gueltigkeitszeitraeume fuer Preise - `INET/CIDR`: IP-Adressen — fuer Audit-Logs Solution2 ist historisch gewachsen (FrontBase-Herkunft) und nutzt hauptsaechlich INTEGER, TEXT, NUMERIC, BOOLEAN, TIMESTAMP. --- ## Karte 14 | Fortgeschritten **Q:** Was ist ein Deadlock und wie vermeidet man ihn in Solution2? **A:** Ein Deadlock entsteht wenn zwei Transaktionen gegenseitig auf Ressourcen warten: - Transaktion A sperrt Zeile 1, will Zeile 2 - Transaktion B sperrt Zeile 2, will Zeile 1 -> Beide warten ewig. PostgreSQL erkennt das und bricht EINE Transaktion ab (Error 40P01). **Vermeidung in Solution2:** - Immer in der gleichen Reihenfolge sperren (z.B. erst Auftragskopf, dann Positionen) - Transaktionen so kurz wie moeglich halten - SELECT FOR UPDATE nur wenn noetig - Bei Error 40P01: Transaktion wiederholen (Retry-Pattern) --- ## Karte 15 | Fortgeschritten **Q:** Was bedeutet `ON DELETE CASCADE` und wo ist es in Solution2 gefaehrlich? **A:** `ON DELETE CASCADE` loescht automatisch abhaengige Datensaetze wenn der Parent geloescht wird. Z.B. `orderitems.orders REFERENCES orders(orders) ON DELETE CASCADE` wuerde alle Positionen loeschen wenn ein Auftrag geloescht wird. **In Solution2 NICHT verwenden!** Weil: 1. Solution2 nutzt Soft-Delete (`valid = false`) statt echtem DELETE 2. Historische Daten muessen erhalten bleiben (Revisionssicherheit) 3. CASCADE kann unbeabsichtigt riesige Datenverluste verursachen Solution2 nutzt stattdessen `ON DELETE RESTRICT` (Standard) — Loeschen wird verhindert wenn abhaengige Daten existieren. --- ## Karte 16 | Fortgeschritten **Q:** Wie funktioniert `pg_dump` fuer Solution2-Backups? **A:** ```bash # Vollstaendiges Backup (Custom-Format, komprimiert) pg_dump -U postgres -Fc -Z9 -d masterdemo -f backup.dump # Nur Schema (ohne Daten) pg_dump -U postgres -s -d masterdemo -f schema.sql # Nur eine Tabelle pg_dump -U postgres -t orders -d masterdemo -f orders.sql # Wiederherstellen pg_restore -U postgres -d masterdemo -c backup.dump ``` **Fuer Docker:** `docker exec postgres-local pg_dump -U postgres -Fc -d masterdemo > backup.dump` Automatisierte Backups laufen auf dem Pi taeglich um 02:30 Uhr. --- ## Karte 17 | Fortgeschritten **Q:** Was ist der Unterschied zwischen `VACUUM` und `VACUUM FULL` in PostgreSQL? **A:** - `VACUUM`: Markiert geloeschte Zeilen als wiederverwendbar. Blockiert KEINE Tabellen, laeuft im Hintergrund (Autovacuum). - `VACUUM FULL`: Schreibt die gesamte Tabelle physisch neu, gibt Speicher ans OS zurueck. **Sperrt die Tabelle exklusiv!** **Fuer Solution2:** Autovacuum reicht normalerweise. `VACUUM FULL` nur nach massiven Loeschaktionen (z.B. nach Bereinigung alter Bewegungsdaten). Nie waehrend Geschaeftszeiten ausfuehren! --- ## Karte 18 | Fortgeschritten **Q:** Was sind CTEs (Common Table Expressions) und wie nutzt man sie? **A:** CTEs sind "benannte Subqueries" — machen komplexe Queries lesbar: ```sql -- Alle Kunden mit offenem Auftragsvolumen > 10.000 EUR WITH offene_auftraege AS ( SELECT company, SUM(totalamount) AS gesamt FROM orders WHERE valid = true AND status IN ('A', 'B') GROUP BY company ) SELECT c.name1, oa.gesamt FROM offene_auftraege oa JOIN company c ON c.company = oa.company WHERE oa.gesamt > 10000 ORDER BY oa.gesamt DESC; ``` CTEs koennen auch rekursiv sein — z.B. fuer Stuecklisten-Aufloesung (BOM explosion). --- ## Karte 19 | Fortgeschritten **Q:** Was sind Window Functions und wozu braucht man sie in Solution2? **A:** Window Functions berechnen Werte ueber eine Gruppe von Zeilen OHNE sie zu gruppieren: ```sql -- Kumulierte Umsaetze pro Kunde, nach Datum sortiert SELECT company, orderdate, totalamount, SUM(totalamount) OVER (PARTITION BY company ORDER BY orderdate) AS kumuliert, ROW_NUMBER() OVER (PARTITION BY company ORDER BY orderdate DESC) AS rang FROM orders WHERE valid = true; ``` **Typische Anwendungen:** Ranglisten, laufende Summen, Vergleich mit Vorperiode, Top-N pro Gruppe. Viel effizienter als Subqueries! --- ## Karte 20 | Fortgeschritten **Q:** Was muss man bei der Migration von FrontBase nach PostgreSQL beachten? **A:** Solution2 wurde urspruenglich fuer FrontBase entwickelt. Wichtige Unterschiede: - **Case Sensitivity:** PostgreSQL ist case-sensitive bei Spaltennamen in Anfuehrungszeichen (`"Name"` != `name`) - **Boolean:** FrontBase nutzt INTEGER (0/1), PostgreSQL hat echtes BOOLEAN - **BLOBs:** Verschiedene Speichermechanismen (FrontBase Inline vs PostgreSQL TOAST) - **Transaktionen:** FrontBase hat andere Isolation-Level-Defaults - **Sequences:** FrontBase hatte eigenes Counter-System, PostgreSQL hat native Sequences Die Migration nutzt ein spezielles Toolset (`sol2-migration`) das Datentyp-Konvertierung und BLOB-Transfer automatisiert.