Wie man ein Buchhaltungssystem mit SQLite aufbaut

Kratzen Sie Ihren eigenen Juckreiz.

Dies ist der Rat, den ich immer gebe, wenn mich jemand fragt, wie man programmieren lernt. Praktisch bedeutet das, dass du Dinge lösen oder Projekte auswählen musst, die für dich relevant sind – entweder in deiner Arbeit oder in deinem Privatleben.

Das gedankenlose Anschauen von Youtube-Tutorials, das Lesen von Programmierbüchern, das Kopieren von Code aus Reddit-Beiträgen usw. bringt dich nicht weiter, wenn du anfängst, das Programmieren zu lernen.

In diesem Beitrag zeige ich dir, wie du eine einfache Buchhaltungsdatenbank mit SQLite erstellst.

So, warum eine Buchhaltungsdatenbank erstellen? Warum kopiert man nicht einfach öffentliche Daten, schiebt sie in SQLite und übt von dort aus?

Der Grund ist, dass die Erstellung einer Buchhaltungsdatenbank weit genug fortgeschritten ist, um alle Aspekte von Datenbanken und SQL abzudecken – von Abfragen über Joins bis hin zu Views und CTEs.

Da ich aus der Buchhaltung komme, denke ich, dass dies das beste Projekt ist, um SQL zu lernen. Schließlich ist das Programmieren ein Werkzeug, um Probleme zu lösen. Daher kann man genauso gut ein schwieriges Problem „lösen“, um SQL vollständig zu lernen.

Die Inspiration, ein Buchhaltungssystem mit SQL zu erstellen, bekam ich durch die Beobachtung, wie Xero funktioniert. Für diejenigen, die damit nicht vertraut sind: Xero ist eine Cloud-Buchhaltungssoftware, die ursprünglich aus Neuseeland stammt. Mittlerweile gibt es sie auch in Australien, den USA, Kanada und dem Vereinigten Königreich.

Das Gute an Xero ist, dass es eine schöne, übersichtliche Benutzeroberfläche und viele Apps zur Auswahl hat, mit denen man seine Funktionen erweitern kann.

Haftungsausschluss: Ich bin weder Ingenieur noch Entwickler bei Xero und diese Beobachtungen entsprechen möglicherweise nicht genau der Funktionsweise des Systems, da es ständig aktualisiert wird. Sicherlich ist das hier vorgestellte SQL nicht das exakte SQL-Design, das Xero verwendet, da ihr System skaliert werden muss. Aber das ist ein sehr interessantes Projekt, also los geht’s!

Buchhaltung 101

Bevor Sie sich zu sehr aufregen, lassen Sie uns zuerst einen Crashkurs über Buchhaltung machen.

Die grundlegende Gleichung der Buchhaltung lautet

Vermögen = Verbindlichkeiten + Eigenkapital

Diese Gleichung besteht im Wesentlichen aus drei Teilen

• Vermögen sind alle Ressourcen des Unternehmens.
• Verbindlichkeiten sind das, was das Unternehmen schuldet.
• und Eigenkapital, die Summe aller Investitionen, Entnahmen, Gewinne oder Verluste der Eigentümer.

Die rechte Seite beschreibt, wie das Vermögen finanziert wurde – entweder durch Verbindlichkeiten oder durch Eigenkapital.

Wir können die obige Gleichung erweitern, um das Eigenkapital aufzuschlüsseln

Vermögen = Verbindlichkeiten + Anfangseigenkapital + Erträge – Aufwendungen

Diese 5 Konten – Vermögen, Verbindlichkeiten, Eigenkapital, Erträge, & Aufwendungen – sind die Kontenarten, die Sie normalerweise in einem Buchhaltungssystem sehen.

Dann gibt es das Konzept von Soll und Haben. Ich könnte diese beiden Begriffe noch ausführlich erörtern, aber für diesen Beitrag müssen Sie nur wissen, dass bei jeder Transaktion:

Soll = Haben

Diese beiden Gleichungen bestimmen im Allgemeinen, was im gesamten Buchhaltungszyklus geschieht. Diese beiden Gleichungen werden auch als Leitfaden für die Erstellung unserer eigenen Buchhaltungsdatenbank dienen.

Da ich aus dem Bereich der Buchhaltung komme, denke ich, dass dies das beste Projekt ist, um SQL zu lernen. Schließlich ist das Programmieren ein Werkzeug, um Probleme zu lösen. Daher kann man genauso gut ein schwieriges Problem „lösen“, um SQL vollständig zu erlernen.

Xeros Implementierung der Buchhaltung

Das Wichtigste ist, dass Xero so konzipiert ist, dass es für Geschäftsinhaber (nicht für Buchhalter) bei den täglichen Geschäftsabläufen von Nutzen ist.

Als solches ist es auf Transaktionszyklen und interne Kontrolle ausgelegt.

Transaktionszyklen

Die grundlegenden Transaktionszyklen sind die folgenden

• Verkaufszyklus
• Einkaufszyklus
• Kassenzyklus

Xero implementiert diese Zyklen wie folgt

Verkaufszyklus
Umsätze werden in Xero mit Hilfe von Rechnungen eingegeben. Stellen Sie sich vor, das Unternehmen stellt echte Papierrechnungen für Verkäufe (Barverkäufe oder auf Rechnung) aus. Genau das will Xero nachbilden.

Die Rechnungen können direkt aus der Software heraus gedruckt werden und werden automatisch in aufsteigender Reihenfolge nummeriert.

Unter der Haube erhöhen Rechnungen das Verkaufskonto und das Forderungskonto.

Einkaufszyklus
Rechnungen werden in Xero mit Rechnungen erfasst. Auch hier stellen Sie sich vor, dass das Unternehmen tatsächliche Rechnungen für Einkäufe ausstellt (Barkäufe oder auf Rechnung). Dies ist der übliche Fall für Versorgungsunternehmen und Inventar. Das ist auch das, was Xero abbilden will.

Die Rechnungen können direkt aus der Software heraus gedruckt werden und können als Ergänzung zu den Genehmigungsverfahren des Unternehmens verwendet werden.

Unter dem Strich erhöhen Rechnungen das Einkaufskonto und das Kreditoren-Konto (AP).

Kassenzyklus
Dies umfasst alle Transaktionen, die das Bargeld betreffen. Es gibt 4 Arten

• Rechnungszahlungen – Zahlungen der ausstehenden Rechnungen
• Rechnungszahlungen – Zahlungen der ausstehenden Rechnungen
• Geldeingänge – Bareingänge, die keine Rechnungszahlungen sind. Dabei kann es sich um Barverkäufe handeln, aber wenn Sie eine Rechnung ausstellen wollen, verwenden Sie die Funktion Rechnungen
• Ausgegebenes Geld – Barauszahlungen, die keine Rechnungszahlungen sind. Dies kann Barkäufe beinhalten, aber wenn Sie eine Rechnung ausstellen, verwenden Sie die Funktion Rechnungen.

Das ist der Teil über die Transaktionszyklen.

Interne Kontrolle

Für die interne Kontrolle müssen Sie das Konzept der Systemkonten verstehen.

Xero hat hier einen umfassenden Artikel zum Verständnis der Systemkonten. Aber für unsere Zwecke werden wir nur die folgenden Systemkonten besprechen

• Debitoren
• Kreditoren
• Bankkonten (verknüpft mit Bank Feeds)

Diese Konten können nicht in manuellen Journalen verwendet werden. Das bedeutet, dass Xero möchte, dass Sie Rechnungen, Rechnungen und Bargeldtransaktionen (Rechnungszahlungen, Rechnungszahlungen, erhaltenes Geld und ausgegebenes Geld) verwenden, um den Saldo dieser Konten zu unterstützen. Dies ist Xeros Umsetzung der internen Kontrolle, wenn Sie so wollen.

Natürlich können Sie die systemfremde Version der Konten AR, AP und Bank verwenden, indem Sie sie im Kontenplan (COA) anlegen. Sie können jedoch nicht die Kontenarten AR, AP und Bank dafür verwenden.

Das Wichtigste ist, dass Xero so konzipiert ist, dass es für Geschäftsinhaber (und nicht für Buchhalter) im Tagesgeschäft nützlich ist.

Caveat: We’re Not Going Down The Rabbit Hole

Die Entwicklung einer Buchhaltung ist wirklich komplex. Es würde mehrere Blogbeiträge erfordern, um dieses Thema zu behandeln. Daher werden wir der Einfachheit halber die folgenden Annahmen treffen (nicht genau so, wie Xero diese implementiert)

• Rechnungs- und Rechnungszahlungen
  Rechnungszahlungen können zwei oder mehr Rechnungen vollständig bezahlen. Das heißt, wir lassen keine Teilzahlungen zu. Dasselbe gilt für Zahlungen von Rechnungen.
• Inventar
  Wir werden hier keine Inventarartikel verwenden. Für Verkäufe oder Käufe werden wir direkt das Konto „Bestand“ verwenden, anstatt Bestandsartikel zu erstellen, die dem Konto „Bestand“ zugeordnet sind.

Das war’s mit unseren Annahmen. Nachdem wir unsere Datenbank entworfen haben, kann der Leser diese Annahmen aufheben und versuchen, Xero so weit wie möglich zu imitieren.

SQL-Grundlagen

Bevor wir nun unsere Version der Xero-Datenbankstruktur rekonstruieren, lassen Sie uns einen Crashkurs über Datenbanken machen.

Eine Datenbank ist eine Sammlung von Tabellen. Jede Tabelle besteht aus Datenzeilen, die Datensätze genannt werden. Die Spalten werden Felder genannt.

Das Programm, das mit einer Datenbank arbeitet, wird Datenbankmanagementsystem oder DBMS genannt. In einer einfachen Analogie ist DBMS ein Excel-Programm, die Datenbank eine Excel-Arbeitsmappe und die Tabelle ein Excel-Arbeitsblatt.

Es gibt zwei Hauptunterschiede zwischen einer Datenbank und einer Excel-Arbeitsmappe.

Die Datenpräsentation ist von der Datenspeicherung getrennt.

Das bedeutet, dass man Daten in einer Datenbank nicht bearbeiten kann, indem man direkt zu den Daten geht und sie bearbeitet. (Andere DBMS-Programme haben grafische Benutzeroberflächen, mit denen Sie direkt auf die Daten in einer Datenbank zugreifen und sie wie eine Tabellenkalkulation bearbeiten können. Aber unter der Haube gibt diese Aktion einen SQL-Befehl aus).

Tabellen sind in der Regel miteinander verknüpft, um eine Beziehung zu bilden.

Beziehungen können eins-zu-eins, eins-zu-viele oder viele-zu-viele sein.

Eins-zu-eins-Beziehung bedeutet, dass „eine Tabellenzeile nur mit einer Zeile in einer anderen Tabelle verbunden ist und umgekehrt“. Ein Beispiel wäre der Name des Mitarbeiters zur Steueridentifikationsnummer.

Diese Art von Beziehung ist normalerweise in einer einzigen Tabelle „Mitarbeiter“ enthalten, da es wirklich keinen Vorteil bringt, die Daten in zwei Tabellen aufzuteilen.

Eine-zu-viele-Beziehung bedeutet dagegen, dass eine Tabellenzeile nur mit einer oder mehreren Zeilen in einer anderen Tabelle verbunden ist, aber nicht umgekehrt. Ein Beispiel sind Rechnungen zu Rechnungszeilen. Eine Rechnung kann mehrere Zeilen haben, aber eine Rechnungszeile gehört nur zu einer bestimmten Rechnung.

Und wie Sie vielleicht schon erraten haben, bedeutet many-to-many „eine Tabellenzeile ist nur mit einer oder mehreren Zeilen in einer anderen Tabelle verbunden und umgekehrt“. Ein Beispiel wäre ein System, das Teilzahlungen implementiert.

Eine Rechnung kann teilweise durch verschiedene Zahlungsvorgänge bezahlt werden, und eine Zahlung kann verschiedene Rechnungen teilweise bezahlen.

Woher kennt eine Datenbank diese Beziehungen?
Es ist einfach. Durch die Verwendung von Primär- und Fremdschlüsseln.

Primärschlüssel sind notwendig, um eine Zeile von einer anderen zu unterscheiden. Sie identifizieren jede Datenzeile in einer Tabelle eindeutig.

Fremdschlüssel hingegen sind Primärschlüssel aus einer anderen Tabelle. Durch die Verknüpfung von Primär- und Fremdschlüsseln werden also die Datenbankbeziehungen aufrechterhalten.

Bei one-to-many enthält die Seite „one“ den Primärschlüssel und die Seite „many“ enthält diesen Primärschlüssel als Fremdschlüssel. In unserem obigen Beispiel werden zum Abrufen aller zu einer Rechnung gehörenden Zeilen die Tabelle „InvoiceLines“ abgefragt, wobei der Fremdschlüssel einer bestimmten Rechnungsnummer entspricht.

Bei „many-to-many“ wird die Beziehung in zwei „one-to-many“-Beziehungen unterteilt, indem eine dritte Tabelle, die sogenannte „joining“-Tabelle, verwendet wird. In unserem Teilzahlungssystem werden beispielsweise die Tabelle Rechnungen, die Tabelle Zahlungen und die Tabelle RechnungenZahlungen als Verbindungstabelle verwendet. Die Primärschlüssel der Tabelle „InvoicePayments“ werden ein zusammengesetzter Schlüssel sein, der aus den Primärschlüsseln der Tabellen „Invoices“ und „Payments“ wie folgt besteht

Beachten Sie, dass die Verbindungstabelle keine anderen Daten enthält, da sie außer der Verbindung der Tabellen Rechnungen und Zahlungen keinen weiteren Zweck hat.

Um die Rechnungen zu erhalten, die durch eine bestimmte Zahlungstransaktion, sagen wir PAY 1, bezahlt wurden, verbinden wir die Tabellen Rechnungen und Zahlungen über die Verbindungstabelle und fragen nach payment_id = "PAY 1".

Das war’s mit den Grundlagen einer Datenbank. Wir sind jetzt bereit, unsere Datenbank zu entwerfen.

Eine einfache Analogie: DBMS ist ein Excel-Programm, die Datenbank ist eine Excel-Arbeitsmappe und die Tabelle ist ein Excel-Arbeitsblatt.

Entwerfen unserer Xero-Implementierung

Nun, da wir ein grundlegendes Verständnis von Xero haben, können wir damit beginnen, eine grobe Skizze der Datenbankstruktur zu erstellen. Beachten Sie, dass ich das Format Table_Name verwenden werde. Das bedeutet, dass Wörter mit großem Anfangsbuchstaben durch Unterstriche getrennt werden. Außerdem werde ich für die Tabellennamen pluralisierte Namen verwenden.

Für den Verkaufszyklus, werden wir die folgenden Tabellen haben

• Rechnungen
• Kunden – ein Kunde kann viele Rechnungen haben, aber eine Rechnung kann nicht zu vielen Kunden gehören
• Rechnung_Zahlungen – denken Sie daran, dass wir im Moment davon ausgehen, dass es eine eins-zu-viele-Beziehung zwischen Rechnung_Zahlungen und Rechnungen gibt (keine Teilzahlungen)
• Rechnung_Linien – dies ist die Verbindungstabelle zwischen Rechnungen und WEG. Ein Konto kann in mehreren Rechnungen erscheinen und eine Rechnung kann mehrere Konten haben.
• Kontenplan (COA)

Für den Zyklus „Einkäufe“, haben wir die folgenden Tabellen

• Rechnungen
• Lieferanten – ein Lieferant kann viele Rechnungen haben, aber eine Rechnung kann nicht zu vielen Lieferanten gehören
• Rechnung_Zahlungen – denken Sie an unsere Annahme, dass es eine eins-zu-viele-Beziehung zwischen Rechnung_Zahlungen und Rechnungen gibt
• Rechnung_Zeilen – dies ist die Verbindungstabelle zwischen Rechnungen und COA. Ein Konto kann in mehreren Rechnungen erscheinen und eine Rechnung kann mehrere Konten haben.
• WEG – dasselbe wie oben im Verkaufszyklus. Wird hier nur der Vollständigkeit halber eingefügt.

Für den Kassenzyklus, werden wir die folgenden Tabellen haben (Zahlungstabellen, die wir bereits oben erstellt haben)

• Received_Moneys – kann einen optionalen Kunden haben
• Received_Money_Lines – dies ist die Verbindungstabelle Tabelle zwischen Received_Moneys und COA
• Spent_Moneys – kann einen optionalen Supplier haben
• Spent_Money_Lines – dies ist die Verbindungstabelle zwischen Spent_Moneys und COA

Konzeptionell, sieht unsere Datenbankstruktur wie folgt aus

Dieses Diagramm wird in der Datenbanksprache Entity-Relationship Diagram oder ERD genannt. Eins-zu-viele-Beziehungen werden mit 1 – M und viele-zu-viele-Beziehungen mit M – M bezeichnet.

Die Verbindungstabellen sind im obigen Diagramm nicht dargestellt, da sie in den Tabellen mit vielen-zu-vielen-Beziehungen implizit sind.

Implementierung unserer Version in SQL

Jetzt ist es an der Zeit, unser Modell in SQL zu implementieren. Beginnen wir damit, zunächst einige Konventionen festzulegen.

Der Primärschlüssel hat den Feld-/Spaltennamen id, und der Fremdschlüssel hat das Format table_id, wobei table der Name der Tabelle der „Many“-Seite im Singular ist. In der Tabelle Rechnungen lautet der Fremdschlüssel beispielsweise customer_id.

Zeit für den SQL-Code. Hier ist er.

Ein paar Dinge hier:

• SQL-Befehle unterscheiden nicht zwischen Groß- und Kleinschreibung, CREATE TABLE ist dasselbe wie create table
• Die IF EXISTS und IF NOT EXISTS sind optional. Ich habe sie nur verwendet, um Fehler in meinen SQL-Befehlen zu vermeiden. Wenn ich zum Beispiel eine nicht existierende Tabelle lösche, gibt SQLite einen Fehler aus. Außerdem habe ich IF NOT EXISTS an den Befehl create table angehängt, damit wir nicht versehentlich eine bestehende Tabelle überschreiben.
• Sei vorsichtig mit dem Befehl DROP TABLE! Er löscht eine bestehende Tabelle ohne Warnung, selbst wenn sie einen Inhalt hat.
• Tabellennamen können auch in Großbuchstaben geschrieben werden oder nicht. Wenn Tabellennamen Leerzeichen enthalten, sollten sie mit Backticks (`) umschlossen werden. Die Groß- und Kleinschreibung wird nicht beachtet. SELECT * FROM Customers ist dasselbe wie select * from customers.

Auch wenn SQL in Bezug auf die Syntax etwas entspannt ist, sollten Sie sich um Konsistenz in Ihrem SQL-Code bemühen.

Achten Sie auch auf die in der obigen ERD dargestellten Beziehungen. Denken Sie auch daran, dass der Fremdschlüssel auf der Vielfachen Seite steht.

Die Reihenfolge ist wichtig, da einige Tabellen aufgrund des Fremdschlüssels als Abhängigkeit zu einer anderen dienen. Zum Beispiel müssen Sie zuerst die Tabelle „Invoice_Payments“ vor der Tabelle „Invoice“ erstellen, da erstere von letzterer abhängig ist. Der Trick dabei ist, mit den Rändern der ERD zu beginnen, da diese die wenigsten Fremdschlüssel haben.

Sie können auch eine Beispieldatenbank in SQLite ohne Inhalt unter diesem Link herunterladen.

Um sie anzusehen, können Sie den kostenlosen und quelloffenen SQLite Browser verwenden. Laden Sie ihn hier herunter!

Inhalt zu unserer Datenbank hinzufügen

Nun, da wir die Beispieldatenbank haben, lassen Sie uns Daten in sie eingeben. Beispieldaten können von hier heruntergeladen werden – zerlegen Sie sie einfach nach Bedarf in CSVs.

Beachten Sie, dass Gutschriften als positive und Gutschriften als negative Werte angezeigt werden.

Für diesen Beitrag habe ich einfach die Importfunktion von DB Browser verwendet, um CSVs aus der obigen Excel-Datei zu importieren. Um zum Beispiel Customers.csv zu importieren

• Wählen Sie die Tabelle Customers

• Gehen Sie zu Datei > Importieren > Tabelle aus CSV-Datei und wählen Sie die Customers.csv

• Klicken Sie bei allen folgenden Aufforderungen auf Ok/Yes, um die Daten zu importieren.

Wenn Sie den folgenden SQL-Befehl eingeben, sollten alle Kunden in unserer Datenbank angezeigt werden

Erstellen von Finanzberichten aus unserer Datenbank

Um zu beweisen, dass unsere Datenbank als grobes Buchhaltungssystem funktioniert, lassen Sie uns die Bilanz erstellen.

Der erste Schritt besteht darin, die Transaktionsansichten für unsere Rechnungen, Rechnungen, Geldeingänge und Geldausgänge zu erstellen. Der Code sieht wie folgt aus

In den ersten beiden Anweisungen verwende ich eine CTE (mit dem Schlüsselwort recursive). CTEs sind nützlich, da ich 4 Tabellen kombiniere, um eine einzige Ansicht für Rechnungstransaktionen und entsprechende Zahlungen zu erhalten. Mehr über CTEs in SQLite erfahren Sie hier.

Nach der Ausführung des obigen Befehls sollte Ihre Datenbank die folgenden 4 Ansichten haben.

Schließlich erstellen wir den Code für die Trial Balance oder kurz TB. Beachten Sie, dass die TB nur eine Sammlung der Salden unserer Transaktionen unter Berücksichtigung der Regeln ist, die wir beim Entwurf unserer Datenbank festgelegt haben.

Der Code lautet wie folgt

Der obige Code enthält mehrere SQL-Abfragen, die durch den Befehl union all verbunden sind. Ich habe jede Abfrage kommentiert, um zu zeigen, was sie zu erreichen versucht.

Die erste Abfrage versucht zum Beispiel, alle Gutschriften für die Rechnungstransaktionen (hauptsächlich Verkäufe) zu erhalten. Die zweite für die Belastungen der Rechnungstransaktionen (hauptsächlich Einkäufe) und so weiter.

Die Ausführung sollte zu folgendem TB führen.

Sie können dies in Excel eingeben, um zu prüfen, ob die Belastungen gleich den Belastungen sind (was ich getan habe). Die Gesamtsumme von Soll und Haben ist 14115 bzw. -14115.

Hooray! Du hast es geschafft

Die Erstellung eines Buchhaltungssystems ist wirklich komplex. Wir haben im Grunde die ganze Bandbreite des Datenbankdesigns erforscht – vom Konzept über die ERD bis hin zur Erstellung und Abfrage der Datenbank. Klopfen Sie sich selbst auf die Schulter, dass Sie so weit gekommen sind.

Beachten Sie, dass wir unsere Datenbank absichtlich begrenzt haben, um uns mehr auf die Konzepte zu konzentrieren. Sie können diese aufheben und versuchen, eine andere ohne die Einschränkungen zu erstellen.

Das war’s! Du bist jetzt ein SQL-Ninja! Herzlichen Glückwunsch!

Sehen Sie sich mein zweites Buch Accounting Database Design an, das bald auf Leanpub veröffentlicht wird!

Sehen Sie sich auch mein erstes Buch PowerQuery Guide to Pandas auf Leanpub an.

Folgen Sie mir auf Twitter und Linkedin.