DB - Datenpflege

[Honsek]

Hallo.

Meine Frage zielt darauf ab, wie man mit Daten umgeht, die in einer DB gelöscht werden. Fall: In meinem DB-Projekt RentABike sind einige Räder in der Tabelle bikes gespeichert, jetzt aber real verschlissen und können verschrottet werden. Kann man diese Räder so einfach aus der Tabelle bikes löschen oder gibt es einige Dinge - möglicherweise schon bei der Tabellen-Definition - zu beachten?

Mit flottem Gruß
Honsek

[tionov]

Nicht ganz einfach zu beantworten. Wenn Felder in anderen Tabellen sich darauf beziehen, also der PrimaryKey von 'bikes' als Foreign Key z.B. in 'contracts' gespeichert ist, dann hängt es von der Beziehung ab und davon, was als Aktion definiert ist, wenn der Datensatz gelöscht oder geändert wird.

'On Update Cascade' und 'On Delete Cascade' bei der Definition des Foreign Key geben die Änderung an die andere Tabelle weiter.

https://www.postgresql.org/docs/11/ddl- ... TRAINTS-FK

Also: Löschst du den Datensatz in 'bikes' wird auch der zugehörige in 'contracts' gelöscht, wenn der dortige Fremdschlüssel entsprechend definiert ist. Fehlt die Definition verweigert die Datenbank die Löschung, weil von anderen Tabellen noch darauf verwiesen wird.

Wahrscheinlich will man noch zehn Jahr in der Buchhaltung die Verträge sehen, dann darf der Datensatz natürlich nicht gelöscht werden. Das kann man lösen, indem 'bikes' noch eine Spalte bekommt, 'valid', die per default auf 'true' steht (universeller ist -1 für true und 0 für false). Dann arbeitet man auch am besten immer mit einem View 'vwbikes' der nur die validen anzeigt.

Nochmal, ich rate dir zu einer Benennung wichtiger Teile der DB in einer Art und Weise, die im Namen gleich sagt, worum es sich handelt. Dadurch werden alle Abfragen später besser lesbar. Für Tabellennamen verwendet man auch keinen Plural.

In meinen Dbs schreibe ich 'tbcustomer', 'vwcustomer', 'pkcustomer', 'fkcustomer' und weiß dadurch sofort, um was es sich handelt.

[Honsek]

Hallo.

Ja, die Bezeichner im Datenbankumfeld folgten bisher meiner eigenen Logik - die ich für gut befand. Ich könnte mich auch dem Vorschlag von tionov nähern. Dazu müsste ich aber wissen, welche Bezeichner er für welche Objekte vorschlägt.

Mit freundlichem Gruß

Honsek

[Honsek]

Hallo.

Ich habe die Anregungen von tionov - bezogen auf die Bezeichner in meinem DB-Projekt RentABike - nach meinem Kenntnisstand umgesetzt (-> Anhang) und muss feststellen, dass sich die SQL-Anweisungen jetzt schneller erzeugen und wegen der verwendeten Präfixe auch besser lesen lassen. Hier ein Beispiel:

Code: Alles auswählen

CREATE VIEW IF NOT EXISTS vwcountry 
AS 
SELECT pkcountry AS "ID", 
       country AS "LAND (KÜRZEL)", 
       description AS "LÄNDERNAME" 
FROM tbcountry;

Mit freundlichen Grüßen

Honsek

rab_structure.sql.txt

(4.5 KiB) 250-mal heruntergeladen

[tionov]

Sorry, ich hatte nicht zeitnah geantwortet und muss nachtragen, wie ich aus meiner Erfahrung heraus Objekte im Datenbank-Schema benenne. Ich arbeite freiwillig fast nur mit Postgresql, mit anderen DBs, wenn ein Projekt es unbedingt verlangt.

Im Laufe der Jahre habe ich in der Benennung verschiedene Fehler gemacht, die dazu führten, dass ich Projekte später wieder umbaute und heute habe ich ein ganz einfache Art der Benennung für SQL und das Schema:

Alles wird klein geschrieben, auch SQL. Wenn irgend ein Tool SQL Befehle in Versalien ausspuckt, bitte, dann übernehme ich das auch mal, aber wenn ich selbst SQL schreibe, dann grundsätzlich klein.

Keine Umlaute, keine Sonderzeichen. Nie! [a-z] muss reichen. Wenn ein Feldbezeichner unbedingt aus mehreren Wörtern bestehen soll und sonst unlesbar wäre, kann noch ein Underscore verwendet werden ("_").

Auch keine Zahlen.Wer item1, item2, item3 in Spalten stehen hat, hat schon irgendwo die Normalisierung gebrochen.

Stets Prefixe verwenden für Tabellen ("tb"), Views ("vw"), künstliche Primary Keys ("pk") und Foreign keys, die auf künstliche Primary Keys verweisen, bekommen den Prefix ("fk").

Sequencen baut Postgresql für den Pseudo-Typ 'serial' selbst und macht dann daraus 'tbname_pkname_seq'. Das passt für mich so. Ich kümmere mich einfach nicht darum.

Wie Honsek schön gezeigt hat, ergeben sich dann daraus Schemas wie:
shell code

create table tbcontract 
    (
      pkcontract serial, 
     -- ...
      fkcustomer integer not null,
      foreign key (fkcustomer) references tbcustomer (pkcustomer)
    );

So sagen mir die wichtigsten Objekte bereits anhand ihrer Namen, was sie sind und welche Funktion sie einnehmen. Mit diesem einfachen Regelwerk der Benennung decke ich praktisch hundert Prozent meiner Bedürfnisse ab.

[tionov]

Hallo Honsek,

wir habe das schon privat besprochen, aber ich will trotzdem meine Anmerkungen und Einwände hier posten, damit Leute daraus lernen können.

rab_structure.sql.txt

Uiuiui, da gibt es noch Einiges ...
shell code

CREATE TABLE sqlite_sequence(name,seq);

Verstehe ich nicht, aber ich kenne sqlite nicht gut.

Ich schätze mal, dass 'tbrentedobject' vorher mal 'bikes' hieß und diese
meint, oder? Also wir können jetzt auch Ridschas und Anhänger speichern,
nicht? Ich würde die Tabelle anders nennen, denn 'rented' ist Partizip
Präsens und meint "gerade vermietet", das ist aber nicht der Fall für
Räder, die gerade im Lager stehen.

Was speichern tbsort und tbstatus?

pksize, pkprice, pktype, pkcountry, pkidentdocument als Integer
(autoincrement) in den jeweiligen Tabellen machen keinen Sinn, weil (am
Beispiel size):

"size" ist bereits eindeutig, es reicht(e) also:
shell code

CREATE TABLE tbsize
    (
      pksize      TEXT CHECK(LENGTH(size)=2) PRIMARY KEY
    );

Und ... wenn ich nicht einen extra (künstlichen) Primary Key verwende,
nenne ich das auch nicht so, sondern mache nur:
shell code

CREATE TABLE tbsize
    (
      size      TEXT CHECK(LENGTH(size)=2) PRIMARY KEY
    );

Dasselbe gilt für tbcountry, deren Inhalte "DE", "IT", "AT" bereits
Primary Keys sind. 'DEFAULT "DE"' macht überhaupt keinen Sinn, ähm, die
ganzen Defaults wie du sie verwendest machen keinen Sinn, z.B. in
tbidentdocument.identdocument:
shell code

'identdocument      TEXT NOT NULL DEFAULT "Ausweis"'

In diese Tabelle dürfen eh nur lauter unterschiedliche Strings rein,
nämlich:

"Führerschein", "Ausweis", "Fingerabdruck"

die zugleich Primary keys sind.

Es gehört also der Default in tbcustomer rein, so:
shell code

CREATE TABLE tbcustomer
(
...
identdocument TEXT NOT NULL DEFAULT 'Ausweis'
...
FOREIGN KEY(identdocument)  REFERENCES tbidentdocument( identdocument )
);

Damit können in 'tbcustomer' in der Spalte 'identdocument' nur Entitäten
gespeichert werden, die in 'tbidentdocument' enthalten sind und wenn
nichts angegeben wurde schreibt die DB automatisch 'Ausweis' rein.

Ich denke, das ist das, was du willst.

(Jetzt ganz davon abgesehen, ob wir wirklich eine separate Tabelle
brauchen für drei Einträge).

Das gilt für o.g. Tabellen ebenso.

Preise würde ich hier definitiv nicht in einer eigenen Tabelle
speichern, sondern bei dem Objekt, das einen Preis hat (also in einem
Feld 'tbrentedobject.price'), oder was spräche dem entgegen?

[Honsek]

Hallo.
Ich habe meine Datenbank RentABike nach den Hinweisen von tionov neu aufgesetzt. Das betraf die Tabellen-Definition der einzelnen DB-Tabellen; eingeschlossen die Festlegung der Primärschlüssel und der notwendigen Fremdschlüssel. Damit Leser schnell zu einem Ergebnis kommen, habe ich auch einige Datensätze eingefügt. Die komplette Datenbank wird in einem Dump für SQlite3 als Anhang bereitgestellt. Bitte die Extension .txt entfernen!

In einer Konsole kommt man mit dem .read-Kommando schnell zum Ziel und kann sich als Erfolgskontrolle bereits einige Datensätze anzeigen lassen:

Code: Alles auswählen

$ sqlite3 rab.sqlite
-- Loading resources from /home/hans/.sqliterc
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .read rab_dump.sql 
sqlite> SELECT order_date, rental_begin, rental_end FROM tbcontract;
order_date  rental_begin  rental_end
----------  ------------  ----------
2021-05-04  2021-07-10    2021-07-17
2021-05-04  2021-07-10    2021-07-17
2021-05-04  2021-07-10    2021-07-15
2021-06-06  2021-06-12    2021-06-19
2021-06-06  2021-06-12    2021-06-19
2021-06-10  2021-06-19    2021-06-26
sqlite> .quit

Ein Problem bleibt - und da setzte ich auf die Hilfe der Leserinnen und Leser: Bisher liegen die Preise für die Ausleihobjekte (es gibt neben den Rädern zum Beispiel auch Rad-Anhänger oder Laufräder) in einer eigenen Tabelle. Die Preise sollen später im Gambas-Programm in einer DB-Combobox ausgewählt werden können, um so die Eingabe zu erleichtern und fehlerfreie Eingaben ermöglichen. Als Datentyp für einen Geldwert wird nun auf Integer gesetzt, was Konvertierungen beim Eingaben und Anzeigen der Preise erfordert.

Frage: Gibt es einen hinreichend plausiblen Grund, den Preis bei den Ausleihobjekten in der DB-Tabelle tbrentalobject als weiteres Feld einzufügen?

Mit freundlichem Gruß

Honsek

rab_dump.sql.txt

(8.79 KiB) 239-mal heruntergeladen

[PJBlack]

Hallo.

Hallo!

[tionov]

Frage: Gibt es einen hinreichend plausiblen Grund, den Preis bei den Ausleihobjekten in der DB-Tabelle tbrentalobject als weiteres Feld einzufügen?

Ich verstehe nicht ganz, was du da wirklich vorhast, bzw. ich kann es mir denken. Ein Verleih hat z.B. 50 Herrenräder und 50 Damenräder. Die haben – logisch – denselben Preis. Wenn man den Preis bei dem einzelnen Rad speichert, kann es leicht vorkommen, dass sich der User vertippt und bei einem einzelnen Rad einen anderen Preis eingibt als der, der für alle anderen desselben Typs gilt.

Das möchtest du verhindern? Wenn man eine Preiserhöhung macht, sollen auf einmal die Preise aller Räder des selben Typs erhöht werden?

[Honsek]

Hallo.

Es gibt einen weiteren Aspekt, den ich bisher nicht berücksichtigt hatte. Unser Autor Gianluigi aus Genua machte darauf aufmerksam, dass die ausleihende Firma ein Problem bekommt, wenn sie die Preise aus wirtschaftlichen Gründen zum Zeitpunkt A ändern möchte. Es könnte ja sein, dass 10 Kunden bereits Räder zum alten Preis - auf den sie vertrauen können - weit vor dem Zeitpunkt A reserviert hatten. Was nun - was tun? Wie bekommt man dieses nahende Problem in den Griff und wie bildet man die zeitliche Preisbindung im (aktuellen) Datenbank-Modell ab? Jede Idee wird sorgfältig geprüft.

Mit freundlichem Gruß

Hans

[PJBlack]

es sollten hier die gleichen Mechanismen greifen mit denen Du gegenüber dem Finanzamt, Stichwort GoB/GoBD, (viel Spaß mit denen) eine ordentliche Buchführung dokumentierst ...

ich finde übrigens die Idee alle Fahrräder ein Preis als falsch ... (14" Klapprad <-> 24" Crossbike <-> 26" Rad mit Hilfsantrieb <-> 28" Elektrorad; Kinderrad <-> Damenrad <-> Herrenrad <-> Trike; mit Sonderausstattung <-> ohne Sonderausstatung; etc.)

ich würde, ohne groß darüber nachgedacht zu haben, für jeden Artikel einen separaten Preis pflegen mit dazugehöriger Tabelle der Änderungen ... so könntest Du sekundengenau belegen wann für welchen Artikel welcher Peis galt.

Ansonsten holst Du Dir den Preis nicht aus den Artikelstammdaten sondern aus der Auftragsbestätigung (die Du ja eindeutig fortlaufend und nicht manipulierbar gespeichert hast) ... berücksichtigen musst Du noch Preissenkungen (die Du ja natürlich dem Kunden weitergeben möchtest) sowie z.B. eine Änderung des Mehrwertsteuersatzes, den der Kunde bei Rechnungsstellung tragen muss ...

Der Markt von Faktura und Buchhaltungssoftware ist relativ dünn ... wird schon einen Grund haben.

[tionov]

Es gibt einen weiteren Aspekt, den ich bisher nicht berücksichtigt hatte. Unser Autor Gianluigi aus Genua machte darauf aufmerksam, dass die ausleihende Firma ein Problem bekommt, wenn Sie die Preise aus wirtschaftlichen Gründen zum Zeitpunkt A ändern möchte. Es könnte ja sein, dass 10 Kunden bereits Räder zum alten Preis - auf den sie vertrauen können - weit vor dem Zeitpunkt A reserviert hatten. Was nun - was tun? Wie bekommt man dieses nahende Problem in den Griff und wie bildet die zeitliche Preisbindung im (aktuellen) Datenbank-Modell ab? Jede Idee wird sorgfältig geprüft.

Der Preis braucht mehrere Plätze zur Speicherung.

Als erstes sollte man den Preis bei dem Produkt speichern, um das es geht. Wenn man Standardpreise verwenden möchte, könnte man den Produkten auch einen Typ verpassen und den Preis in der Tabelle speichern, in der die Typen aufgelistet sind. Dann kostet z.B. jedes Herren- und Damenrad 15 EUR, ein Tandem 20 und jeder Anhänger 5.

Der Vorteil dieser Vorgehensweise ist, dass man so einen Preis ändert und der sich damit für alle Räder dieses Typs geändert hat. Bei einem Fahrradverleih würde ich so vorgehen.

Dann muss natürlich, wenn ein Rad vermietet wird, der Preis zusätzlich im zugehörigen Kontrakt gespeichert werden. Denn dieser Preis gilt ja nur für den Zeitpunkt der Vermietung und der Preis des Rades wird sich sich ja später mal ändern. Da darf dann nicht die Buchhaltung plötzlich für vergangene Kontrakte höhere Preise aufweisen, weil der Preis des Rades später geändert wurde. Darum ist der Preis des Rads beim Kontrakt der zweite Speicherort.

Und dann noch etwas: Ihr solltet die Preise netto speichern. Es kommt noch die Mehrwertsteuer hinzu und die kann sich auch ändern. Die Mehrwertsteuer muss auch im Kontrakt gespeichert werden, weil die ist ja nicht ewig gültig.

Ihr habt noch ein weiteres Problem. Wollt ihr wirklich pro Kontrakt nur ein einziges Rad vermieten? Was ist, wenn eine Familie kommt und fünf Räder mieten will? Oder eine Reisegruppe kommt? Wollt ihr da für jedes Rad eine eigene Rechnung aufmachen? Das macht doch keinen Sinn.

Also braucht ihr tbcontract und tbcontractdetail (mit fkcontract, das pkcontract von tbcontract referenziert), in letzterer Tabelle stehen dann die Einzelpositionen, mit aktuell gültigem Preis und Mwst. In tbcontract das Datum, fkcustomer usw.

Ich habe ja mal gesagt, das Ding ist kompliziert.

[Honsek]

Ich habe ja mal gesagt, das Ding ist kompliziert.

Hallo tionov,

wie wahr. Aber Aufgeben ist (bisher) keine Option. Die Hinweise habe ich verstanden. Jetzt muss ich diese nur noch in das DB-Modell einpflegen. Wenn das erfolgt ist, werde ich danach wieder versuchen, alle notwendigen Abfragen in SQL zu formulieren. Zuvor werde ich mir noch einmal die Theorie zu den Joins ansehen und versuchen, diese zu durchblicken. Dann werde ich berichten oder hier wieder gezielt nachfragen.

Was mir jetzt schon auffällt: Kann ich bei der Speicherung des Netto-Preises noch auf den Datentyp Integer setzen? Beispiel: Aktuell kostet ein Rad nach der Preisliste 6€. Das ergäbe einen Nettopreis von 5,04€ bei MwSt = 19%. Was nun - was tun - bezogen auf die Speicherung?

Mit freundlichem Gruß

Hans

[tionov]

Ich habe ja mal gesagt, das Ding ist kompliziert.

wie wahr. Aber Aufgeben ist (bisher) keine Option.

Die Frage ist auch, wie kompliziert macht man es. Macht man ein Spielbeipiel oder will Gian echt einen Fahrradverleih gründen? Ich meine, referentielle Integrität haben wir schon jetzt im Schema enthalten und in einem Spielfahrradverleih brauchen wir uns weder um Mwst noch um viele Räder pro Vermietung zu kümmern. Dann muss Claudia, wenn sie ein Rad mit Anhänger mietet, eben zwei Verträge eingehen.

Dass wir die Preise zweimal speichern müssen, einmal beim Rad, einmal beim Kontrakt, ist aber essentiell, alles andere wäre ein grober Fehler.

[tionov]

Es kam die Frage auf:

Was ist die letzte Tabelle?
Welche Einzelpositionen sollen das sein?
Was bleibt dann noch in der Tabelle tbcontract an Feldern übrig?

Von mir gemeint war: tbcontract die erste und tbcontractdetail die zweite. In Kurzform:
shell code

tbcontract:

pkcontract
fkcustomer
datum
paid (boolean = bezahlt, default 0)

tbcontractdetail:

pbcontractdetail
fkcontract
fkrentalobject
price
broughtback_at (date = zurückgebracht am)

So kann Papa Meier kommen und für sich und seine Kinder Tina und Timo mit einem Vertrag drei Fahrräder mieten. Und die Fahrräder können zu verschiedenen Zeiten zurückgebracht werden.