Hogyan építsünk könyvelési rendszert az SQLite segítségével

Vakarja meg a saját viszketését.

Ezt a tanácsot adom mindig, amikor valaki megkérdezi, hogyan tanuljon meg programozni. Gyakorlatilag ez azt jelenti, hogy olyan dolgokat kell megoldanod, vagy olyan projekteket kell választanod, amelyek relevánsak számodra – akár a munkádban, akár a magánéletedben.

Azzal, hogy ész nélkül nézegetsz tutorialokat a Youtube-on, programozási könyveket olvasol, kódot másolsz Reddit-posztokból stb. nem jutsz semmire, ha programozni kezdesz tanulni.

Ebben a bejegyzésben megmutatom, hogyan készíthetsz egy kezdetleges könyvelési adatbázist SQLite segítségével.

Szóval, miért hozz létre egy könyvelési adatbázist? Miért nem másoljuk egyszerűen a nyilvános adatokat, tuszkoljuk őket az SQLite-be, és onnan gyakorolunk?

Az ok az, hogy egy számviteli adatbázis létrehozása eléggé előrehaladott ahhoz, hogy az adatbázisok és az SQL minden aspektusát lefedje – a lekérdezésektől a joinokon át a nézetekig és a CTE-kig.

A számviteli háttérrel rendelkezők szerintem ez a legjobb projekt az SQL megtanulására. Végül is a programozás egy eszköz a problémák megoldására. Ezért akár egy nehéz problémát is “megoldhatunk”, hogy teljes mértékben megtanuljuk az SQL-t.

A Xero működésének megfigyelése inspirált arra, hogy SQL segítségével hozzak létre egy könyvelési rendszert. Azok számára, akik nem ismerik, a Xero egy felhőalapú könyvelési szoftver, amely Új-Zélandról származik. Mára már Ausztráliában, az Egyesült Államokban, Kanadában és az Egyesült Királyságban is elterjedt.

A Xero jó tulajdonsága, hogy szép, letisztult felülete van, és rengeteg alkalmazás közül lehet választani a funkciók bővítéséhez.

Disclaimer: Nem vagyok sem mérnök, sem fejlesztő a Xerónál, és ezek a megfigyelések nem biztos, hogy pontosan megfelelnek a rendszer működésének, mivel azt folyamatosan frissítik. Természetesen az itt bemutatott SQL nem a Xero által használt pontos SQL-dizájn, mivel a rendszerüknek skálázódnia kell. De ez egy nagyon érdekes projekt, úgyhogy vágjunk bele!”

Belszámolás 101

Mielőtt túlságosan izgatottak lennénk, először vegyünk egy gyorstalpaló tanfolyamot a könyvelésről.

Az alapvető számviteli egyenlet a következő

Vagyon = Kötelezettségek + Saját tőke

Ez az egyenlet alapvetően három részből áll

• Az eszközök a gazdálkodó egység összes forrása.
• A kötelezettségek az, amivel a vállalat tartozik.
• és a saját tőke, a tulajdonos összes befektetésének, lehívásának, nyereségének vagy veszteségének az összessége.

A jobb oldal azt írja le, hogy az eszközöket hogyan finanszírozták – vagy a Kötelezettségek vagy a Saját tőke révén.

A fenti egyenletet kibővíthetjük a Saját tőke lebontására

Vagyon = Kötelezettségek + Kezdő saját tőke + Bevételek – Költségek

Ez az 5 számla – Eszközök, Kötelezettségek, Saját tőke, Bevételek, & Költségek – azok a számlatípusok, amelyeket jellemzően egy számviteli rendszerben látunk.

Ezután ott van a Terhelés és a Hitel fogalma. Folytathatnám és részletesen tárgyalhatnám ezt a kettőt, de ehhez a bejegyzéshez csak annyit kell tudni, hogy minden tranzakcióban:

Debit = Credit

Ez a két egyenlet szabályozza általában azt, ami az egész számviteli ciklusban történik. Ez a két egyenlet fog útmutatóként szolgálni a saját könyvelési adatbázisunk létrehozásához is.

Könyvelési háttérrel rendelkezve úgy gondolom, hogy ez a legjobb projekt az SQL megtanulására. Végül is a programozás egy eszköz a problémák megoldására. Ennélfogva akár “megoldhatnánk” egy nehéz feladatot is, hogy teljes mértékben megtanuljuk az SQL-t.

A Xero könyvelési implementációja

A fontos dolog, amit nem szabad elfelejteni, hogy a Xero úgy van megtervezve, hogy a vállalkozók (nem a könyvelők) számára hasznos legyen a vállalkozás mindennapi működésében.

Akként a tranzakciós ciklusok és a belső ellenőrzés köré tervezték.

Tranzakciós ciklusok

Az alapvető tranzakciós ciklusok a következők

• Eladási ciklus
• Beszerzési ciklus
• Pénzforgalmi ciklus

A Xero ezeket a ciklusokat a következőképpen valósítja meg

Eladási ciklus
Az eladások számlák segítségével kerülnek a Xeróba. Képzeljük el, hogy a vállalkozás tényleges papírszámlákat állít ki az értékesítésről (készpénzes értékesítés vagy számla ellenében). A Xero pontosan ezt szeretné leképezni.

A számlák közvetlenül a szoftverből nyomtathatók ki, és automatikusan növekvő sorrendben számozva vannak.

A számlák a motorháztető alatt az Értékesítési számlát és a Követelések (AR) számlát növelik.

Beszerzési ciklus
A számlák a Számlák segítségével kerülnek a Xeróba. Ismét képzeljük el, hogy a vállalkozás tényleges számlákat állít ki a vásárlásokról (készpénzes vásárlás vagy számla ellenében). Ez a szokásos eset a közüzemi szolgáltatások és a Készletek esetében. Ez az a dolog, amit a Xero szintén le akar másolni.

A számlák közvetlenül a szoftverből nyomtathatók ki, és felhasználhatók a vállalkozás által végzett jóváhagyási eljárások kiegészítésére.

A gépháztető alatt a számlák növelik a beszerzések számlát és a kötelezettségek (AP) számlát.

Pénzforgalmi ciklus
Ez magában foglalja a készpénzzel kapcsolatos összes tranzakciót. 4 típusa van

• Számlafizetés – a fennálló számlák kifizetése
• Számlafizetés – a fennálló számlák kifizetése
• Beérkezett pénz – olyan készpénzbevételek, amelyek nem számlafizetések. Ez lehet készpénzes értékesítés, de ha számlát állít ki, használja a Számlák funkciót.
• Kiadott pénz – készpénzkifizetések, amelyek nem számlakifizetések. Ez magában foglalhat készpénzes vásárlásokat, de ha számlát állít ki, használja a Számlák funkciót.

Ez a tranzakciós ciklusokra vonatkozó rész.

Belső ellenőrzés

A belső ellenőrzéshez meg kell értenie a rendszerszámlák fogalmát.

AXero itt talál egy átfogó cikket a rendszerszámlák megértéséről. A mi céljainkhoz azonban csak a következő rendszerszámlákat fogjuk tárgyalni

• Követelések
• Kifizetendő számlák
• Bankszámlák (bankbetétekkel összekapcsolva)

Ezek a számlák nem használhatók a kézi naplókban. Ez azt jelenti, hogy a Xero azt szeretné, ha számlákkal, számlákkal és készpénzes tranzakciókkal (számlafizetések, számlafizetések, kapott pénz és elköltött pénz) támogatná ezeknek a számláknak az egyenlegét. Ez a Xero belső ellenőrzésének megvalósítása, ha úgy tetszik.

Az AR, AP és Bank számlák nem rendszerváltozatát természetesen használhatja, ha létrehozza őket a Számlatükörben (COA). Nem használhatja azonban az AR, AP és Bank számlatípusokat ezekhez.

A fontos dolog, amit nem szabad elfelejteni, hogy a Xero úgy van kialakítva, hogy a vállalkozás tulajdonosai (és nem a könyvelők) számára hasznos legyen a vállalkozás mindennapi működésében.

Caveat: Nem megyünk le a nyúl üregébe

A könyvelés kialakítása valóban összetett dolog. Több blogbejegyzésre lenne szükség csak ennek a témának a lefedéséhez. Ezért az egyszerűség kedvéért a következő feltevéseket fogjuk létrehozni (nem pontosan úgy, ahogyan a Xero ezeket megvalósítja)

• Számla- és számlafizetés
  A számlafizetés két vagy több számlát is kifizethet teljes egészében. Vagyis nem engedélyezzük a részleges kifizetéseket. Ugyanez igaz a számlafizetésekre is.
• Leltár
  Itt nem fogunk leltárelemeket használni. Értékesítéshez vagy vásárláshoz közvetlenül a Készletszámlát fogjuk használni, ahelyett, hogy a Készletszámlára leképezett készletelemeket hoznánk létre.

Ez a mi feltételezéseink. Adatbázisunk megtervezése után az olvasó felemelheti ezeket a feltételezéseket, és megpróbálhatja a Xero-t a lehető legjobban utánozni.

SQL-alapok

Most mielőtt rekonstruálnánk a Xero adatbázisszerkezetének a mi verzióját, vegyünk egy gyorstalpaló tanfolyamot az adatbázisokról.

Az adatbázis táblák gyűjteménye. Minden egyes táblázat rekordoknak nevezett adatsorokból áll. Az oszlopokat mezőknek nevezzük.

Az adatbázissal dolgozó programot adatbázis-kezelő rendszernek vagy DBMS-nek nevezzük. Egyszerű analógiaként a DBMS az Excel program, az adatbázis az Excel munkafüzet, a táblázat pedig egy Excel munkalap.

Az adatbázis és az Excel munkafüzet között két fő különbség van.

Az adatok bemutatása elkülönül az adatok tárolásától.

Ez azt jelenti, hogy egy adatbázisban nem lehet úgy szerkeszteni az adatokat, hogy közvetlenül az adatokhoz megyünk át és szerkesztjük azokat. (Más DBMS programok rendelkeznek olyan grafikus felhasználói felületekkel, amelyek lehetővé teszik az adatbázisban lévő adatok közvetlen elérését és szerkesztését, mint egy táblázatkezelőben. De a motorháztető alatt ez a művelet egy SQL-parancsot ad ki).

A táblák általában úgy kapcsolódnak egymáshoz, hogy kapcsolatot alkotnak.

A kapcsolatok lehetnek egy az egyhez, egy a sokhoz vagy sok a sokhoz kapcsolatok.

Az egy az egyhez kapcsolat azt jelenti, hogy “egy táblázat sora csak egy másik táblázat egy sorával áll kapcsolatban és fordítva”. Egy példa erre az alkalmazott neve az adóazonosító számhoz.

Ez a fajta kapcsolat általában egyetlen táblázatban szerepel Alkalmazottak, mivel az adatok két táblázatra való szétválasztásának nincs igazán előnye.

Az egy a sokhoz kapcsolat viszont azt jelenti, hogy “egy táblázat sora csak egy vagy több sorhoz kapcsolódik egy másik táblázatban, de fordítva nem”. Egy példa erre a Számlák a Számlasorokhoz. Egy számlának több sora is lehet, de egy számlasor csak egy adott számlához tartozik.

A sok-sok közötti kapcsolat pedig, ahogy azt már kitalálhattad, azt jelenti, hogy “egy táblázat sora csak egy vagy több sorhoz kapcsolódik egy másik táblázatban, és fordítva”. Erre példa lehet egy olyan rendszer, amely részleges kifizetéseket valósít meg.

Egy számlát részben különböző fizetési műveletekkel lehet kifizetni, és egy kifizetés részben különböző számlákat fizethet ki.

Hogyan ismeri egy adatbázis ezeket a kapcsolatokat?
Ez egyszerű. Az elsődleges és idegen kulcsok használatával.

Az elsődleges kulcsokra azért van szükség, hogy megkülönböztessük az egyik sort a másiktól. Egyedülállóan azonosítanak minden adatsort egy táblázatban.

A idegen kulcsok viszont egy másik táblázat elsődleges kulcsai. Ezért az elsődleges kulcsok és az idegen kulcsok összekapcsolásával az adatbázis kapcsolatai fennmaradnak.

Az egy a sokhoz oldal tartalmazza az elsődleges kulcsot, a “sok” pedig ezt az elsődlegeset tartalmazza idegen kulcsként. Fenti példánkban egy számla összes sorának lekérdezéséhez az InvoiceLines táblából kérdezzük le az összes számlához tartozó sort, ahol az idegen kulcs megegyezik egy adott számlaszámmal.

A sok-sok közötti kapcsolat esetében a kapcsolat két egy-sok közötti kapcsolatra bomlik egy harmadik, “joining” táblának nevezett tábla használatával. Például a részleges fizetési rendszerünkben a Számlák tábla, a Kifizetések tábla és a SzámlaKifizetések tábla lesz az összekötő tábla. Az InvoicePayments tábla elsődleges kulcsai a számlák és a kifizetések tábla elsődleges kulcsaiból álló összetett kulcsok lesznek az alábbiak szerint

.

Figyeljen arra, hogy az összekötő tábla nem tartalmaz más adatokat, mivel a számlák és a kifizetések táblák összekapcsolásán kívül más célja nincs.

Hogy megkapjuk egy bizonyos fizetési művelet, mondjuk PAY 1 által kifizetett számlákat, az összekötő táblán keresztül összekötjük a Számlák és a Kifizetések táblákat, és lekérdezzük a payment_id = "PAY 1".

Ez az adatbázis alapjai. Most már készen állunk az adatbázisunk megtervezésére.

Egy egyszerű analógia szerint az DBMS az Excel program, az adatbázis az Excel munkafüzet és a táblázat az Excel munkalap.

A Xero implementációnk megtervezése

Most, hogy megvan az alapismeretünk a Xeróról, elkezdhetjük az adatbázis szerkezetének durva vázlatának elkészítését. Vegyük figyelembe, hogy Table_Name formátumot fogok használni. Ez nagy kezdőbetűs szavakat jelent, amelyeket aláhúzással választunk el. A táblázatok neveihez szintén többes számban fogom használni a neveket.

Az értékesítési ciklushoz, a következő tábláink lesznek

• Számlák
• Ügyfelek – egy ügyfélnek sok számlája lehet, de egy számla nem tartozhat sok ügyfélhez
• Számlák_Fizetések – egyelőre emlékezzünk arra a feltételezésre, hogy a Számlák_Fizetések és a számlák között egy-többszörös kapcsolat van (nincs részleges fizetés)
• Számlák_Sorok – ez a számlák és a COA közötti összekötő tábla. Egy számla több számlán is szerepelhet, és egy számlához több számla is tartozhat.
• Számlaterv (COA)

A beszerzési ciklushoz, a következő tábláink lesznek

• Számlák
• Beszállítók – egy szállítónak több számlája is lehet, de egy számla nem tartozhat több szállítóhoz
• Számla_Fizetések – egyelőre emlékezzünk a feltételezésünkre, hogy a Számla_Fizetések és a Számlák között egy-többszörös kapcsolat van
• Számla_Sorok – ez a számlák és a COA közötti összekötő tábla. Egy számla több számlán is szerepelhet, és egy számlához több számla is tartozhat.
• COA – ugyanaz, mint a fentiekben az értékesítési ciklusban. Csak a teljesség kedvéért teszem ide.

A készpénzciklushoz, a következő táblákat fogjuk létrehozni (a fizetési táblákat már fentebb létrehoztuk)

• Received_Moneys – lehet egy opcionális Customer
• Received_Money_Lines – ez a csatlakozás. tábla a Received_Moneys és a COA között
• Spent_Moneys – lehet egy opcionális Supplier
• Spent_Money_Lines – ez az összekötő tábla a Spent_Moneys és a COA között

Koncepcionálisan, az adatbázisunk szerkezete a következő

Ezt a diagramot az adatbázis nyelvén Entity-Relationship Diagramnak vagy ERD-nek nevezik. Az egy-sok közötti kapcsolatokat az 1 – M, a sok-sok közötti kapcsolatokat pedig az M – M jelöli.

A fenti diagramon a csatlakozó táblák nem jelennek meg, mivel a sok-sok közötti kapcsolatokkal rendelkező táblákban implicit módon jelennek meg.

Változatunk SQL-ben való implementálása

Most itt az ideje, hogy modellünket SQL-ben implementáljuk. Kezdjük először néhány konvenció meghatározásával.

Az elsődleges kulcs mező/oszlop neve id lesz, az idegen kulcs pedig table_id formátumú, ahol table a “sok” oldal táblájának neve egyes számban. Például a Számlák táblában az idegen kulcs az SQL kódhoz customer_id.

Az idő lesz customer_id.

. Itt van.

Pár dolog itt:

• Az SQL parancsok nem nagy- és kisbetűsek, a CREATE TABLE ugyanaz, mint a create table
• A IF EXISTS és a IF NOT EXISTS opcionális. Én csak azért használtam őket, hogy megelőzzem a hibákat az SQL-parancsaimban. Ha például egy nem létező táblát dobok ki, az SQLite hibát fog adni. Továbbá a IF NOT EXISTS-t azért tettem a create table parancsra, hogy véletlenül se írjunk felül egy létező táblát.
• Vigyázzunk a DROP TABLE paranccsal! Egy meglévő táblát figyelmeztetés nélkül töröl, még akkor is, ha van tartalma.
• A táblázatok nevei lehetnek csupa nagybetűsek vagy nem csupa nagybetűsek is. Ha a táblázatnevek szóközöket tartalmaznak, akkor azokat backtickkel (`) kell körülvenni. A nagy- és kisbetűket nem kell megkülönböztetni. SELECT * FROM Customers megegyezik a select * from customers.

Még ha az SQL kicsit laza is a szintaxis tekintetében, törekedni kell a következetességre az SQL-kódban.

Figyeljen a fenti ERD-ben látható kapcsolatokra is. Ne feledje azt sem, hogy az idegen kulcs a sokadik oldalon van.

A sorrend fontos, mivel egyes táblák az idegen kulcs miatt függőségként szolgálnak egy másiknak. Például először az Invoice_Payments táblát kell létrehozni a Invoice tábla előtt, mivel az előbbi az utóbbi függősége. A trükk itt az, hogy az ERD széleivel kezdjük, mivel ezek azok, amelyekben a legkevesebb idegen kulcs van.

Egy mintaadatbázist is letölthetünk SQLite-ban, tartalom nélkül, erről a linkről.

A megtekintéshez használhatjuk az ingyenes és nyílt forráskódú SQLite Browser-t. Töltse le innen!

Tartalom hozzáadása az adatbázisunkhoz

Most, hogy megvan a mintaadatbázisunk, adjunk hozzá adatokat. A mintaadatok letölthetők innen – csak bontsuk le CSV-kre, ahogy szükséges.

Figyeljünk arra, hogy a kreditek pozitívumként, a kreditek pedig negatívumként jelennek meg.

Ezért a bejegyzésért csak a DB Browser importálási funkcióját használtam a CSV-k importálására a fenti Excel fájlból. Például a Customers.csv

• A Customers tábla

• Menjen a File > Import > Table from CSV file és válassza a Customers.csv

• Az adatok importálásához kattintson az Ok/Ja gombra az összes következő kérésre.

Ha kiadjuk a következő SQL parancsot, akkor meg kell jelenítenie az adatbázisunkban lévő összes Ügyfelet

Pénzügyi jelentések készítése az adatbázisunkból

Az adatbázisunk nyers számviteli rendszerként való működésének bizonyítására hozzuk létre a Tárgyidőszaki mérleget.

Az első lépés a számlák, számlák, kapott_pénzek és elköltött_pénzek tranzakcióink tranzakciós nézeteinek létrehozása. A kód a következő lesz

Az első két utasításban CTE-t használok (recursive kulcsszóval). A CTE-k hasznosak, mivel 4 táblázatot kombinálok, hogy egyetlen nézetet kapjak a számlatranzakciók és a megfelelő kifizetések számára. A CTE-kről az SQLite-ban itt tudhat meg többet.

A fenti parancs végrehajtása után az adatbázisnak a következő 4 nézetet kell tartalmaznia.

Végül létrehozzuk a Trial Balance vagy röviden TB kódját. Megjegyezzük, hogy a TB csak a tranzakcióink egyenlegeinek gyűjteménye, figyelembe véve az adatbázisunk tervezésekor lefektetett szabályokat.

A kód a következő

A fenti kód több SQL-lekérdezést tartalmaz, amelyeket a union all paranccsal kötünk össze. Minden egyes lekérdezést megjegyzésekkel láttam el, hogy megmutassam, mit próbálnak elérni.

Az első lekérdezés például megpróbálja megszerezni az összes jóváírást a számlázási tranzakciókhoz (főleg az értékesítéshez). A második a számlatranzakciók (többnyire Vásárlás) terheléseit, és így tovább.

A végrehajtás eredményeként a következő TB-t kell kapnunk.

Az Excelben ellenőrizhetjük, hogy a terhelés egyenlő-e a jóváírással (én ezt tettem). A terhelések és a jóváírások összege 14115 és -14115.

Szuper! Megcsináltad

Egy könyvelési rendszer létrehozása igazán összetett dolog. Lényegében az adatbázis-tervezés teljes skáláját végigjártuk – a fogalmaktól az ERD-n át a létrehozásig és a lekérdezésig. Veregesse meg a vállát, hogy idáig eljutott.

Figyeljen arra, hogy szándékosan korlátoztuk az adatbázisunkat, hogy inkább a fogalmakra koncentráljunk. Ezeket felemelheti, és megpróbálhat egy másikat építeni a korlátozások nélkül.

Ez az! Most már egy SQL ninja vagy! Gratulálok!

Nézze meg a Leanpubon hamarosan megjelenő második könyvemet, a Számviteli adatbázis-tervezést!

Nézze meg az első könyvemet, a PowerQuery Guide to Pandas-t is a Leanpubon.

Kövessen a Twitteren és a Linkedin-en.