Hoe bouw je een boekhoudsysteem met SQLite

Krab je eigen jeuk.

Dit is het advies dat ik altijd geef als iemand me vraagt hoe ik moet leren programmeren. In de praktijk betekent het dat je dingen moet oplossen of projecten moet kiezen die relevant zijn voor jou – hetzij in je werk of in je persoonlijke leven.

Mindeloos naar tutorials op Youtube gaan, programmeerboeken lezen, code kopiëren uit Reddit-posts, enzovoort, zal je nergens brengen als je begint te leren programmeren.

In deze post ga ik je laten zien hoe je een ruwe boekhouddatabase kunt bouwen met behulp van SQLite.

Dus, waarom zou je een boekhouddatabase maken? Waarom niet gewoon openbare gegevens kopiëren, ze naar SQLite schuiven en van daaruit verder oefenen?

De reden hiervoor is dat het maken van een boekhoudkundige database ver genoeg gaat om alle aspecten van databases en SQL te behandelen – van query’s tot joins tot views en CTE’s.

Vanuit een boekhoudkundige achtergrond denk ik dat dit het beste project is om SQL te leren. Immers, programmeren is een instrument om problemen op te lossen. Daarom kan ik net zo goed een moeilijk probleem “oplossen” om SQL volledig te leren.

Ik kreeg de inspiratie om een boekhoudsysteem met SQL te maken door te observeren hoe Xero werkt. Voor degenen die niet bekend zijn met het, Xero is een cloud accounting software die is ontstaan in Nieuw-Zeeland. Het is nu uitgebreid naar Australië, de VS, Canada en het VK.

Het goede van Xero is dat het een mooie schone interface heeft en veel apps om uit te kiezen om de functionaliteit uit te breiden.

Disclaimer: Ik ben geen ingenieur of ontwikkelaar bij Xero en deze observaties komen misschien niet precies overeen met hoe het systeem werkt, omdat het altijd wordt bijgewerkt. Zeker, de SQL die hier wordt gepresenteerd is niet het exacte SQL-ontwerp dat Xero gebruikt omdat hun systeem schaalbaar moet zijn. Maar dit is een zeer interessant project, dus laten we het doen!

Boekhouden 101

Voordat je te enthousiast wordt, eerst een spoedcursus boekhouden.

De fundamentele boekhoudkundige vergelijking is

Assets = Liabilities + Equity

Die vergelijking bestaat in principe uit drie delen

• Assets zijn alle middelen van de entiteit.
• Liabilities zijn wat de onderneming verschuldigd is.
• en Equity, de accumulatie van alle investeringen van de eigenaar, tekeningen, winst of verlies.

Het rechterdeel beschrijft hoe de activa zijn gefinancierd – met passiva of met eigen vermogen.

We kunnen bovenstaande vergelijking uitbreiden om het Eigen Vermogen uit te splitsen

Bezittingen = Schulden + Beginnend Eigen Vermogen + Opbrengsten – Uitgaven

Deze 5 rekeningen – Activa, Schulden, Eigen Vermogen, Opbrengsten, & Uitgaven – zijn de rekeningtypen die je doorgaans in een boekhoudsysteem ziet. Ik zou deze twee diepgaand kunnen bespreken, maar voor deze post is het voldoende te weten dat bij elke transactie:

Debet = Credit

Deze twee vergelijkingen bepalen in het algemeen wat er in de hele boekhoudcyclus gebeurt. Deze twee vergelijkingen zullen ook als leidraad dienen bij het maken van onze eigen boekhoudkundige database.

Gezien mijn achtergrond in de boekhouding, denk ik dat dit het beste project is om SQL te leren. Programmeren is immers een middel om problemen op te lossen. Je kunt dus net zo goed een moeilijk probleem “oplossen” om SQL volledig te leren.

Xero’s implementatie van boekhouden

Het belangrijkste om te onthouden is dat Xero zo is ontworpen dat het nuttig is voor bedrijfseigenaren (en niet voor accountants) in de dagelijkse gang van zaken van het bedrijf.

Als zodanig, het ontworpen rond transactie cycli en interne controle.

Transactiecycli

De basistransactiecycli zijn de volgende

• Verkoopcyclus
• Inkoopcyclus
• Kascyclus

Xero implementeert deze cycli als volgt

Verkoopcyclus
Verkopen worden in Xero ingevoerd met behulp van facturen. Stelt u zich voor dat een bedrijf echte papieren facturen uitgeeft voor verkopen (contante verkopen of verkopen op rekening). Dit is precies wat Xero wil nabootsen.

De facturen kunnen direct vanuit de software worden afgedrukt en worden automatisch in oplopende volgorde genummerd.

Onder de motorkap verhogen facturen de rekening Verkoop en de rekening Debiteuren (AR).

Inkoopcyclus
Facturen worden in Xero ingevoerd met behulp van Facturen. Ook hier stelt u zich voor dat het bedrijf daadwerkelijk facturen voor aankopen uitgeeft (contant of op rekening). Dit is het gebruikelijke geval voor nutsvoorzieningen en voorraden. Dit is ook wat Xero wil repliceren.

De rekeningen kunnen direct vanuit de software worden afgedrukt en kunnen worden gebruikt als aanvulling op eventuele goedkeuringsprocedures van het bedrijf.

Onder de motorkap verhogen rekeningen de Inkooprekening en de Crediteurenrekening (AP).

Cash Cycle
Hierbij gaat het om alle transacties die te maken hebben met Cash. Er zijn 4 soorten

• Factuurbetalingen – betalingen van de openstaande facturen
• Bill Betalingen – betalingen van de openstaande facturen
• Ontvangen geld – kasontvangsten die geen factuurbetalingen zijn. Dit kan contante verkoop betreffen, maar als u een factuur gaat uitgeven, gebruik dan de functie Facturen.
• Uitgegeven geld – contante uitbetalingen die geen factuurbetalingen zijn. Dit kan aankopen in contanten betreffen, maar als u een factuur gaat uitgeven, gebruikt u de functie Facturen.

Dat is het gedeelte over de transactiecycli.

Interne controle

Voor interne controle moet u het concept van systeemrekeningen begrijpen.

Xero heeft hier een uitgebreid artikel voor het begrijpen van systeemrekeningen. Maar voor onze doeleinden gaan we alleen in op de volgende systeemrekeningen

• Boekingen
• Betalingen
• Bankrekeningen (gekoppeld aan Bank Feeds)

Deze rekeningen kunnen niet worden gebruikt in Handmatige journalen. Dit betekent dat Xero wil dat u facturen, rekeningen en contante transacties (factuurbetalingen, factuurbetalingen, ontvangen geld en uitgegeven geld) gebruikt om het saldo van deze rekeningen te ondersteunen. Dit is Xero’s implementatie van interne controle, zo u wilt.

U kunt natuurlijk de niet-systeemversie van de AR-, AP- en bankrekeningen gebruiken door ze in het rekeningstelsel (Chart of Accounts, COA) aan te maken. U kunt er echter niet de AR-, AP- en Bank-rekeningtypen voor gebruiken.

Het belangrijkste om te onthouden is dat Xero zo is ontworpen dat het nuttig is voor bedrijfseigenaren (niet accountants) in de dagelijkse bedrijfsvoering.

Caveat: We’re not Going Down The Rabbit Hole

Het ontwerpen van een boekhouding is echt complex. Het zal meerdere blog posts vergen om dit onderwerp te behandelen. Daarom gaan we voor de eenvoud de volgende veronderstellingen maken (niet precies hoe Xero deze implementeert)

• Factuur- en factuurbetalingen
  Factuurbetalingen kunnen twee of meer facturen in hun geheel betalen. Dat betekent dat we geen gedeeltelijke betalingen toestaan. Hetzelfde geldt voor Bill Payments.
• Inventory
  We gaan hier geen inventaris items gebruiken. Voor verkopen of aankopen, gaan we de Inventarisrekening direct gebruiken in plaats van inventarisitems te maken die gekoppeld zijn aan de Inventarisrekening.

Dat is het voor onze veronderstellingen. Na het ontwerpen van onze database, kan de lezer deze aannames opheffen en proberen Xero zo veel mogelijk na te bootsen.

SQL Basics

Nu voordat we onze versie van Xero’s database structuur reconstrueren, laten we een spoedcursus databases volgen.

Een database is een verzameling tabellen. Elke tabel bestaat uit rijen van gegevens genaamd records. De kolommen worden velden genoemd.

Het programma om met een database te werken wordt een Database Management Systeem of DBMS genoemd. Als een eenvoudige analogie, DBMS is om Excel-programma, database is om Excel-werkmap en tabel is om een Excel-werkblad.

Er zijn twee belangrijke verschillen tussen een database en een Excel-werkmap.

De presentatie van gegevens is gescheiden van de opslag van gegevens.

Dat wil zeggen, je kunt niet bewerken van gegevens in een database door te gaan over de rechtstreeks naar de gegevens en bewerken van het. (Andere DBMS-programma’s hebben GUI’s waarmee je rechtstreeks naar de gegevens in een database kunt gaan en ze kunt bewerken zoals een spreadsheet. Maar onder de motorkap, die actie geeft een SQL-commando).

Tabellen zijn meestal aan elkaar gekoppeld om een relatie te vormen.

Relaties kunnen een-op-een, een-op-veel, of veel-op-veel zijn.

Een-op-een relatie betekent “een tabel rij is gerelateerd aan slechts een rij in een andere tabel en vice versa”. Een voorbeeld zou zijn naam van de werknemer om het fiscaal identificatienummer.

Dit soort wordt meestal opgenomen in een enkele tabel Werknemers als er echt geen voordeel van het scheiden van de gegevens in twee tabellen.

One-to-many aan de andere kant betekent “een tabel rij is gerelateerd aan slechts een of meer rijen in een andere tabel, maar niet vice versa”. Een voorbeeld is Invoices to InvoiceLines. Een factuur kan meerdere regels hebben, maar een factuurregel hoort alleen bij een bepaalde factuur.

En zoals u misschien al geraden had, betekent many-to-many “een tabelrij is gerelateerd aan slechts een of meer rijen in een andere tabel en vice versa”. Een voorbeeld zou een systeem zijn dat gedeeltelijke betalingen implementeert.

Een factuur kan gedeeltelijk worden betaald door verschillende betalingstransacties en een betaling kan verschillende facturen gedeeltelijk betalen.

Hoe zou een database deze relaties kennen?
Het is eenvoudig. Het is door het gebruik van primaire en vreemde sleutels.

Priminaire sleutels zijn nodig om de ene rij te onderscheiden van de andere. Ze identificeren elke rij gegevens in een tabel op unieke wijze.

Foreign keys, aan de andere kant, zijn primaire sleutels uit een andere tabel. Door de primaire sleutels en de vreemde sleutels met elkaar in verband te brengen, worden de relaties in de database dus bewaard.

Voor one-to-many bevat de “ene” kant de primaire sleutel en de “vele” deze primaire als zijn vreemde sleutel. In ons bovenstaande voorbeeld, om alle lijnen die behoren tot een factuur te krijgen, vragen we de InvoiceLines tabel waar de foreign key is gelijk aan een bepaalde factuurnummer.

Voor veel tot veel, de relatie wordt opgesplitst in twee one-to-many relaties door middel van een derde tabel genaamd de “join” tabel. Bijvoorbeeld, ons gedeeltelijke betalingssysteem zal de tabel Facturen, de tabel Betalingen, en de tabel FactuurBetalingen als samenvoegende tabel hebben. De primaire sleutels van de tabel Factuurbetalingen zullen een samengestelde sleutel zijn die bestaat uit de primaire sleutels voor de tabellen Facturen en Betalingen, en wel als volgt

Merk op dat de samenvoegtabel geen andere gegevens bevat, aangezien deze geen ander doel heeft dan het samenvoegen van de tabellen voor facturen en betalingen.

Om de facturen te krijgen die door een bepaalde betalingstransactie zijn betaald, bijvoorbeeld PAY 1, voegen we de tabellen Facturen en Betalingen samen via de verbindingstabel en vragen we naar de payment_id = "PAY 1".

Dat is het voor de basis van een database. We zijn nu klaar om onze database te ontwerpen.

Als een eenvoudige analogie, DBMS is aan Excel-programma, database is aan Excel-werkmap en tabel is aan een Excel-werkblad.

Ontwerpen van onze implementatie van Xero

Nu dat we een basiskennis van Xero hebben om te beginnen met het maken van een ruwe schets van de database structuur. Merk op dat ik het Table_Name formaat ga gebruiken. Dat zijn woorden met een hoofdletter en een eerste letter, gescheiden door underscores. Ik ga ook meervoudige namen gebruiken voor de tabelnamen.

Voor de verkoopcyclus, hebben we de volgende tabellen

• Facturen
• Klanten – een klant kan veel facturen hebben, maar een factuur kan niet bij veel klanten horen
• Factuur_ Betalingen – onthoud voor nu onze aanname dat er een één-op-veel relatie is tussen Factuur_ Betalingen en Facturen (geen gedeeltelijke betalingen)
• Factuur_Lijnen – dit is de verbindingstabel tussen Facturen en COA. Een rekening kan in meerdere facturen voorkomen en een factuur kan meerdere rekeningen hebben.
• Rekeningstelsel (COA)

Voor de Inkoopcyclus, hebben we de volgende tabellen

• Facturen
• Leveranciers – een leverancier kan veel facturen hebben, maar een factuur kan niet bij veel leveranciers horen
• Facturen_betalingen – denk aan onze aanname dat er een één-op-veel relatie is tussen facturen_betalingen en facturen
• Facturen_Lijnen – dit is de verbindingstabel tussen facturen en COA. Een rekening kan in meerdere rekeningen voorkomen en een rekening kan meerdere rekeningen hebben.
• COA – hetzelfde als hierboven in de Sales Cycle. Gewoon hier zetten voor de volledigheid.

Voor de Kas Cyclus, hebben we de volgende tabellen (betalingstabellen die we hierboven al hebben aangemaakt)

• Received_Moneys – kan een optionele klant hebben
• Received_Money_Lines – dit is de verbindende tabel tussen Received_Moneys en COA
• Spent_Moneys – kan een optionele Supplier
• Spent_Money_Lines – dit is de verbindingstabel tussen Spent_Moneys en COA

Conceptueel, ziet onze databasestructuur er als volgt uit

Dit diagram wordt Entity-Relationship Diagram of ERD genoemd in het databasegemeen. One-to-many-relaties worden aangeduid met 1 – M en many-to-many met M – M.

De verbindingstabellen worden in bovenstaand diagram niet getoond omdat ze impliciet zijn in de tabellen met many-to-many-relaties.

Het implementeren van onze versie in SQL

Nu is het tijd om ons model in SQL te implementeren. Laten we beginnen met het definiëren van enkele conventies eerst.

De primaire sleutel zal veld/kolomnaam van id hebben, en de vreemde sleutel zal het formaat table_id hebben waarbij table de naam van de tabel van de “vele” kant in enkelvoud vorm is. Bijvoorbeeld, in de tabel Facturen, zal de vreemde sleutel customer_id.

Tijd voor de SQL-code zijn. Hier is het.

Een paar dingen hier:

• SQL commando’s zijn niet hoofdlettergevoelig, CREATE TABLE is hetzelfde als create table
• De IF EXISTS en IF NOT EXISTS zijn optioneel. Ik heb ze alleen gebruikt om fouten in mijn SQL commando’s te voorkomen. Als ik bijvoorbeeld een niet-bestaande tabel drop, zal SQLite een fout geven. Ook heb ik IF NOT EXISTS op het create table commando gezet, zodat we niet per ongeluk een bestaande tabel overschrijven.
• Wees voorzichtig met het DROP TABLE commando! Het zal een bestaande tabel verwijderen zonder waarschuwing, zelfs als deze inhoud heeft.
• Tabelnamen kunnen ook met of zonder hoofdletters worden geschreven. Als tabelnamen spaties bevatten, moeten ze worden omsloten door backticks (`). Zij zijn niet hoofdlettergevoelig. SELECT * FROM Customers is hetzelfde als select * from customers.

Ondanks dat SQL wat syntax betreft een beetje ontspannen is, moet je er toch naar streven om consistentie in je SQL code te behouden.

Let ook op de relaties die in de ERD hierboven zijn weergegeven. Vergeet ook niet dat de foreign key is aan de vele kant.

Volgorde is belangrijk omdat sommige tabellen dienen als een afhankelijkheid van een andere als gevolg van de foreign key. U moet bijvoorbeeld eerst Invoice_Payments aanmaken vóór de Invoice-tabel, omdat de eerstgenoemde een afhankelijkheid is van de laatstgenoemde. De truc hier is om te beginnen met de randen van de ERD, omdat dat degenen zijn met het minste aantal foreign keys.

U kunt ook een voorbeeld database in SQLite downloaden zonder inhoud in deze link.

Om het te bekijken, kunt u de gratis en open-sourced SQLite Browser gebruiken. Download hem hier!

Inhoud toevoegen aan onze database

Nu we de voorbeeld database hebben, laten we er gegevens in invoeren. De voorbeeldgegevens kunnen van hier worden gedownload – verdeel ze in CSV’s naar behoefte.

Merk op dat credits als positieven en credits als negatieven worden weergegeven.

Voor dit bericht heb ik DB Browser’s importfunctie gebruikt om CSV’s uit het bovenstaande Excel-bestand te importeren. Om bijvoorbeeld Customers.csv

• te importeren, selecteert u de tabel Customers

• Ga naar Bestand > Tabel importeren > uit CSV-bestand en kies de tabel Customers.csv

• Klik op Ok/Yes bij alle volgende vragen om de gegevens te importeren.

Als u het volgende SQL-commando uitvoert, zouden alle klanten in onze database moeten worden weergegeven

Financiële rapporten maken van onze database

Om aan te tonen dat onze database werkt als een ruw boekhoudsysteem, laten we de balans van de balans maken.

De eerste stap is het maken van de transactie-overzichten voor onze facturen, rekeningen, ontvangen_Moneys, en uitgegeven_Moneys transacties. De code ziet er als volgt uit

In de eerste twee statements gebruik ik een CTE (met het sleutelwoord recursive). CTE’s zijn nuttig omdat ik 4 tabellen combineer om één enkel overzicht te krijgen voor Factuur transacties en overeenkomstige betalingen. Meer informatie over CTE’s in SQLite vindt u hier.

Na het uitvoeren van het bovenstaande commando zou uw database de volgende 4 weergaven moeten hebben.

Ter slotte maken we de code voor de Trial Balance of kortweg TB. Merk op dat TB gewoon een verzameling is van de saldi van onze transacties, rekening houdend met de regels die we hebben vastgelegd toen we onze database ontwierpen.

De code ziet er als volgt uit

De bovenstaande code bevat meerdere SQL-query’s die met het commando union all zijn samengevoegd. Ik heb elke query geannoteerd om te laten zien wat elke query probeert te bereiken.

De eerste query probeert bijvoorbeeld alle crediteringen voor de factuurtransacties (meestal Verkoop) te krijgen. De tweede voor de debits van de Bill transacties (meestal Purchases) enzovoort.

Uitvoeren zou moeten resulteren in de volgende TB.

U kunt dit in Excel zetten om te controleren of de debits gelijk zijn aan de credits (wat ik heb gedaan). Het totaal aan debiteringen en crediteringen is respectievelijk 14115 en -14115.

Hooray! Je hebt het gehaald

Een boekhoudsysteem maken is echt ingewikkeld. We hebben in wezen het hele scala van database-ontwerp verkend – van concepten tot ERD tot creatie tot query’s. Geef jezelf een schouderklopje dat je zover bent gekomen.

Denk eraan dat we onze database met opzet hebben beperkt om ons meer op de concepten te richten. Je kunt deze opheffen en proberen een andere te bouwen zonder de beperkingen.

Dat is het! Je bent nu een SQL ninja! Gefeliciteerd!

Check out my second book Accounting Database Design soon to be published on Leanpub!

Bekijk ook mijn eerste boek PowerQuery Guide to Pandas op Leanpub.

Volg me op Twitter en Linkedin.