Hur man bygger ett redovisningssystem med SQLite

Kräfta din egen klåda.

Detta är det råd jag alltid ger när någon frågar mig hur man lär sig programmera. I praktiken innebär det att du måste lösa saker eller välja projekt som är relevanta för dig – antingen i ditt arbete eller i ditt 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.

I det här inlägget ska jag visa dig hur du bygger en rå bokföringsdatabas med hjälp av SQLite.

Så, varför skapa en bokföringsdatabas? Varför inte bara kopiera offentliga data, lägga dem i SQLite och öva därifrån?

Anledningen är att skapandet av en bokföringsdatabas är tillräckligt avancerat för att täcka alla aspekter av databaser och SQL – från frågor till joins till vyer och CTE:s.

Att komma från en bokföringsbakgrund gör att jag tror att detta är det bästa projektet för att lära sig SQL. Programmering är trots allt ett verktyg för att lösa problem. Därför är det lika bra att ”lösa” ett svårt problem för att lära sig SQL fullt ut.

Inspirationen till att skapa ett redovisningssystem med hjälp av SQL fick jag genom att observera hur Xero fungerar. För dem som inte känner till det är Xero ett molnbaserat bokföringsprogram som har sitt ursprung i Nya Zeeland. Det har nu expanderat till Australien, USA, Kanada och Storbritannien.

Det som är bra med Xero är att det har ett trevligt rent gränssnitt och många appar att välja mellan för att utöka funktionaliteten.

Disclaimer: Jag är varken ingenjör eller utvecklare på Xero och dessa observationer kanske inte exakt motsvarar hur systemet fungerar eftersom det alltid uppdateras. Säkerligen är den SQL som presenteras här inte den exakta SQL-designen som Xero använder eftersom deras system måste skalas. Men detta är ett mycket intressant projekt så låt oss göra det!

Redovisning 101

För att du ska bli alltför upphetsad ska vi först ha en snabbkurs i redovisning.

Den grundläggande ekvationen för redovisning är

Aktiva tillgångar = skulder + eget kapital

Denna ekvation består i princip av tre delar

• Aktiva tillgångar är alla resurser som företaget förfogar över
• Skulder är det som företaget är skyldigt
• och Eget kapital, ackumulationen av alla ägarens investeringar, uttag, vinst eller förlust.

Den högra sidan beskriver hur tillgångarna finansierades – antingen genom skulder eller eget kapital.

Vi kan utvidga ekvationen ovan för att bryta ner Eget kapital

Aktiva = Skulder + Begynnande Eget kapital + Intäkter – Kostnader

Dessa fem konton – Tillgångar, Skulder, Eget kapital, Intäkter, & Kostnader – är de kontotyper som du vanligtvis ser i ett redovisningssystem.

Därefter kommer begreppet debet och kredit. Jag skulle kunna fortsätta och diskutera dessa två på djupet men för det här inlägget är allt du behöver veta att i varje transaktion:

Debit = Credit

Dessa två ekvationer styr i allmänhet vad som händer i hela bokföringscykeln. Dessa två ekvationer kommer också att tjäna som vägledning när vi skapar vår egen bokföringsdatabas.

När jag kommer från en bokföringsbakgrund anser jag att detta är det bästa projektet för att lära sig SQL. Programmering är trots allt ett verktyg för att lösa problem. Därför kan man lika gärna ”lösa” ett svårt för att lära sig SQL fullt ut.

Xeros implementering av redovisning

Det viktiga att komma ihåg är att Xero är utformat på ett sådant sätt att det kommer att vara användbart för företagare (inte revisorer) i den dagliga verksamheten.

Som sådan är den utformad kring transaktionscykler och intern kontroll.

Transaktionscykler

De grundläggande transaktionscyklerna är följande

• Försäljningscykel
• Köpscykel
• Kassacirkel

Xero implementerar dessa cykler på följande sätt

Försäljningscykel
Försäljning förs in i Xero med hjälp av fakturor. Föreställ dig att företaget utfärdar pappersfakturor för försäljning (kontantförsäljning eller förskottsförsäljning). Detta är precis vad Xero vill efterlikna.

Fakturorna kan skrivas ut direkt från programmet och de numreras automatiskt i stigande ordning.

Under huven ökar fakturorna försäljningskontot och kontot för kundfordringar.

Köpcykel
Fakturor förs in i Xero med hjälp av fakturor. Föreställ dig återigen att företaget utfärdar faktiska fakturor för inköp (kontantköp eller på konto). Detta är det vanliga fallet för allmännyttiga tjänster och inventarier. Detta är också det som Xero vill replikera.

Räkningarna kan skrivas ut direkt från programmet och kan användas för att komplettera eventuella godkännandeförfaranden som görs av företaget.

Under huven ökar räkningarna kontot för inköp och kontot för leverantörsskulder (AP).

Kassacirkel
Detta omfattar alla transaktioner som rör kontanter. Det finns 4 typer

• Fakturabetalningar – betalningar av utestående fakturor
• Fakturabetalningar – betalningar av utestående fakturor
• Mottagna pengar – kontantinkomster som inte är fakturabetalningar. Det kan handla om kontantförsäljning, men om du ska utfärda en faktura ska du använda funktionen Fakturor.
• Utbetalda pengar – kontantutbetalningar som inte är fakturabetalningar. Detta kan innebära kontantköp men om du ska utfärda en faktura använder du funktionen Fakturor.

Det var det om transaktionscyklerna.

Intern kontroll

För intern kontroll måste du förstå begreppet systemkonton.

Xero har en omfattande artikel för att förstå systemkonton här. Men för vårt syfte kommer vi bara att diskutera följande systemkonton

• Accounts Receivable
• Accounts Payable
• Bank Accounts (linked to Bank Feeds)

Dessa konton kan inte användas i Manual journals. Detta innebär att Xero vill att du ska använda fakturor, räkningar och kontanttransaktioner (fakturabetalningar, fakturabetalningar, mottagna pengar och använda pengar) för att stödja saldot på dessa konton. Detta är Xeros implementering av intern kontroll om du så vill.

Självklart kan du använda den icke-systembaserade versionen av AR-, AP- och bankkontona genom att skapa dem i kontoplanen (COA). Du kan dock inte använda kontotyperna AR, AP och Bank för dem.

Det viktiga att komma ihåg är att Xero är utformat på ett sådant sätt att det kommer att vara användbart för företagare (inte revisorer) i den dagliga verksamheten.

Caveat: Vi går inte ner i kaninhålet

Att utforma en bokföring är verkligen komplicerat. Det kommer att krävas flera blogginlägg bara för att täcka detta ämne. Därför kommer vi för enkelhetens skull att skapa följande antaganden (inte exakt hur Xero implementerar dessa)

• Invoice and Bill Payments
  Invoice Payments kan betala två eller flera fakturor helt och hållet. Det betyder att vi inte kommer att tillåta delbetalningar. Samma sak gäller för fakturabetalningar.
• Inventarier
  Vi kommer inte att använda inventarier här. Vid försäljning eller köp kommer vi att använda inventeringskontot direkt i stället för att skapa inventeringsposter som mappas till inventeringskontot.

Det var allt för våra antaganden. Efter att ha utformat vår databas kan läsaren lyfta dessa antaganden och försöka efterlikna Xero så mycket som möjligt.

SQL Basics

Nu, innan vi rekonstruerar vår version av Xeros databasstruktur, ska vi ha en snabbkurs i databaser.

En databas är en samling tabeller. Varje tabell består av datarader som kallas poster. Kolumnerna kallas fält.

Programmet för att arbeta med en databas kallas för ett databashanteringssystem eller DBMS. Som en enkel analogi är DBMS ett Excel-program, databas en Excel-arbetsbok och tabell ett Excel-arbetsblad.

Det finns två huvudsakliga skillnader mellan en databas och en Excel-arbetsbok.

Datapresentationen är skild från datalagringen.

Det innebär att du inte kan redigera data i en databas genom att gå över direkt till datan och redigera den. (Andra DBMS-program har grafiska gränssnitt som gör det möjligt att direkt komma åt data i en databas och redigera dem som i ett kalkylblad. Men under huven utfärdar den åtgärden ett SQL-kommando).

Tabeller är vanligtvis kopplade till varandra för att bilda en relation.

Relationer kan vara en-till-en, en-till-många eller många-till-många.

En-till-en-relation innebär att ”en tabellrad är relaterad till endast en rad i en annan tabell och vice versa”. Ett exempel skulle vara anställdas namn till skatteregistreringsnummer.

Denna typ ingår vanligtvis i en enda tabell Anställda eftersom det egentligen inte finns någon fördel med att separera data i två tabeller.

En-till-många å andra sidan betyder ”en tabellrad är relaterad till endast en eller flera rader i en annan tabell, men inte tvärtom”. Ett exempel är fakturor till fakturarader. En faktura kan ha flera rader, men en fakturarad hör bara till en viss faktura.

Och som ni kanske gissade det betyder många-till-många att ”en tabellrad är relaterad till endast en eller flera rader i en annan tabell och vice versa”. Ett exempel är ett system som implementerar delbetalningar.

En faktura kan delvis betalas av olika betalningstransaktioner och en betalning kan delvis betala olika fakturor.

Hur känner en databas till dessa relationer?
Det är enkelt. Det är genom användning av primära och främmande nycklar.

Primärnycklar är nödvändiga för att skilja en rad från en annan. De identifierar unikt varje datarad i en tabell.

Förändringsnycklar är å andra sidan primärnycklar från en annan tabell. Genom att relatera primärnycklar och främmande nycklar bevaras databasrelationerna.

För one-to-many innehåller ”one”-sidan primärnyckeln och ”many”-sidan innehåller denna primärnyckel som sin främmande nyckel. I vårt ovanstående exempel, för att få fram alla rader som hör till en faktura, frågar vi i tabellen InvoiceLines där den främmande nyckeln är lika med ett visst fakturanummer.

För många till många bryts relationen ner i två en till många-relationer genom användning av en tredje tabell som kallas ”joining”-tabellen. Till exempel kommer vårt system för delbetalningar att ha tabellerna Invoices (fakturor), Payments (betalningar) och InvoicePayments (fakturabetalningar) som sammanfogande tabell. Primärnycklarna i tabellen InvoicePayments kommer att vara en sammansatt nyckel som består av primärnycklarna för tabellerna Invoices och Payments enligt följande

Observera att den sammanfogade tabellen inte innehåller några andra uppgifter eftersom den inte har något annat syfte än att sammanfoga tabellerna Fakturor och Betalningar.

För att få fram de fakturor som betalats av en viss betalningstransaktion, till exempel PAY 1, sammanfogar vi tabellerna Fakturor och Betalningar genom sammanfogningstabellen och frågar efter payment_id = "PAY 1".

Det var allt för grunderna i en databas. Vi är nu redo att utforma vår databas.

Som en enkel analogi kan man säga att DBMS är som ett Excel-program, databas är som en Excel-arbetsbok och tabell är som ett Excel-arbetsblad.

Utforma vår implementering av Xero

Nu när vi har en grundläggande förståelse för Xero kan vi börja skapa en grov skiss av dess databasstruktur. Observera att jag kommer att använda formatet Table_Name. Det är är är ord med stora bokstäver på första bokstaven separerade med understrykningar. Jag kommer också att använda pluraliserade namn för tabellnamnen.

För försäljningscykeln, kommer vi att ha följande tabeller

• Fakturor
• Kunder – en kund kan ha många fakturor, men en faktura kan inte tillhöra många kunder
• Faktura_betalningar – kom ihåg vårt antagande för tillfället att det finns ett en-till-många-förhållande mellan Faktura_betalningar och fakturor (inga delbetalningar)
• Faktura-linjer – det här är den sammanfogande tabellen mellan fakturor och COA. Ett konto kan förekomma i flera fakturor och en faktura kan ha flera konton.
• Kontoplan (COA)

För inköpscykeln, kommer vi att ha följande tabeller

• Fakturor
• Leverantörer – en leverantör kan ha många fakturor, men en faktura kan inte tillhöra många leverantörer
• Fakturabetalningar – kom ihåg vårt antagande för tillfället att det finns ett en-till-många-förhållande mellan Fakturabetalningar och Fakturor
• Faktura-linjer – det här är den sammanfogande tabellen mellan fakturor och COA. Ett konto kan förekomma i flera fakturor och en faktura kan ha flera konton.
• COA – samma sak som ovan i försäljningscykeln. Jag lägger bara in det här för fullständighetens skull.

För kontantcykeln, kommer vi att ha följande tabeller (betalningstabeller som vi redan har skapat ovan)

• Received_Moneys – kan ha en valfri kund
• Received_Money_Lines – detta är den sammanförande mellan Received_Moneys och COA
• Spent_Moneys – kan ha en valfri Supplier
• Spent_Money_Lines – detta är den sammanlänkande tabellen mellan Spent_Moneys och COA

Konceptuellt, vår databasstruktur är följande

Detta diagram kallas för Entity-Relationship Diagram eller ERD i databasspråket. En-till-många-relationer betecknas med 1 – M och många-till-många med M – M.

De sammanfogande tabellerna visas inte i ovanstående diagram eftersom de är implicita i tabellerna med många-till-många-relationer.

Implementering av vår version i SQL

Nu är det dags att implementera vår modell i SQL. Låt oss börja med att definiera några konventioner först.

Primärnyckeln kommer att ha fält-/kolumnnamnet id, och den främmande nyckeln kommer att ha formatet table_id där table är namnet på tabellen på ”många”-sidan i singularform. Till exempel i tabellen Fakturor kommer den främmande nyckeln att vara customer_id.

Time för SQL-koden. Här är den.

Ett par saker här:

• SQL-kommandon är inte skiftlägeskänsliga, CREATE TABLE är samma som create table
• De IF EXISTS och IF NOT EXISTS är valfria. Jag har bara använt dem för att förhindra fel i mina SQL-kommandon. Om jag till exempel släpper en tabell som inte finns kommer SQLite att ge ett fel. Dessutom sätter jag IF NOT EXISTS på kommandot create table så att vi inte av misstag åsidosätter någon befintlig tabell.
• Var försiktig med kommandot DROP TABLE! Det raderar en befintlig tabell utan förvarning även om den har innehåll.
• Tabellnamn kan också skrivas med eller utan versaler. Om tabellnamnen innehåller blanksteg ska de omges av backtips (`). De är inte skiftlägeskänsliga. SELECT * FROM Customers är samma som select * from customers.

Även om SQL är lite avslappnad när det gäller syntaxen bör du sträva efter att upprätthålla konsistens i din SQL-kod.

Närma dig också de relationer som visas i ERD ovan. Kom också ihåg att den främmande nyckeln finns på den många sidan.

Ordningen är viktig eftersom vissa tabeller fungerar som ett beroende till en annan på grund av den främmande nyckeln. Till exempel måste du först skapa Invoice_Payments före tabellen Invoice eftersom den förstnämnda tabellen är beroende av den sistnämnda. Tricket här är att börja med kanterna i ERD eftersom det är de som har minst antal utländska nycklar.

Du kan också ladda ner en exempeldatabas i SQLite utan innehåll i den här länken.

För att visa den kan du använda den fria och öppen källkod SQLite Browser. Ladda ner den här!

Lägga till innehåll till vår databas

Nu när vi har provdatabasen kan vi lägga in data i den. Provdata kan laddas ner härifrån – dela bara upp den i CSV-filer efter behov.

Notera att krediter visas som positiva och krediter som negativa.

För det här inlägget använde jag bara DB Browser’s importfunktion för att importera CSV-filer från Excel-filen ovan. Till exempel för att importera Customers.csv

• Välj tabellen Customers

• Gå till File > Import > Table from CSV file och välj Customers.csv