Beim Schreiben von Anwendungen, mit denen Rechnungen erstellt werden sollen, stellt sich immer wieder die Frage nach dem korrekten Datenmodell. Davon ausgehend, dass es nicht das perfekte Datenmodell für alle Anwendungsfälle gibt, wollen wir in diesem Beitrag einmal unterschiedliche Ansätze betrachten und diskutieren. Diese haben eines gemein: Die Anwendung mit diesem Datenmodell soll die Möglichkeit bieten, sowohl Bestellungen zu erfassen als auch Rechnungen und Lieferscheine zu erstellen. Wie welche Daten gespeichert werden und welche Möglichkeiten es gibt, schauen wir uns nun an.
Bestellungen erfassen
Über bestimmte grundlegende Anforderungen herrscht vermutlich eine einstimmige Meinung: Wenn man Bestellungen erfassen möchte und die Regeln der Normalisierung von Datenmodellen befolgen will, benötigt man mindestens drei Tabellen. Die erste Tabelle enthält die Kundendaten mitsamt einer Kunden-ID und den Daten wie Firma, Anrede, Vorname, Nachname, Straße, PLZ, Ort, Land, Telefon, E-Mail und so weiter.
In den meisten Fällen liegen die für das Speichern der Anschrift vorgesehenen Felder in zweifacher Ausführung vor, nämlich für die Lieferadresse und die Rechnungsadresse, denn diese können sich unterscheiden. Normalerweise finden wir hier für Liefer- und Rechnungsadresse meist auch noch ein Fremdschlüsselfeld zur Auswahl eines Eintrags einer Anrede-Tabelle – die zählen wir hier aber nicht mit.
Die zweite Tabelle enthält die grundlegenden Daten einer Bestellung wie die Bestell-ID, das Bestelldatum und ein Fremdschlüsselfeld, mit dem der Kunde ausgewählt werden kann, der diese Bestellung getätigt hat. Auf diese Weise können wir zu jedem Kunden keine, eine oder mehrere Bestellungen anlegen.
Die dritte Tabelle enthält die Positionen der Bestellung. Diese umfassen eine Bezeichnung der Position, den Einzelpreis, die Menge, die Mehrwertsteuer und gegebenenfalls noch einen Rabatt. Außerdem benötigen wir hier ein Fremdschlüsselfeld, das mit der Bestellungen-Tabelle verknüpft ist, damit wir die einzelnen Bestellpositionen der Bestellung zuordnen können.
Normalerweise gibt es mindestens noch eine vierte Tabelle, aus der die grundlegenden Daten für die Bestellpositionen stammen. Diese nennen wir üblicherweise Produkt- oder Artikeltabelle. Sie enthält die Produkt- oder Artikelbezeichnung, den Einzelpreis sowie den Mehrwertsteuersatz und weitere Detailinformationen, die für andere Vorgänge wie Warenwirtschaft erforderlich sind – wie die Verwaltung von Beständen et cetera. Wir wollen im Folgenden von Produkten statt von Artikeln sprechen.
Um die Bestellposition mit dem jeweiligen Produkt zu verknüpfen, enthält die Tabelle der Bestellpositionen meist ein Fremdschlüsselfeld zu der Tabelle der Produkte. Auf diese Weise kann man über die Benutzeroberfläche leicht das zu einer Bestellposition hinzuzufügende Produkt auswählen.
Der erste Entwurf sieht im Datenbankfenster von Access wie in Bild 1 aus.
Bild 1: Erster Entwurf mit vielen Schwachstellen (V1)
Produktdaten mit den Bestellpositionen speichern?
Hier gibt es direkt die erste spannende Frage: Wenn ich eine Tabelle mit Bestellpositionen verwende und eine mit den Produkten und in der Tabelle der Bestellpositionen ein Feld ist, mit dem ich das mit dieser Position bestellte Produkt selektieren kann, benötige ich dann überhaupt die ganzen bereits erwähnten Felder? Wenn bereits in der Produkte-Tabelle die Bezeichnung, der Einzelpreis und der Mehrwertsteuersatz gespeichert sind, reicht es dann nicht aus, wenn ich mit einem Fremdschlüsselfeld aus der Bestellpositionen-Tabelle auf den Eintrag aus der Produkte-Tabelle verweise? Und in der Bestellpositionen-Tabelle nur die Felder Menge und Rabatt fülle?
Unter dem Gesichtspunkt, möglichst wenige Daten zu speichern, scheint diese Idee verlockend. Es gibt jedoch ein entscheidendes Problem: Einzelpreise und Mehrwertsteuersätze ändern sich von Zeit zu Zeit. Und es kann sogar vorkommen, dass wir den Namen eines Produktes ändern wollen (auch wenn es sich dann anbieten würde, einen neuen Produktdatensatz zu diesem Zweck anzulegen). Wenn es Bestellungen für ein Produkt gibt, und der Einzelpreis und der Mehrwertsteuersatz nur in der Produkttabelle vorliegen, dann können wir später nicht mehr nachvollziehen, zu welchem Preis und zu welchem Mehrwertsteuersatz der Kunde zuvor bestellt hat. Stattdessen werden wir alle Bestellpositionen immer mit den aktuellen Einzelpreisen und Mehrwertsteuersätzen vorfinden.
Wir können so also nach der Änderung auch nur eines Einzelpreises oder Mehrwertsteuersatzes für ein Produkt in einer Bestellposition einer Bestellung nicht mehr die tatsächlichen Umsätze nachvollziehen.
Den aktuellen Stand des Datenmodells finden Sie in der Beispieldatenbank GrundlagenDatenmodellRechnungsverwaltung_V1.accdb.
Änderungen von Einzelpreis und Mehrwertsteuersatz behandeln
Was tun? Damit die Änderungen am Produktnamen und an den Einzelpreisen und Mehrwertsteuersätzen eines einzelnen Produkts in der Produkttabelle keine Auswirkungen mehr auf die zum Zeitpunkt der Bestellung gültigen Werte haben, müssen wir diese Daten direkt in der Tabelle mit den Bestellpositionen speichern.
Dazu sehen wir einfach drei Felder mit den gleichen Namen wie in der Produkte-Tabelle vor, also beispielsweise Produkt, Einzelpreis und Mehrwertsteuersatz (siehe Bild 2).
Bild 2: Diese Felder sollen dauerhaft mit der Bestellposition gespeichert werden (V2).
Fügen wir in der Benutzeroberfläche einer Bestellposition ein Produkt beispielsweise durch Auswahl eines Nachschlagefeldes hinzu, kopieren wir direkt die Werte der Felder Produkt, Einzelpreis und Mehrwertsteuersatz in die Tabelle der Bestellpositionen.
Diese und die folgende Änderung haben wir in der Beispieldatenbank GrundlagenDatenmodellRechnungsverwaltung_V2.accdb hinterlegt.
Auf das Thema Mehrwertsteuersätze gehen wir gegen Ende des Beitrags im Detail ein.
Änderungen von Adressdaten behandeln
Das sind allerdings nicht die einzigen Daten, die Änderungen unterliegen. Auch die Adressen von Kunden ändern sich von Zeit zu Zeit. Das ist auch kein Problem: Wir ändern einfach die Adressdaten in der Kundentabelle und haben jederzeit den aktuellen Stand, um neue Bestellungen korrekt erfassen zu können.
Die Sache hat allerdings einen Haken: Im aktuellen Stand unseres Datenmodells ist die Kundentabelle noch über ein Fremdschlüsselfeld in der Tabelle tblBestellungen mit der Bestellung verknüpft. Wenn wir die Daten in der Kunden-Tabelle ändern, dann ändern wir automatisch auch die Adressdaten des Kunden einer Bestellung.
Das bringt wieder Nachteile mit sich, wenn wir einmal von der Region abhängige Auswertungen über unsere Bestelldaten durchführen möchten. Zieht ein Kunde von Duisburg nach München, ändert sich auch die Auswertung, denn nun hat Bayern ein paar Bestellungen und Umsätze mehr auf der Habenseite.
Auch das wollen wir tunlichst vermeiden. Wie können wir das tun? Wir wählen einen ähnlichen Ansatz wie bei den Bestellpositionen und Produkten: Wir legen in der Bestellungen-Tabelle weitere Felder an, in denen wir die Kundenadressen zum Zeitpunkt der Bestellung speichern. Das Fremdschlüsselfeld zum Kunden in der Tabelle tblBestellungen sollten wir jedoch beibehalten, denn wir wollen ja gegebenenfalls auch einmal einen Überblick über die Bestellungen eines Kunden und die darin enthaltenen Positionen haben.
Wie in Bild 3 zu sehen, haben wir eine Reihe von Feldern auch in der Tabelle der Bestellungen angelegt. In der Praxis gelingt das übrigens ganz einfach – Sie müssen einfach nur die zu kopierenden Felder im Entwurf der Quelltabelle kopieren und dann in der Zieltabelle einfügen.
Bild 3: Diese Felder sollen dauerhaft mit der Bestellung gespeichert werden (V2).
Auch hier müssen wir in der Benutzeroberfläche beim Auswählen des Kunden zu einer Bestellung Code auslösen, der dafür sorgt, dass die Felder der Bestellungen-Tabelle mit den Daten aus der Kundentabelle gefüllt werden.
Theoretisch könnten wir auch noch die beiden Felder EMail und Telefon zur Tabelle tblBestellungen hinzufügen. Dies hätte den Sinn, dass wir später noch reproduzieren könnten, wohin wir E-Mails beispielsweise mit Bestellbestätigungen oder Rechnungen gesendet haben. Allerdings gehen wir davon aus, dass die E-Mails auch archiviert werden und im späteren Verlauf gegebenenfalls eingesehen werden können.
Adressen separat speichern
In vielen Datenbanken sehen wir, dass Adressen separat in eigenen Tabellen gespeichert werden. Das könnte man angehen, indem man eine Tabelle für Adressen anlegt und dort Adressen speichert, die man dann über zwei Fremdschlüsselfelder namens LieferadresseID und RechnungsadresseID referenziert.
Danach würde dieser Teil des Datenmodells wie in Bild 4 aussehen. Wir haben hier im gleichen Zuge noch eine Frage gelöst, nämlich die, ob wir E-Mail und Telefon nur einmal je Kunde benötigen oder ob wir für die Liefer- und die Rechnungsadresse jeweils eigene Felder namens EMail und Telefon vorsehen sollen.
Bild 4: Ausgliederung der Liefer- und Rechnungsadressen in eine weitere Tabelle (V3).
Wir haben die beiden Felder einfach in die Tabelle tblAdressen verschoben, wo man diese Daten nun für alle Adressen hinterlegen kann.
Wenn wir entschieden hätten, dass wir nur eine E-Mail-Adresse und eine Telefonnummer benötigen, hätten wir diese Felder in der Tabelle tblKunden belassen können.
Es macht jedoch mehr Sinn, hier auf Flexibilität zu setzen, denn es kann beispielsweise passieren, dass ein Kunde ein digitales Produkt ordert, zu dem Daten per E-Mail versendet werden sollen und der für die Rechnungen eine eigene Rechnungs-E-Mail-Adresse verwendet.
Beliebige Anzahl an Adressen
Mit dieser Version des Datenmodells sind wir allerdings auf je zwei Adressen pro Kunde beschränkt. Wer zum Beispiel schon einmal bei Amazon bestellt hat, weiß, dass man dort beliebig viele Lieferadressen hinterlegen kann. Wir wollen uns einmal ansehen, wie wir unser Datenmodell verändern müssten, um dies zu implementieren.
Voraussetzung soll jedoch sein, dass wir eine der Adressen als Standardlieferadresse festlegen können und eine als Standardrechnungsadresse. Dazu gibt es zwei Möglichkeiten:
- Wir belassen die Struktur weitgehend so, wie sie ist, und erlauben damit die Zuordnung der aktuellen Standardadresse für die Lieferung und die Rechnungen über die beiden Fremdschlüsselfelder LieferadresseID und RechnungsadresseID der Tabelle tblKunden. Damit wir mehr als diese beiden Adressen für einen Kunden anlegen und diese auch zuordnen können wollen, müssen wir der Tabelle tblAdressen ein Fremdschlüsselfeld hinzufügen, mit dem wir den Kunden festlegen können, zu dem diese Adresse gehört. Erst dann können wir in der Benutzeroberfläche für diesen Kunden die passenden Adressen zur Auswahl für die beiden Felder LieferadresseID und RechnungsadresseID anbieten (siehe Bild 5).
- Die zweite Möglichkeit ist, die Zuweisung der Adressen gar nicht mehr über Fremdschlüsselfelder in der Kundentabelle zu erledigen. Stattdessen entfernen wir die Fremdschlüsselfelder wie in Bild 6 und stellen die Verknüpfung nur noch über das Fremdschüsselfeld KundeID der Adresstabelle her. Das heißt, wir können keine, eine oder mehrere Tabellen zu jedem Kunden in der Adresstabelle speichern. Allerdings wissen wir bisher noch nicht, welche wir dann als Liefer- und Rechnungsadresse verwenden sollen. Dies regeln wir über zwei Ja/Nein-Felder namens IstStandardLieferadresse und IstStandardRechnungsadresse. Damit können wir zum Beispiel festlegen, das eine einzige Adresse als Liefer- und Rechnungsadresse fungiert. Der Nachteil ist allerdings, dass wir in Access per Code die Integrität der Daten sicherstellen müssen – also beispielsweise, dass für jeden Kunden immer mindestens eine Adresse gespeichert ist und das jeweils eine Adresse als Standardlieferadresse und eine als Standardrechnungsadresse markiert ist.
Bild 5: Das Fremdschlüsselfeld KundeID ermöglicht die Zuordnung der Adressdatensätze zu einem Kundendatensatz (V4).
Bild 6: Wir können die Beziehung auch komplett umdrehen. Dann müssen wir angeben, welche Adresse die Standardliefer- und Standardrechnungsadresse ist (V5).
Da scheint es zumindest aus Sicht der Datenintegrität zuverlässiger, Variante V4 zu wählen.
Eigene Adresstabellen in Bezug auf Bestellungen
Wenn wir wie in V3, V4 oder V5 eine eigene Tabelle zum Speichern der Kundenadressen verwenden, können wir nach wie vor wie in V2 die bei Bestellung gültige Liefer- und Rechnungsadresse in die Felder der Tabelle tblBestellungen übernehmen.
[
Wir könnten aber auch hier die Adressen in einer oder mehreren Tabellen speichern. Hier haben wir einen etwas einfacheren Fall, als dass wir zu jeder Bestellung nur maximal eine Liefer- und eine Rechnungsadresse speichern müssen. Wir könnten also die Felder der Tabelle tblBestellungen wie in Bild 7 in weiteren Tabellen organisieren (siehe GrundlagenDatenmodellRechnungsverwaltung_V6.accdb).
Bild 7: Auch beiden Bestellungen könnte man die Adressen wie hier in eigenen Tabellen speichern – hier einmal mit jeweils einer Tabelle für Liefer- und Rechnungsadressen (V6).
Hier haben wir einmal einen anderen Ansatz gewählt als in den vorherigen Beispielen, wo wir dem Kunden die Adressen auf verschiedene Arten zugeordnet haben.
In diesem Fall verwenden wir nicht eine Tabelle für alle Adressen, sondern wir haben einmal zwei Tabellen genutzt – eine für die Liefer- und eine für die Rechnungsadressen. Diese haben wir tblBestellungenLieferadressen und tblBestellungenRechnungsadressen genannt. Beide sind jeweils über das Fremdschlüsselfeld BestellungID mit der jeweiligen Bestellung aus der Tabelle tblBestellungen verknüpft.
Wir könnten auch hier mit nur einer Adresstabelle arbeiten. Dazu müssten wir dann wieder zwei Fremdschlüsselfelder zur Tabelle tblBestellungen hinzufügen, mit denen wir jeweils die Lieferadresse und die Rechnungsadresse für die jeweilige Bestellung hinterlegen können.
Im Gegensatz zur Version V6 können wir hier, wenn die Liefer- und die Rechnungsadresse gleich sind, einen Datensatz einsparen.
In V6 benötigen wir immer zwei Datensätze, je einen in der Tabelle tblBestellungenLieferadressen und einen in der Tabelle tblBestellungenRechnungsadressen. Die Version mit nur einer Adresstabelle ist in Bild 8 dargestellt (siehe GrundlagenDatenmodellRechnungsverwaltung_V7.accdb).
Bild 8: Man könnte auch nur eine Tabelle mit Bestelladressen verwenden, die dann mit zwei Fremdschlüsselfeldern der Tabelle tblBestellungen verknüpft wird (V7).
Kundennummern
Einen Punkt haben wir noch nicht berücksichtigt: die individuelle Kundennummer. Aktuell haben wir in der Tabelle tblKunden nur ein Primärschlüsselfeld namens KundeID. Dieses wird von der Autowertfunktion mit aufsteigenden Zahlen gefüllt. Gegebenenfalls kommen die Bestellungen aber aus einem Shopsystem, das einen eigenen Kundennummernkreis pflegt, oder man möchte generell ein anderes Kundennummern-Format nutzen als einfach durchlaufende Zahlen zu verwenden.
In diesem Fall fügt man einfach ein weiteres Feld beispielsweise namens Kundennummer hinzu und legt den entsprechenden Datentyp fest – bei Zahlen den Datentyp Zahl und wenn man Kombinationen aus Buchstaben und Zahlen verwenden möchte den Datentyp Text. Wichtig ist, dass wir dieses Feld mit einem eindeutigen Index versehen, damit keine Kundennummer doppelt vergeben wird. Das Feld Kundennummer haben wir ab V8 hinzufügt.
Bestellnummern
Gleiches gilt für die Nummern von Bestellungen. Auch hier kann es sein, dass Bestellungen von einem oder mehreren Onlineshops importiert werden und/oder manuell angelegt werden. Dann möchte man gegebenenfalls auch diese Bestellnummern pflegen, wozu sich ein Feld namens Bestellnummer zusätzlich zum Primärschlüsselfeld BestellungID anbietet.
Auch hier sollten wir einen eindeutigen Index festlegen, um zu verhindern, dass Bestellnummern doppelt angelegt werden.
Schließlich können wir ein solches Feld auch noch für die Produktnummern in der Tabelle tblProdukte anlegen.
Rechnungen
Als ob das nicht schon genug Varianten wären (und es gibt sicher noch einige weitere), kommen nun noch die Rechnungen ins Spiel.
Eigentlich könnten wir auf Basis der bisher angelegten Daten bereits Rechnungen erstellen. Die Daten liegen in allen Versionen in den enthaltenen Tabellen vor. Der einzige Unterschied besteht in der Art der Verteilung auf die jeweiligen Tabellen und darin, dass – zumindest in den ersten Versionen – Änderungen an den Basisdaten, wie zum Beispiel den Adressen oder Produkten, zu Unterschieden bei der gedruckten oder als PDF erstellten Rechnung führen können.
Für die Erstellung von Rechnungen benötigen wir zusätzlich eigentlich nur noch ein Rechnungsdatum und eine Rechnungsnummer. Können wir diese nicht einfach noch in die Bestellungen-Tabelle einbauen und dann daraus unsere Rechnung generieren?
Technisch ist das möglich. Aber es gibt einige Gründe, warum man Bestellungen und Rechnungen in eigenen Tabellen speichern sollte.
- Es sind zwei völlig unterschiedliche Vorgänge, die zwar miteinander zu tun haben, aber die doch nacheinander ablaufen. Das heißt also, dass wir unter Umständen zwar eine Bestellung erhalten, diese aber bereits vor Rechnungsstellung storniert wird – sei es, dass ein Produkt nicht lieferbar ist oder der Kunde die Bestellung storniert.
- Es kann sein, dass nicht alle Positionen einer Bestellung in der gleichen Rechnung abgerechnet werden können – beispielsweise, weil ein Produkt aktuell nicht lieferbar ist.
- Andererseits kann es auch sein, dass ein Unternehmen nicht für jede Bestellung eine Rechnung ausstellt, sondern beispielsweise immer am Ende eines Monats abrechnet.
Also schauen wir uns an, wie wir das Datenmodell auch noch um die Tabellen zum Verwalten der Rechnungsdaten ergänzen.
Rechnungstabellen
Grundsätzlich können wir die Entwürfe der Tabellen für die Bestellungen und Bestellpositionen genau auf die Tabellen für die Rechnungen übertragen. Dazu fügen wir dem Datenmodell zunächst eine Tabelle namens tblRechnungen hinzu. Diese enthält im Wesentlichen die Felder, die wir bereits von der Tabelle tblBestellungen her kennen – nur dass wir das Primärschlüsselfeld RechnungID nennen und ein Feld namens Rechnungsdatum hinzugefügt haben. Statt des Feldes Bestellnummer verwenden wir Rechnungsnummer.
Dieses Feld versehen wir mit einen eindeutigen Index. Die Anwendung muss sicherstellen, dass die Rechnungsnummern durchlaufend sind. Das Feld Bestelldatum übernehmen wir ebenfalls wie die Felder KundeID, LieferadresseID und RechnungsadresseID. Allerdings verweisen die beiden Fremdschlüsselfelder LieferadresseID und RechnungsadresseID auf eine weitere neue Tabelle namens tblRechnungsadressen. Warum schleifen wir die Lieferadresse mit? Weil wir gegebenenfalls die Lieferadresse in der Rechnung vermerken wollen.
Ende des frei verfügbaren Teil. Wenn Du mehr lesen möchtest, hole Dir ...
den kompletten Artikel im PDF-Format mit Beispieldatenbank
diesen und alle anderen Artikel mit dem Jahresabo