256 Zeilen
12 KiB
Markdown
256 Zeilen
12 KiB
Markdown
# 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.
|