Sådan opbygger du et regnskabssystem ved hjælp af SQLite

Skrad din egen kløe.

Dette er det råd, som jeg altid giver, når nogen spørger mig, hvordan man lærer at programmere. I praksis betyder det, at du skal løse ting eller vælge projekter, der er relevante for dig – enten i dit arbejde eller i dit privatliv.

Mindlessly going to tutorials in Youtube, reading programming books, copying code from Reddit posts, etc. will get you nowhere if you’re starting to learn programming.

In this post, I’m going to show you how to build a crude accounting database using SQLite.

So, why create an accounting database? Hvorfor ikke bare kopiere offentlige data, skubbe dem til SQLite og øve sig derfra?

Grunden er, at det at oprette en regnskabsdatabase er avanceret nok til at dække alle aspekter af databaser og SQL – fra forespørgsler til joins til views og CTE’er.

Da jeg kommer fra en regnskabsbaggrund, mener jeg, at dette er det bedste projekt til at lære SQL. Programmering er jo trods alt et værktøj til at løse problemer. Derfor kan man lige så godt “løse” et svært problem for at lære SQL fuldt ud.

Jeg fik inspiration til at lave et regnskabssystem ved hjælp af SQL ved at observere, hvordan Xero fungerer. For dem, der ikke er bekendt med det, er Xero et cloud regnskabsprogram, der stammer fra New Zealand. Det er nu udvidet til Australien, USA, Canada og Storbritannien.

Det gode ved Xero er, at det har en flot, ren grænseflade og en masse apps at vælge imellem for at udvide funktionaliteten.

Disclaimer: Jeg er ikke ingeniør eller udvikler hos Xero, og disse observationer svarer måske ikke helt til, hvordan systemet fungerer, da det altid opdateres. Det er helt sikkert, at den SQL, der præsenteres her, ikke er det nøjagtige SQL-design, som Xero bruger, da deres system skal skaleres. Men det er et meget interessant projekt, så lad os gøre det!

Regnskab 101

Hvor du bliver for begejstret for meget, så lad os først få et crashkursus i regnskab.

Den grundlæggende regnskabsmæssige ligning er

Aktiver = Passiver + Egenkapital

Denne ligning består grundlæggende af tre dele

• Aktiver er alle virksomhedens ressourcer.
• Passiver er det, som virksomheden skylder.
• og Egenkapital er opsamlingen af alle ejerens investeringer, trækninger, overskud eller tab.

Den højre side beskriver, hvordan aktiverne blev finansieret – enten gennem passiver eller egenkapital.

Vi kan udvide ovenstående ligning til at opdele Egenkapitalen

Aktiver = Passiver + Begyndende Egenkapital + Indtægter – Udgifter

Disse 5 konti – Aktiver, Passiver, Egenkapital, Indtægter, & Udgifter – er de kontotyper, som man typisk ser i et regnskabssystem.

Så er der begrebet Debet og Kredit. Jeg kunne fortsætte og diskutere disse to i dybden, men for dette indlæg er det eneste, du behøver at vide, at i enhver transaktion:

Debit = Credit

Disse to ligninger styrer generelt, hvad der sker i hele regnskabscyklussen. Disse to ligninger vil også tjene som en vejledning i oprettelsen af vores egen regnskabsdatabase.

Da jeg kommer fra en regnskabsbaggrund, mener jeg, at dette er det bedste projekt til at lære SQL. Programmering er trods alt et værktøj til at løse problemer. Derfor kan man lige så godt “løse” et svært et for fuldt ud at lære SQL.

Xero’s implementering af regnskab

Det vigtige at huske er, at Xero er designet på en sådan måde, at det vil være nyttigt for virksomhedsejere (ikke revisorer) i den daglige drift af virksomheden.

Som sådan er det designet omkring transaktionscyklusser og intern kontrol.

Transaktionscyklusser

De grundlæggende transaktionscyklusser er følgende

• Salgscyklus
• Købscyklus
• Kassecyklus

Xero implementerer disse cyklusser på følgende måde

Salgscyklus
Salg indtastes i Xero ved hjælp af fakturaer. Forestil dig, at virksomheden udsteder egentlige papirfakturaer for salg (kontant salg eller på konto). Det er præcis det, som Xero ønsker at efterligne.

Fakturerne kan udskrives direkte fra softwaren, og de nummereres automatisk i stigende rækkefølge.

Under motorhjelmen øger fakturaer salgskontoen og kontoen for debitorer (AR).

Købscyklus
Fakturaer indtastes i Xero ved hjælp af fakturaer. Forestil dig igen, at virksomheden udsteder faktiske fakturaer for køb (kontantkøb eller på regning). Dette er det sædvanlige tilfælde for forsyningsselskaber og inventar. Det er også det, som Xero ønsker at efterligne.

Regningerne kan udskrives direkte fra softwaren og kan bruges til at supplere eventuelle godkendelsesprocedurer, som virksomheden foretager.

Under motorhjelmen øger regninger kontoen Indkøb og kontoen Kreditor (AP).

Cash Cycle
Dette omfatter alle transaktioner, der vedrører kontanter. Der er 4 typer

• Faktura betalinger – betalinger af de udestående fakturaer
• Faktura betalinger – betalinger af de udestående fakturaer
• Modtagne penge – kontante indbetalinger, der ikke er fakturabetalinger. Det kan dreje sig om kontantsalg, men hvis du skal udstede en faktura, skal du bruge funktionen Fakturaer.
• Udbetalte penge – kontante udbetalinger, som ikke er fakturabetalinger. Dette kan involvere kontantkøb, men hvis du skal udstede en faktura, skal du bruge funktionen Fakturaer.

Det er den på transaktionscyklus-delen.

Intern kontrol

For intern kontrol skal du forstå begrebet systemkonti.

Xero har en omfattende artikel til at forstå systemkonti her. Men til vores formål vil vi kun diskutere følgende systemkonti

• Debitorkonti
• Kreditorer
• Kreditorer
• Bankkonti (knyttet til bankfeeds)

Disse konti kan ikke bruges i manuelle journaler. Det betyder, at Xero ønsker, at du skal bruge fakturaer, regninger og kontanttransaktioner (fakturabetalinger, regningsbetalinger, modtagne penge og brugte penge) til at understøtte saldoen på disse konti. Dette er Xeros implementering af intern kontrol, om du vil.

Du kan naturligvis bruge den ikke-systemiske version af AR-, AP- og bankkonti ved at oprette dem i kontoplanen (COA). Du kan dog ikke bruge AR-, AP- og bankkontotyperne til dem.

Det vigtige at huske er, at Xero er designet på en sådan måde, at det vil være nyttigt for virksomhedsejere (ikke revisorer) i den daglige drift af virksomheden.

Caveat: Vi går ikke ned i kaninhullet

Det er virkelig komplekst at designe et regnskab. Det vil kræve flere blogindlæg bare at dække dette emne. Derfor vil vi for enkelhedens skyld oprette følgende forudsætninger (ikke præcis hvordan Xero implementerer disse)

• Faktura- og fakturabetaling
  Fakturabetaling kan betale to eller flere fakturaer samlet. Det vil sige, at vi ikke tillader delvise betalinger. Det samme gælder for Betalinger af regninger.
• Inventar
  Vi vil ikke bruge inventarposter her. Ved salg eller køb vil vi bruge lagerkontoen direkte i stedet for at oprette lagerposter, der er knyttet til lagerkontoen.

Det er alt for vores antagelser. Efter at have designet vores database kan læseren løfte disse antagelser og prøve at efterligne Xero så meget som muligt.

SQL-grundlæggende

Nu skal vi, før vi rekonstruerer vores version af Xeros databasestruktur, have et lynkursus i databaser.

En database er en samling af tabeller. Hver tabel består af rækker af data kaldet records. Kolonnerne kaldes felter.

Programmet til at arbejde med en database kaldes et Database Management System eller DBMS. Som en simpel analogi er DBMS til Excel-program, database til Excel-arbejdsbog og tabel til et Excel-arbejdsark.

Der er to hovedforskelle mellem en database og en Excel-arbejdsbog.

Datapræsentation af data er adskilt fra datalagring.

Det betyder, at du ikke kan redigere data i en database ved at gå over direkte til dataene og redigere dem. (Andre DBMS-programmer har GUI’er, der giver dig mulighed for at få direkte adgang til data i en database og redigere dem som i et regneark. Men under motorhjelmen udsender denne handling en SQL-kommando).

Tabeller er normalt knyttet til hinanden for at danne en relation.

Relationer kan være en-til-en, en-til-mange eller mange-til-mange.

En-til-en-relation betyder “en tabelrække er kun relateret til én række i en anden tabel og omvendt”. Et eksempel ville være medarbejdernavn til skatteidentifikationsnummer.

Denne type indgår normalt i en enkelt tabel Medarbejdere, da der ikke rigtig er nogen fordel ved at adskille dataene i to tabeller.

En-til-mange på den anden side betyder “en tabelrække er kun relateret til en eller flere rækker i en anden tabel, men ikke omvendt”. Et eksempel er Fakturaer til FakturaLinjer. En faktura kan have flere linjer, men en fakturalinje hører kun til en bestemt faktura.

Og som du måske har gættet det, betyder many-to-many “en tabelrække er kun relateret til en eller flere rækker i en anden tabel og omvendt”. Et eksempel kunne være et system, der implementerer delvise betalinger.

En faktura kan blive betalt delvist af forskellige betalingstransaktioner, og en betaling kan betale forskellige fakturaer delvist.

Hvordan kender en database disse relationer?
Det er enkelt. Det er ved hjælp af primære og fremmede nøgler.

Primære nøgler er nødvendige for at skelne en række fra en anden. De identificerer entydigt hver datarække i en tabel.

Fremme nøgler er på den anden side primære nøgler fra en anden tabel. Ved at relatere primærnøglerne og de fremmede nøgler fastholdes databasens relationer.

For one-to-many indeholder “one”-siden primærnøglen, og “many”-siden indeholder denne primærnøgle som sin fremmede nøgle. I vores ovenstående eksempel skal vi for at få alle de linjer, der hører til en faktura, forespørge i tabellen InvoiceLines, hvor den fremmede nøgle er lig med et bestemt fakturanummer.

For mange-til-mange-forholdet opdeles i to en-til-mange-forhold ved hjælp af en tredje tabel kaldet “joining”-tabellen. Vores delvise betalingssystem vil f.eks. have tabellen Fakturaer, Betalinger og FakturaBetalinger som den sammenføjende tabel. Primærnøglerne i tabellen InvoicePayments vil være en sammensat nøgle bestående af primærnøglerne for tabellerne Invoices og Payments på følgende måde

Bemærk, at sammenføjningstabellen ikke indeholder andre data, da den ikke har noget andet formål ud over at sammenføje tabellerne Fakturaer og Betalinger.

For at få de fakturaer, der er betalt af en bestemt betalingstransaktion, f.eks. PAY 1, sammenføjer vi tabellerne Fakturaer og Betalinger via sammenføjningstabellen og spørger efter payment_id = "PAY 1".

Det er det for det grundlæggende i en database. Vi er nu klar til at designe vores database.

Som en simpel analogi er DBMS til Excel-program, database til Excel-arbejdsbog og tabel til et Excel-regneark.

Design af vores implementering af Xero

Nu har vi en grundlæggende forståelse af Xero for at begynde at skabe en grov skitse af dens databasestruktur. Vær opmærksom på, at jeg vil bruge Table_Name-formatet. Det er er er ord med stort begyndelsesbogstav adskilt af understregninger. Jeg har også tænkt mig at bruge flertalsnavne til tabelnavne.

For Sales Cycle, skal vi have følgende tabeller

• Fakturaer
• Kunder – en kunde kan have mange fakturaer, men en faktura kan ikke tilhøre mange kunder
• Faktura_Betalinger – husk vores antagelse indtil videre, at der er et en-til-mange-forhold mellem henholdsvis Faktura_Betalinger og Fakturaer (ingen delvise betalinger)
• Faktura_Linjer – dette er joining-tabellen mellem Fakturaer og COA. En konto kan optræde i flere fakturaer, og en faktura kan have flere konti.
• Kontoplan (COA)

For indkøbscyklusen, skal vi have følgende tabeller

• Fakturaer
• Leverandører – en leverandør kan have mange fakturaer, men en faktura kan ikke tilhøre mange leverandører
• Bill_Payments – husk vores antagelse indtil videre, at der er en én-til-mange-relation mellem henholdsvis Bill_Payments og Fakturaer
• Bill_Lines – dette er sammenføjningstabellen mellem Fakturaer og COA. En konto kan optræde i flere regninger, og en regning kan have flere konti.
• COA – det samme som ovenstående i salgscyklus. Jeg sætter det bare ind her for fuldstændighedens skyld.

For Cash Cycle, skal vi have følgende tabeller (betalingstabeller, som vi allerede har oprettet ovenfor)

• Received_Moneys – kan have en valgfri Customer
• Received_Money_Lines – dette er den sammenføjede mellem Received_Moneys og COA
• Spent_Moneys – kan have en valgfri Supplier
• Spent_Money_Lines – dette er sammenføjningstabellen mellem Spent_Moneys og COA

Konceptuelt set, vores databasestruktur er som følger

Dette diagram kaldes Entity-Relationship Diagram eller ERD i databasens sprogbrug. One-to-many-relationer betegnes med 1 – M og many-to-many med M – M.

De sammenføjende tabeller er ikke vist i ovenstående diagram, da de er implicitte i tabellerne med many-to-many-relationer.

Implementering af vores version i SQL

Nu er det tid til at implementere vores model i SQL. Lad os starte med at definere nogle konventioner først.

Primærnøglen vil have felt-/kolonnenavnet id, og den fremmede nøgle vil have formatet table_id, hvor table er navnet på tabellen på “mange”-siden i ental. I tabellen Fakturaer vil den fremmede nøgle f.eks. være customer_id.

Time for SQL-koden. Her er den.

Et par ting her:

• SQL-kommandoer er ikke stempelfølsomme, CREATE TABLE er det samme som create table
• Den IF EXISTS og IF NOT EXISTS er valgfri. Jeg har bare brugt dem for at forhindre fejl i mine SQL-kommandoer. Hvis jeg f.eks. dropper en ikke-eksisterende tabel, vil SQLite give en fejl. Jeg har også sat IF NOT EXISTS på kommandoen create table, så vi ikke ved et uheld tilsidesætter en eksisterende tabel.
• Vær forsigtig med DROP TABLE-kommandoen! Den vil slette en eksisterende tabel uden advarsel, selv om den har indhold.
• Tabelnavne kan også skrives med eller uden store bogstaver. Hvis der er mellemrum i tabelnavne, skal de omsluttes med bagstreg (`). Der tages ikke hensyn til store og små bogstaver. SELECT * FROM Customers er det samme som select * from customers.

Selv om SQL er en smule afslappet med hensyn til syntaks, bør du bestræbe dig på at opretholde konsistens i din SQL-kode.

Se også på de relationer, der er vist i ERD’et ovenfor. Husk også, at den fremmede nøgle er på den mange side.

Rækkefølgen er vigtig, da nogle tabeller tjener som en afhængighed til en anden på grund af den fremmede nøgle. F.eks. skal du først oprette Invoice_Payments først før Invoice-tabellen, da førstnævnte er en afhængighed af sidstnævnte. Tricket her er at starte med kanterne i ERD’en, da det er dem med det mindste antal fremmednøgler.

Du kan også downloade en eksempeldatabase i SQLite uden indhold i dette link.

For at se den kan du bruge den gratis og open-sourced SQLite Browser. Download den her!

Tilføjning af indhold til vores database

Nu da vi har eksempeldatabasen, kan vi indtaste data i den. Prøvedata kan downloades herfra – du skal blot opdele dem i CSV’er efter behov.

Bemærk, at kreditter vises som positive og kreditter som negative.

I dette indlæg har jeg blot brugt DB Browser’s importfunktion til at importere CSV’er fra ovenstående Excel-fil. For eksempel for at importere Customers.csv

• Vælg tabellen Customers

• Gå til File > Import > Import > Table from CSV file og vælg Customers.csv

• Klik på Ok/Ja til alle efterfølgende prompts for at importere dataene.

Hvis du udsender følgende SQL-kommando, bør den vise alle kunderne i vores database

Skabelse af regnskabsrapporter fra vores database

For at bevise, at vores database fungerer som et groft regnskabssystem, skal vi oprette Prøvesaldoen.

Det første skridt er at oprette transaktionsvisningerne for vores transaktioner Invoices, Bills, Received_Moneys og Spent_Moneys. Koden vil være som følger

I de to første udsagn bruger jeg en CTE (med nøgleordet recursive). CTE’er er nyttige, da jeg kombinerer 4 tabeller for at få en enkelt visning for fakturatransaktioner og tilsvarende betalinger. Du kan lære mere om CTE’er i SQLite her.

Når du har udført ovenstående kommando, bør din database have følgende 4 visninger.

Slutteligt opretter vi koden til Trial Balance eller TB forkortet. Bemærk, at TB blot er en samling af saldi for vores transaktioner under hensyntagen til de regler, vi fastsatte, da vi designede vores database.

Koden er som følger

Overstående kode indeholder flere SQL-forespørgsler, der er forbundet med kommandoen union all. Jeg har kommenteret hver forespørgsel for at vise, hvad de hver især forsøger at opnå.

Den første forespørgsel forsøger f.eks. at få alle kreditterne for fakturatransaktionerne (for det meste salg). Den anden for debiteringer for fakturatransaktionerne (mest Indkøb) osv.

Den anden for debiteringer for fakturatransaktionerne (mest Indkøb) osv.

Du kan sætte dette ind i Excel for at kontrollere, at debiteringer svarer til krediteringer (hvilket jeg gjorde). De samlede debiteringer og kreditter er henholdsvis 14115 og -14115.

Hurra! Du har klaret det

Det er virkelig kompliceret at oprette et regnskabssystem. Vi har i det væsentlige udforsket hele spektret af databasedesign – fra koncepter til ERD til oprettelse til forespørgsel på det. Klap dig selv på skulderen, fordi du er nået så langt.

Læs dig mærke til, at vi bevidst har begrænset vores database for at fokusere mere på koncepterne. Du kan løfte disse og forsøge at bygge en anden uden begrænsningerne.

Det var det! Du er nu en SQL-ninja! Tillykke!

Se min anden bog Accounting Database Design, der snart udkommer på Leanpub!

Kig også på min første bog PowerQuery Guide to Pandas på Leanpub.

Følg mig på Twitter og Linkedin.