Datenbankmodellierung: Kleiner Shop

    Datenbankmodellierung: Kleiner Shop

    Guten Tag,

    ich habe ein Problem mit der Datenbankmodellierung. Ich musste mich aufgrund meines Vorhabens mit dem Thema Datenbanken auseinandersetzen.
    ich bräuchte Hilfe, ob mein Ergebnis, was ich bis jetzt erstellt habe so korrekt ist, wo ich Änderungen vornehmen muss und weiter unten, wie ich noch ein spezielles Vorhaben inkludieren kann.

    Ich habe bereits in einem Datenbank-Forum um Hilfe gebeten, leider bekam ich diese nicht so wirklich.

    1. Was möchte ich erreichen?

    Ich entwickle gerade für den privaten Gebrauch als blutiger Hobby-Programmierer für einen kleinen Bekanntenkreis ein Programm, welches die Verwaltung von Getränken vereinfachen soll. Es geht hierbei um Buchungen/Käufen.
    Man kann über ein Tablet auf eine Liste von Getränken zugreifen und ganz leicht hier einen Einkauf tätigen. Sobald man nun den Kauf bestätigt soll nun folgendes passieren, weshalb ich auch eine Datenbank benötige:
    Das Programm soll den Einkauf in eine Datenbank verarbeiten, indem es einzelne Getränke mit ihren Preis erfasst und dem entsprechenden Benutzer das berechnet.

    2. Wie bin ich das Problem angegangen?

    Ich habe mir überlegt, welche Möglichen Tabellen gebraucht werden (Dahinter, warum ich diese Nutze).
    Ich kam auf folgende 5 Stück:
    1. User = Name, Guthaben werden hier verwaltet
    2. Order = Gesamtpreis, Datum einer Bestellung
    3. Order_Details = Anzahl einer bestimmten Getränkesorte
    4. Beverage = Getränke, die Möglich sind/waren
    5. Prices = Um immer den aktuellen Preis zu berechnen (Preisveränderungen)
    Danach setze ich mich mit der Kardinalität der Entitys auseinander, diese kann man sich im Anhang anschauen.
    Aber noch mal kurz schriftlich:
    • 1 User kann n Bestellungen besitzen,
    • 1 Bestellung enthält n Details (Um auch verschiedene Getränkesorten in einer Buchung zu berücksichtigen),
    • 1 Order_Details besitzt n Getränke (Auch hier wieder: Verschiedene Getränkesorten) und
    • 1 Getränk besitzt n Preise (Preisveränderungen bei Buchung berücksichtigen).
    Anschließend habe ich mich damit befasst, welche Attribute die Entities haben müssen. Diese sieht man auch in der Beziehungsübersicht (siehe Anhang) ein.

    3. Mein Problem / Meine Frage

    Meine Frage bezieht sich nun darauf, ob mein Lösungsansatz korrekt ist, was falsch ist. Ganz besonders im Hinblick auf die Tatsache, dass ich denke, dass die Schlüsselvergaben falsch sind.
    Des Weiteren eine Frage: Ist es richtig, wenn ich davon ausgehe, dass der Primärschlüssel UserID aus der Tabelle Users, dann der Fremdschlüssel für Orders ist? Sinngemäß bei den anderen Primärschlüsseln und Beziehungen?

    4. Weitergehendes Problem

    Wie könnte ich dann beispielsweise noch erfassen, dass es Plus-Einzahlungen existieren? Also in meinem Beispiel, werden nur Buchungen genommen und jedes mal in der Tabelle Users wird unter Total (Guthaben) dann der aktuelle Minusbetrag aktualisiert. Wenn nun der Benutzer jedoch seine Rechnung begleicht, müsste ich ja in die Tabelle gehen und den Wert manuell ändern und hätte dann noch nicht mal ein Nachweis bzw. eine Buchung, wo ich ablesen kann: Am 01.01.2018 wurde der Betrag eingezahlt.
    Hintergrundinfo: Ich dachte daran, dass wenn ich die Zahlung erhalten habe, öffne ich durch ein passwortgeschütztes Fenster die Möglichkeit den Benutzer auszuwählen und den einbezahlten Betrag zu verbuchen. Nur sollte dies in der Datenbank auch verwirklicht werden.


    Nun habe ich sehr viel darüber geschrieben, um mein Problem, mein Vorhaben gut darzustellen, um hier Hilfe bei der Umsetzung zu erhalten!

    Ich würde mich darüber sehr freuen!
    Bilder
    • BeverageShop.PNG

      39,99 kB, 1.335×858, 55 mal angesehen
    Deine Schlüsseldefinitionen sind tatsächlich teilweise falsch.

    Tabelle Order_Details: Hier benötigst du erst einmal einen eigenen Primary Key, dann kommt auch Access nicht auf die dumme Idee, den PK auf den Foreign Key BeverageId zu setzen
    Tabelle Beverages: Hier muss das Feld BerverageId PK sein, PriceId ist hier der Foreign Key
    Tabelle Prices: PK muss hier PriceId sein

    Als einfache Faustformel: Ein Foreign Key (oder Fremdschlüssel) sollte immer der Primary Key (oder Primärschlüssel) der verbundenen Tabelle sein. Muss nicht immer zutreffen, aber die Eindeutigkeit der Verbindung muss gewährleistet sein.

    Ganz wichtig: Niemals einen Datentyp als Feldnamen verwenden!

    Ein weiterer Tipp: Versehe den Feldnamen mit einem Präfix, der die Zugehörigkeit zur Tabelle anzeigt. Macht es später bei der Erstellung von SQL-Statements einfacher, weil es normalerweise keine Aliasse mehr bedarf.

    Anbei ein Entwurf deines Datenmodells in etwa nach den Kriterien, mit denen ich Datenmodelle entwerfe. Keine Garantie auf Vollständigkeit und Korrektheit. Erstellt für MS SQL Server.

    Zu deiner 4. Frage: entweder ein Feld mit dem Guthaben oder Debit des Users in der Tabelle anlegen oder schöner - weil nachvollziehbar - eine Tabelle anlegen, in der alle Einzahlungen und Abzüge der Benutzer aufgeführt werden. Die Summe über alle Posten eines Benutzers ist dann entweder sein Guthaben oder seine Schuld.

    Grüße
    Mikhal
    Bilder
    • beverages.JPG

      77,41 kB, 1.010×767, 51 mal angesehen
    Computer erleichtern die Arbeit -
    und die Welt ist eine Scheibe!
    Erstmal vielen Dank!
    Das hat mir auf jeden Fall viel weitergeholfen.
    Ich habe nun die Verbesserungsvorschläge übernommen und eine weitere Tabelle hinzugefügt.
    Nur frage ich mich, ob Access die Kardinalitäten bzw. referentielle Integrität richtig gesetzt hat. Ab Orders bis Prices finde ich das etwas komisch, oder war mein ERM auch falsch:
    • 1 User kann n Bestellungen besitzen,
    • 1 Bestellung enthält n Details (Um auch verschiedene Getränkesorten in einer Buchung zu berücksichtigen),
    • 1 Order_Details besitzt n Getränke (Auch hier wieder: Verschiedene Getränkesorten) und
    • 1 Getränk besitzt n Preise (Preisveränderungen bei Buchung berücksichtigen).
    Oder beispielsweise auch mit der neuen Tabelle:
    Ein 1 User hat doch n Account Buchungen und nicht andersherum?

    Also mit Users und Orders bin ich einverstanden und mit Orders und Order_details ebenfalls. Aber Order_details muss doch 1 zu n lauten und nicht andersherum, ebenfalls bei prices?
    Bilder
    • BeverageShop.PNG

      77,4 kB, 1.943×974, 49 mal angesehen
    Mir ist die Struktur nicht ganz klar, daher habe ich mal eine eigene skizziert (deutsche Tabellenbezeichner, um Missverständnisse möglichst zu vermeiden) und dabei Dinge wie Storno, MwSt-Sätze, Währungen, Rabatte, Sonderpreise etc. komplett weggelassen. Wenn ich keinen Denkfehler mache, müssten sich alle benötigten Informationen darin finden bzw. berechnen lassen.

    Quellcode

    1. Tabelle Guthaben
    2. ================
    3. ID INT PK
    4. UserID INT (FK auf Kunde)
    5. Amount Currency
    6. AmountDate DATETIME
    7. Tabelle Kunde
    8. =============
    9. ID INT PK
    10. Irgendwelche Infodaten
    11. Tabelle Bestellung
    12. ==================
    13. ID INT PK
    14. UserID INT (FK auf Kunde)
    15. OrderDate DATETIME
    16. Tabelle Positionen
    17. ==================
    18. ID INT PK
    19. OrderID INT (FK auf Bestellung)
    20. ProductID INT (FK auf Produkt)
    21. Quantity FLOAT (oder DECIMAL oder evtl. auch INT, kommt darauf an)
    22. Tabelle Produkt
    23. ===============
    24. ID INT PK
    25. Irgendwelche Infodaten
    26. Tabelle Preis
    27. =============
    28. ID INT PK
    29. ProductID INT (FK auf Produkt)
    30. Price Currency
    31. ValidSince DATETIME

    Ein Kunde lädt sein Guthaben auf, dabei wird ein Datensatz in der Tabelle Guthaben erzeugt. Dasselbe geschieht bei Buchung einer Bestellung, dabei wird die errechnete Gesamtsumme der Bestellung dann als Negativbetrag eingetragen. Das aktuelle Guthaben ergibt sich also aus der Summe aller Guthaben zum Benutzerkonto. So zieht sich das Ganze durch die komplette DB.
    10 Minuten Nachdenken ersparen oftmals 10 Stunden Fehlersuche.
    Aber Order_details muss doch 1 zu n lauten und nicht andersherum, ebenfalls bei prices?

    Zu einer Position gibt es zwar nur eine OrderId, aber du hast doch unter Umständen mehrere Positionen in einer Bestellung...
    Das gleiche gilt für die Preise...

    Grüße
    Mikhal
    Computer erleichtern die Arbeit -
    und die Welt ist eine Scheibe!
    Gut, dass mit dem Guthaben habe ich wohl verstanden.

    @DeddyH:

    Den einzigen Unterschied den ich feststellen konnte, ist nun die Beziehung zwischen Produkt und Preis.
    Ich habe ja nun als Fremdschlüssel in Beverage die Produkt ID von Prices.
    Wenn ich einen Preis ändere von einen Getränk, wird ein neuer Datensatz in Prices erstellt. Mit der Produkt ID den neuen ValidSince und dem neuen Preis. Die PriceID wird dann dem Produkt zugeschrieben. Oder doch lieber, wie es in deinem Beispiel beschrieben ist?
    Gut, auch dies habe ich nun berücksichtigt, also wäre das Ergebnis nun richtig von der Modellierung her?
    Wie könnte ich dies am Besten testen?

    Müsste ich jetzt die Stammdaten einarbeiten? Zum Beispiel:
    Welche User exisitieren, welche Getränke werden angeboten, zu welchem Preis.
    Bilder
    • BeverageShop.PNG

      52,91 kB, 1.902×944, 50 mal angesehen

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Asura“ ()

    Im Artikel ist der Fremdschlüssel auf den Preis überflüssig. An Deiner Stelle würde ich jetzt ein paar Testdaten eingeben und mir zur Vereinfachung eine Handvoll Views erstellen, z.B. das schon angesprochene aktuelle Guthaben.
    10 Minuten Nachdenken ersparen oftmals 10 Stunden Fehlersuche.
    Gut ich habe ein paar Testdaten gesetzt. Wie kann ich nun diese Views erstellen?
    Ich habe in der Tabelle orders auch zwei Bestellungen gesetzt.
    Mir ist das noch nicht so klar, wie meine Datenbank mithilfe des Programmes die einzelnen Werte richtig setzt. Beispielsweise bei einer Bestellung bei Orders ein neuer Eintrag und bei Order Details dann genau die OrderID von der Tabelle Orders.

    Wie könnte so ein SQL Befehl aussehen in dem Fall?

    Ich habe auch mal die Access Datenbank als *.MDB in einer Zip hochgeladen, um die Testdaten zu zeigen. (Bezüglich Tabelle Users: Die ID Vergabe - also auch das Auslassen einzelner Zahlen - ist kein Fehler)
    Dateien
    Ohne jetzt in deine Access-Datenbank reingeschaut zu haben: PKs müssen immer einen gültigen Wert haben. Die sogenannten Datenbank-Constraints sorgen schon dafür, dass die Werte eindeutig sind. In Access setze ich bei PK immer Autowert ein, da wird immer eine korrekte ID generiert, ohne dass ich mich darum kümmern muss.

    Deine Fragen lassen darauf schließen, dass du bis jetzt noch nie mit Datenbanken in Delphi zu tun hattest. Da bieten sich zunächst mal die Tutorien hier auf der Seite an sowie das kostenlose Einführungsbuch hier auf der Seite an.


    Datenbank-Tutorien
    Einführungsbuch Delphi Starter

    Grüße
    Mikhal
    Computer erleichtern die Arbeit -
    und die Welt ist eine Scheibe!
    Die Tutorials sind schön und gut, aber dabei handelt es sich eher um die Verbindung von Delphi und der ADO Komponente, dass habe ich ja bereits mir bei gebracht. Mir geht es da viel mehr um so Dinge wie INNER JOIN (Ja, ich habe da ein kleines Unterthema dazu im Tutorial gesehen) . Also viel mehr um SQL, also wie ich mit Delphi auf die Datenbank Zugreife, SQL Codes Abfrage und diese verarbeite, habe ich bereits.

    Zu meiner Frage oben, korrigieren wenn ich falsch Liege:
    Wenn ich nun in meinem Programm bin, der Kunde hat sich ausgewählt, somit ist dem Programm die UserID bereits schon mal klar.
    Danach lädt das Programm die Getränke die bei der Datenbank Aktiv ist, eventuell mit der ID. Der Benutzer wählt sich dann Getränke aus. Nach der Kaufbestätigung wird an die Datenbank, ein Eintrag bei Orders erstellt mit Datum, daraufhin kann man die OrderID bestimmen und mit der OrderID wird bei Order_Details die Eintragungen der verschiedenen gekauften Getränke mit deren Ids und Menge erstellt?
    Danach wird einfach ein Eintrag bei "Account" mit der Minusbuchung, Datum und Betrag getätigt.

    Würde so der Ablauf dann über SQL laufen?
    Ich hätte mal eine Frage, wie könnte ich für die Tabelle Accounts sämtliche Einträge erfassen, um diese zu berechnen?

    So sieht meine Funktion aus:

    Delphi-Code

    1. function CheckAccountAmount(UserID: Integer): String;
    2. var SL: TStringlist;
    3. begin
    4. F_Admin2_2.ADOQuery.SQL.Text := 'SELECT acc_amount FROM acc_account WHERE :usr_ID = acc_usr_ID';
    5. F_Admin2_2.ADOQuery.Parameters.ParamByName('usr_ID').Value:= UserID;
    6. F_Admin2_2.ADOQuery.Open;
    7. { TODO : Alle Einträge addieren }
    8. result := FloattoStrF(F_Admin2_2.ADOQuery.Fields[0].AsFloat,ffCurrency,15,2); // Das Zeigt mir ja nur den ersten Wert an
    9. end;


    Ich habe das versucht, mit einem Stringlist die Ergebnisse zu Laden, jedoch ohne Erfolg.

    Delphi-Code

    1. F_Admin2_2.ADOQuery.First;
    2. while not F_Admin2_2.ADOQuery.Eof do
    3. begin
    4. Stringlist.Add(F_Admin2_2.ADOQuery.FieldByName('acc_amount').AsString);
    5. F_Admin2_2.ADOQuery.Next;
    6. end;
    Inwiefern hattest du keinen Erfolg dabei, die Ergebnisse in die Stringlist zu laden?

    Aber davon ab, ist es auch nicht der richtige Weg, sich die Einzelwerte aus der Datenbank zu holen und dann selbst aufzusummieren. dafür bietet SQL Aggregatfunktionen an.
    In deinem Fall etwa sowas:

    SQL-Abfrage

    1. SELECT SUM(acc_amount) as total amount FROM acc_account WHERE :usr_ID = acc_usr_ID';
    Gruß Thomas

    Admin werden ist nicht schwer, sein ......
    Die drei Feinde des Programmierers: Sonne, Frischluft und dieses unerträgliche Gebrüll der Vögel.
    Schreib den Alias aber besser zusammen, sonst musst Du ihn maskieren ;)

    SQL-Abfrage

    1. SELECT SUM(acc_amount) as TotalAmount FROM acc_account WHERE :usrID = acc_usr_ID';

    Den Parameter usrID fütterst Du dann mit der ID des aktuellen Benutzers.
    10 Minuten Nachdenken ersparen oftmals 10 Stunden Fehlersuche.
    Du MUSST hier keinen Alias verwenden, aber wenn Du das tust, kannst Du bequem über FieldByName auf das Feld zugreifen. Ansonsten wird automatisch ein Feldname vom DBMS vergeben, der nicht sonderlich hübsch aussieht und sich von DBMS zu DBMS oder gar von Version zu Version unterscheiden kann. Die Möglichkeit über die Feldliste (Dataset.Fields[0]) ist davon natürlich nicht betroffen.
    10 Minuten Nachdenken ersparen oftmals 10 Stunden Fehlersuche.