How to Build an Accounting System using SQLite

Kiristä oma kutina.

Tämän neuvon annan aina, kun joku kysyy minulta, miten ohjelmointia voi oppia. Käytännössä se tarkoittaa sitä, että sinun on ratkaistava asioita tai valittava projekteja, jotka ovat sinulle merkityksellisiä – joko työssäsi tai yksityiselämässäsi.

Silmitön Youtubesta tutoriaalien katselu, ohjelmointikirjojen lukeminen, koodin kopioiminen Reddit-postauksista jne. ei johda mihinkään, jos alat opettelemaan ohjelmointia.

Tässä postauksessa näytän, miten rakennat karkean kirjanpitotietokannan SQLite-tietokannan avulla.

Miksi luoda kirjanpitotietokanta sitten? Miksei vain kopioida julkisia tietoja, työnnetä niitä SQLiteen ja harjoitella sieltä käsin?

Syy on se, että kirjanpitotietokannan luominen on tarpeeksi edistynyt kattaakseen kaikki tietokantojen ja SQL:n osa-alueet – kyselyistä joinseihin, näkymiin ja CTE:iin.

Kirjanpitäjataustan omaavana olen sitä mieltä, että tämä on paras projekti SQL:n oppimiseen. Loppujen lopuksi ohjelmointi on työkalu ongelmien ratkaisemiseen. Näin ollen voisi yhtä hyvin ”ratkaista” vaikean ongelman, jotta SQL:n oppisi täysin.

Sain inspiraation kirjanpitojärjestelmän luomiseen SQL:n avulla tarkkailemalla, miten Xero toimii. Niille, jotka eivät tunne sitä, Xero on Uudesta-Seelannista peräisin oleva pilvipohjainen kirjanpito-ohjelmisto. Nyt se on laajentunut Australiaan, Yhdysvaltoihin, Kanadaan ja Isoon-Britanniaan.

Hyvää Xerossa on se, että sillä on mukava, siisti käyttöliittymä ja paljon sovelluksia, joista voi valita toiminnallisuuden laajentamiseksi.

Disclaimer: En ole Xeron insinööri enkä kehittäjä, eivätkä nämä havainnot välttämättä vastaa täysin sitä, miten järjestelmä toimii, koska sitä päivitetään jatkuvasti. Varmasti tässä esitetty SQL ei ole tarkka SQL-malli, jota Xero käyttää, koska heidän järjestelmänsä täytyy skaalautua. Mutta tämä on erittäin mielenkiintoinen projekti, joten tehdään se!

Kirjanpito 101

Ennen kuin innostut liikaa, käydään ensin pikakurssi kirjanpidosta.

Kirjanpidon perusyhtälö on

Varat = Velat + Oma pääoma

Tämässä yhtälössä on periaatteessa kolme osaa

• Varat ovat kaikki yrityksen resurssit.
• Velat ovat se, mitä yritys on velkaa.
• Ja Oma pääoma, kaikkien omistajan investointien, piirustusten, voiton tai tappion kertymä.

Oikea puoli kuvaa, miten omaisuus on rahoitettu – joko velkojen tai oman pääoman kautta.

Voidaan laajentaa yllä olevaa yhtälöä jaottelemaan Oma pääoma

Varat = Velat + Alkava oma pääoma + Tuotot – Kulut

Nämä viisi tiliä – Varat, Velat, Oma pääoma, Tuotot, & Kulut – ovat tilityyppejä, joita tyypillisesti näkee kirjanpitojärjestelmässä.

Sitten on käsite Debit ja Credit. Voisin jatkaa ja keskustella näistä kahdesta perusteellisesti, mutta tässä postauksessa sinun tarvitsee vain tietää, että jokaisessa liiketapahtumassa:

Debit = Credit

Nämä kaksi yhtälöä ohjaavat yleensä sitä, mitä koko kirjanpidon syklissä tapahtuu. Nämä kaksi yhtälöä toimivat myös ohjenuorana oman kirjanpitotietokantamme luomisessa.

Kirjanpitotaustan omaavana olen sitä mieltä, että tämä on paras projekti SQL:n oppimiseen. Loppujen lopuksi ohjelmointi on työkalu ongelmien ratkaisemiseen. Näin ollen voisi yhtä hyvin ”ratkaista” vaikean ongelman, jotta SQL:n oppisi täysin.

Xeron kirjanpidon toteutus

Tärkeää on muistaa, että Xero on suunniteltu siten, että siitä on hyötyä yrityksen omistajille (ei kirjanpitäjille) yrityksen päivittäisessä toiminnassa.

Siten se on suunniteltu tapahtumakiertojen ja sisäisen valvonnan ympärille.

Transaktiosyklit

Transaktioiden perussyklit ovat seuraavat

• Myyntisykli
• Ostosykli
• Kassasykli

Xero toteuttaa nämä syklit seuraavasti

Myyntisykli
Myyntitapahtumat merkitään Xeroon laskujen avulla. Kuvitellaan, että yritys laatii myynneistä (käteismyynnistä tai ennakkomyynnistä) todelliset paperilaskut. Juuri tätä Xero haluaa jäljitellä.

Laskut voidaan tulostaa suoraan ohjelmistosta, ja ne numeroidaan automaattisesti nousevassa järjestyksessä.

Laskut kasvattavat myyntitiliä ja myyntisaamistiliä (AR).

Ostot-sykli
Laskut syötetään Xeroon käyttämällä laskuja. Kuvittele jälleen, että yritys antaa todellisia laskuja ostoista (käteisostot tai laskut). Tämä on tavallinen tapaus yleishyödyllisten palvelujen ja varaston osalta. Tämä on myös asia, jonka Xero haluaa jäljitellä.

Laskut voidaan tulostaa suoraan ohjelmistosta, ja niitä voidaan käyttää täydentämään yrityksen tekemiä hyväksyntämenettelyjä.

Konepellin alla laskut kasvattavat Ostot-tiliä ja Ostovelat-tiliä (AP-tiliä).

Kassasykli
Tässä on kyse kaikista käteisvaroihin liittyvistä tapahtumista. Niitä on 4 tyyppiä

• Laskumaksut – maksamatta olevien laskujen maksut
• Laskumaksut – maksamatta olevien laskujen maksut
• Saadut rahat – käteistulot, jotka eivät ole laskujen maksuja. Kyse voi olla käteismyynnistä, mutta jos aiot laatia laskun, käytä Laskut-toimintoa.
• Käytetty raha – käteismaksut, jotka eivät ole laskujen maksuja. Tähän voi liittyä käteisostoja, mutta jos aiot laatia laskun, käytä Laskut-toimintoa.

Tässä on tapahtumakiertojen osa.

Sisäinen valvonta

Sisäistä valvontaa varten sinun on ymmärrettävä järjestelmätilien käsite.

Xerossa on kattava artikkeli järjestelmätilien ymmärtämisestä täällä. Mutta meidän tarkoituksiamme varten käsittelemme vain seuraavia järjestelmätilejä

• Tilit Saamiset
• Velat
• Pankkitilit (linkitetty pankkisyötteisiin)

Nämä tilejä ei voi käyttää manuaalisissa päiväkirjoissa. Tämä tarkoittaa, että Xero haluaa sinun käyttävän laskuja, laskuja ja käteistapahtumia (laskujen maksut, laskujen maksut, saadut rahat ja käytetyt rahat) näiden tilien saldon tukena. Tämä on Xeron sisäisen valvonnan toteutus, jos niin haluat.

Voit tietysti käyttää AR-, AP- ja pankkitilien ei-järjestelmäversiota luomalla ne tilikartassa (COA). Et kuitenkaan voi käyttää niihin AR-, AP- ja Pankkitilityyppejä.

Tärkeää on muistaa, että Xero on suunniteltu siten, että siitä on hyötyä yrityksen omistajille (ei kirjanpitäjille) yrityksen päivittäisessä toiminnassa.

Caveat: We’re Not Going Down The Rabbit Hole

Kirjanpidon suunnittelu on todella monimutkaista. Pelkästään tämän aiheen käsittelemiseen tarvitaan useita blogikirjoituksia. Siksi luomme yksinkertaisuuden vuoksi seuraavat oletukset (ei tarkalleen miten Xero toteuttaa nämä)

• Lasku- ja laskumaksut
  Laskumaksuilla voi maksaa kaksi tai useampia laskuja kokonaan. Tarkoittaen, että emme salli osittaisia maksuja. Sama pätee myös laskujen maksuihin.
• Inventaario
  Me emme aio käyttää tässä inventaarioeriä. Myynteihin tai ostoihin käytämme suoraan Varastotiliä sen sijaan, että luomme Varastotiliin kohdistuvia varastoeriä.

Siinä kaikki oletuksemme. Tietokantamme suunnittelun jälkeen lukija voi nostaa nämä oletukset ja yrittää jäljitellä Xeroa mahdollisimman paljon.

SQL:n perusteet

Nyt ennen kuin rekonstruoimme versiomme Xeron tietokantarakenteesta, käydään pikakurssi tietokannoista.

Tietokanta on kokoelma tauluja. Jokainen taulukko koostuu tietueriveistä, joita kutsutaan tietueiksi. Sarakkeita kutsutaan kentiksi.

Tietokannan kanssa työskentelevää ohjelmaa kutsutaan tietokannan hallintajärjestelmäksi eli DBMS:ksi. Yksinkertaisena analogiana DBMS on Excel-ohjelma, tietokanta on Excel-työkirja ja taulukko on Excel-taulukko.

Tietokannan ja Excel-työkirjan välillä on kaksi pääasiallista eroa.

Tiedon esittäminen on erillään tiedon tallentamisesta.

Tietokannassa olevia tietoja ei voi muokata menemällä suoraan datan päälle ja muokkaamalla sitä. (Muissa DBMS-ohjelmissa on graafiset käyttöliittymät, joiden avulla voit päästä suoraan tietokannassa oleviin tietoihin ja muokata niitä kuten taulukkolaskentaohjelmassa. Mutta konepellin alla tuo toiminto antaa SQL-komennon).

Taulukot ovat yleensä yhteydessä toisiinsa muodostaen suhteen.

Suhteet voivat olla yksi yhteen, yksi moniin tai monta moniin.

Yksi yhteen -suhde tarkoittaa, että ”taulukon rivi liittyy vain yhteen riviin toisessa taulukossa ja päinvastoin”. Esimerkki olisi työntekijän nimi veronumeroon.

Tällainen sisällytetään yleensä yhteen taulukkoon Työntekijät, koska tietojen jakamisesta kahteen taulukkoon ei oikeastaan ole mitään hyötyä.

Yksi-moneen-suhde taas tarkoittaa, että ”taulukon rivi liittyy vain yhteen tai useampaan riviin toisessa taulukossa, mutta ei päinvastoin”. Esimerkkinä tästä on Invoices to InvoiceLines. Laskulla voi olla useita rivejä, mutta laskurivi kuuluu vain tiettyyn laskuun.

Ja kuten ehkä arvasitkin, many-to-many tarkoittaa ”taulukon rivi liittyy vain yhteen tai useampaan riviin toisessa taulukossa ja päinvastoin”. Esimerkkinä voisi olla järjestelmä, joka toteuttaa osittaisia maksuja.

Lasku voidaan maksaa osittain eri maksutapahtumilla ja maksu voi maksaa osittain eri laskuja.

Miten tietokanta tietää nämä suhteet?
Se on yksinkertaista. Se tapahtuu primääri- ja vierasavainten avulla.

Primääriavaimet ovat välttämättömiä, jotta voidaan erottaa yksi rivi toisesta. Ne yksilöivät yksiselitteisesti jokaisen taulukon tietorivin.

Vieraat avaimet taas ovat toisesta taulusta peräisin olevia ensisijaisia avaimia. Näin ollen primääriavaimia ja vierasavaimia yhdistämällä tietokantasuhteet säilyvät.

Yksiköstä moneen -tapauksessa ”yksi” -puolella on primääriavain ja ”moni” -puolella on tämä primääriavain vierasavaimena. Yllä olevassa esimerkissämme saadaksemme kaikki laskuun kuuluvat rivit, teemme kyselyn InvoiceLines-taulusta, jossa vierasavain vastaa tiettyä laskunumeroa.

Monesta moneen -suhteessa suhde pilkotaan kahdeksi yhdestä moniin -suhteeksi käyttämällä kolmatta taulukkoa, jota kutsutaan nimellä ”joining”-taulukko. Esimerkiksi osamaksujärjestelmässämme on liitäntätauluna Laskut-taulu, Maksut-taulu ja LaskuMaksut-taulu. InvoicePayments-taulun ensisijaiset avaimet ovat yhdistelmäavain, joka koostuu Invoices- ja Payments-taulujen ensisijaisista avaimista seuraavasti

.

Huomaa, että liitäntätaulu ei sisällä muita tietoja, koska sillä ei ole muuta tarkoitusta kuin yhdistää Laskut- ja Maksut-taulukot.

Haluaksemme saada tietyllä maksutapahtumalla, vaikkapa PAY 1, maksetut laskut, yhdistämme Laskut- ja Maksut-taulukot liitäntätaulukon kautta ja teemme kyselyn payment_id = "PAY 1".

Se siitä tietokannan perusteista. Olemme nyt valmiita suunnittelemaan tietokantamme.

Yksinkertaisena analogiana DBMS on Excel-ohjelma, tietokanta on Excel-työkirja ja taulukko on Excel-taulukko.

Suunnittelemme Xeron toteutuksen

Nyt meillä on perusymmärrys Xerosta, jotta voimme aloittaa karkean hahmotelman luomisen sen tietokantarakenteesta. Huomaa, että aion käyttää Table_Name-muotoa. Se on isolla alkukirjaimella kirjoitetut sanat, jotka on erotettu alleviivauksilla. Aion myös käyttää monikossa olevia nimiä taulukoiden nimissä.

Myyntisyklin osalta, meillä on seuraavat taulukot

• Laskut
• Asiakkaat – asiakkaalla voi olla monta laskua, mutta lasku ei voi kuulua monelle asiakkaalle
• Laskut_Maksut – muistakaa toistaiseksi olettamuksemme siitä, että Laskut_Maksut ja Laskut ovat vastaavasti yksi-monia-suhde (ei osittaissuorituksia)
• Laskut_Laskurivit – tämä on liitäntätaulu laskujen ja COA:n välillä. Tili voi esiintyä useissa laskuissa ja laskulla voi olla useita tilejä.
• Tilikartta (COA)

Ostosykliä varten, meillä on seuraavat taulukot

• Laskut
• Tavarantoimittajat – tavarantoimittajalla voi olla monta laskua, mutta lasku ei voi kuulua monelle tavarantoimittajalle
• Laskut_Maksut – muistakaa toistaiseksi olettamuksemme siitä, että Laskujen_Maksujen ja Laskujen välillä on yksi-monia-suhde
• Laskujen_Rivistöt – tämä on liitäntätaulu laskujen ja tiliöintikirjanpidon (COA:n) välillä. Tili voi esiintyä useissa laskuissa ja laskulla voi olla useita tilejä.
• COA – sama kuin edellä myyntisyklissä. Laitan tämän vain täydellisyyden vuoksi.

Kassasykliä varten, meillä on seuraavat taulukot (maksutaulukot, jotka olemme jo luoneet edellä)

• Received_Moneys – voi olla valinnainen Customer
• Received_Money_Lines – tämä on liitos. Received_Moneys ja COA
• Spent_Moneys – voi olla valinnainen Supplier
• Spent_Money_Lines – tämä on yhdistävä taulukko Spent_Moneys ja COA

Käsitteellisesti, tietokantamme rakenne on seuraava

Tätä kaaviota kutsutaan tietokantakielessä Entity-Relationship Diagramiksi eli ERD:ksi. Yhden ja monen välisiä suhteita merkitään 1 – M:llä ja monen ja monen välisiä suhteita M – M:llä.

Liittymistauluja ei näytetä yllä olevassa kaaviossa, koska ne ovat implisiittisiä taulukoissa, joissa on monen ja monen välisiä suhteita.

Versiomme toteuttaminen SQL:ssä

Nyt on aika toteuttaa mallimme SQL:ssä. Aloitetaan määrittelemällä ensin joitakin konventioita.

Primääriavaimella on kentän/sarakkeen nimi id, ja vierasavain on muotoa table_id, jossa table on ”moni”-puolen taulukon nimi yksikössä. Esimerkiksi Laskut-taulussa vierasavain on SQL-koodin osalta customer_id.

Aika. Tässä se on.

Pari asiaa tässä:

• SQL-komennot eivät erota isoja ja pieniä kirjaimia, CREATE TABLE on sama kuin create table
• IF EXISTS ja IF NOT EXISTS ovat valinnaisia. Olen käyttänyt niitä vain estääkseni virheet SQL-komennoissani. Jos esimerkiksi pudotan olemattoman taulun, SQLite antaa virheilmoituksen. Lisäksi laitan IF NOT EXISTS create table -komennon päälle, jotta emme vahingossa ohita mitään olemassa olevaa taulukkoa.
• Ole varovainen DROP TABLE-komennon kanssa! Se poistaa olemassa olevan taulukon ilman varoitusta, vaikka sillä olisi sisältöä.
• Taulukoiden nimet voidaan myös kirjoittaa suuraakkosin tai ilman. Jos taulukkonimissä on välilyöntejä, ne on suljettava takaviivoilla (`). Niissä ei oteta huomioon isoja ja pieniä kirjaimia. SELECT * FROM Customers on sama kuin select * from customers.

Vaikka SQL on hieman rento syntaksin suhteen, kannattaa pyrkiä säilyttämään johdonmukaisuus SQL-koodissasi.

Kannattaa myös huomioida yllä olevassa ERD:ssä esitetyt suhteet. Muista myös, että vierasavain on monen puolella.

Järjestys on tärkeä, sillä jotkut taulukot toimivat riippuvuussuhteena toiseen taulukkoon vieraan avaimen takia. Esimerkiksi sinun on luotava ensin Invoice_Payments ennen Invoice-taulukkoa, koska ensin mainittu on jälkimmäisen riippuvuus. Temppu tässä on aloittaa ERD:n reunoista, koska niissä on vähiten vierasavaimia.

Voit myös ladata esimerkkitietokannan SQLite:ssä ilman sisältöä tästä linkistä.

Katsellaksesi sitä voit käyttää ilmaista ja avoimen lähdekoodin SQLite Browseria. Lataa se täältä!

Sisällön lisääminen tietokantaamme

Nyt kun meillä on esimerkkitietokanta, syötetään siihen tietoja. Esimerkkitiedot voi ladata täältä – jaa ne vain CSV-tiedostoiksi tarpeen mukaan.

Huomaa, että hyvitykset näytetään positiivisina ja hyvitykset negatiivisina.

Tässä postauksessa käytin vain DB Browserin tuontiominaisuutta tuodakseni CSV-tiedostot edellä mainitusta Excel-tiedostosta. Esimerkiksi tuodaksesi Customers.csv

• Valitse Customers-taulukko

• Valitse Tiedosto > Tuo > Taulukko CSV-tiedostosta ja valitse Customers.csv

• Tuoda tiedot napsauttamalla Ok/Yes kaikkiin seuraaviin kehotteisiin.

Jos annat seuraavan SQL-komennon, sen pitäisi näyttää kaikki tietokannassamme olevat asiakkaat

Taloudellisten raporttien luominen tietokannastamme

Todistaaksemme tietokantamme toimivan karkeana kirjanpitojärjestelmänä luodaan koe-erittelysaldotili.

Aluksi luomme tapahtumanäkymät Laskujen, Laskujen, Vastaanotettujen_rahojen ja Käytettyjen_rahojen tapahtumillemme. Koodi on seuraava

Kahdessa ensimmäisessä lausekkeessa käytän CTE:tä (avainsanalla recursive). CTE:t ovat hyödyllisiä, koska yhdistän neljä taulukkoa saadakseni yhden näkymän laskutapahtumille ja niitä vastaaville maksuille. Voit oppia lisää edellä mainituista CTE:istä SQLite:ssä täältä.

Yllä olevan komennon suorittamisen jälkeen tietokannassasi pitäisi olla seuraavat 4 näkymää.

Luotaan lopuksi koodi Trial Balancea tai lyhyesti TB:tä varten. Huomaa, että TB on vain kokoelma tapahtumiemme saldoista ottaen huomioon säännöt, jotka määrittelimme tietokantaamme suunnitellessamme.

Koodi on seuraava

Yllä oleva koodi sisältää useita SQL-kyselyjä, jotka on yhdistetty komennolla union all. Olen kommentoinut jokaista kyselyä osoittaakseni, mitä kullakin kyselyllä yritetään saavuttaa.

Ensimmäinen kysely yrittää esimerkiksi saada kaikki hyvitykset laskutapahtumista (enimmäkseen myyntitapahtumista). Toisella laskutapahtumien (enimmäkseen ostot) veloitukset ja niin edelleen.

Suorittamalla sen pitäisi saada tulokseksi seuraava TB.

Voit laittaa tämän Exceliin tarkistaaksesi, että veloitukset ovat yhtä suuret kuin kreditit (minkä tein). Yhteensä debetit ovat 14115 ja kreditit -14115.

Hooray! Olet onnistunut

Kirjanpitojärjestelmän luominen on todella monimutkaista. Tutustuimme periaatteessa koko tietokannan suunnittelun kirjoon – käsitteistä ERD:hen ja sen luomisesta kyselyihin. Taputtele itseäsi selkään, kun olet päässyt näin pitkälle.

Huomaa, että rajasimme tietokantaamme tarkoituksella, jotta voisimme keskittyä enemmän käsitteisiin. Voit nostaa nämä pois ja yrittää rakentaa toisen tietokannan ilman rajoituksia.

Se on siinä! Olet nyt SQL-ninja! Onneksi olkoon!

Lue toinen kirjani Kirjanpidon tietokantojen suunnittelu, joka julkaistaan pian Leanpubissa!

Lue myös ensimmäinen kirjani PowerQuery Guide to Pandas Leanpubissa.

Seuraa minua Twitterissä ja Linkedinissä.