Come costruire un sistema di contabilità usando SQLite

Gratta il tuo prurito.

Questo è il consiglio che do sempre quando qualcuno mi chiede come imparare a programmare. In pratica, significa che devi risolvere cose o scegliere progetti che sono rilevanti per te – sia nel tuo lavoro che nella tua vita personale.

Andare senza sosta ai tutorial su Youtube, leggere libri di programmazione, copiare codice dai post di Reddit, ecc. non ti porterà da nessuna parte se stai iniziando a imparare la programmazione.

In questo post, ti mostrerò come costruire un rozzo database di contabilità usando SQLite.

Allora, perché creare un database di contabilità? Perché non copiare semplicemente i dati pubblici, spingerli in SQLite e fare pratica da lì?

La ragione è che creare un database di contabilità è abbastanza avanzato da coprire tutti gli aspetti dei database e dell’SQL – dalle query alle join alle viste e alle CTE.

Venendo da un background di contabilità, penso che questo sia il miglior progetto per imparare l’SQL. Dopo tutto, la programmazione è uno strumento per risolvere problemi. Quindi, tanto vale “risolverne” uno difficile per imparare completamente SQL.

Ho avuto l’ispirazione di creare un sistema di contabilità usando SQL osservando come funziona Xero. Per coloro che non lo conoscono, Xero è un software di contabilità cloud nato in Nuova Zelanda. Ora si è espanso in Australia, Stati Uniti, Canada e Regno Unito.

La cosa buona di Xero è che ha una bella interfaccia pulita e un sacco di applicazioni tra cui scegliere per estendere le sue funzionalità.

Disclaimer: non sono un ingegnere né uno sviluppatore di Xero e queste osservazioni potrebbero non corrispondere esattamente a come funziona il sistema che è sempre aggiornato. Certamente, l’SQL presentato qui non è l’esatto design SQL che Xero usa in quanto il loro sistema ha bisogno di scalare. Ma questo è un progetto molto interessante, quindi facciamolo!

Contabilità 101

Prima di eccitarti troppo, facciamo un corso accelerato sulla contabilità.

L’equazione contabile fondamentale è

Attività = Passività + Patrimonio netto

Questa equazione ha fondamentalmente tre parti

• Le attività sono tutte le risorse dell’entità.
• Le passività sono ciò che l’azienda deve.
• e il Patrimonio netto, l’accumulo di tutti gli investimenti del proprietario, disegni, profitti o perdite.

Il lato destro descrive come le attività sono state finanziate – o attraverso le passività o il patrimonio netto.

Possiamo espandere l’equazione di cui sopra per scomporre il Patrimonio

Attività = Passività + Patrimonio iniziale + Ricavi – Spese

Questi 5 conti – Attività, Passività, Patrimonio, Ricavi, & Spese – sono i tipi di conto che si vedono tipicamente in un sistema contabile. Potrei continuare a discutere questi due in modo approfondito, ma per questo post, tutto quello che dovete sapere è che in ogni transazione:

Debit = Credit

Queste due equazioni governano generalmente ciò che accade nell’intero ciclo contabile. Queste due equazioni serviranno anche come guida per creare il nostro database di contabilità.

Venendo da un background di contabilità, penso che questo sia il miglior progetto per imparare SQL. Dopo tutto, la programmazione è uno strumento per risolvere problemi. Quindi, potrebbe anche “risolverne” uno difficile per imparare completamente SQL.

L’implementazione di Xero della contabilità

La cosa importante da ricordare è che Xero è progettato in modo tale che sarà utile agli imprenditori (non ai contabili) nelle operazioni quotidiane del business.

Come tale, è progettato intorno ai cicli delle transazioni e al controllo interno.

Cicli di transazione

I cicli di transazione di base sono i seguenti

• Ciclo delle vendite
• Ciclo degli acquisti
• Ciclo della cassa

Xero implementa questi cicli come segue

Ciclo delle vendite
Le vendite sono inserite in Xero usando le fatture. Immagina l’azienda che emette fatture cartacee per le vendite (vendite in contanti o in conto). Questa è l’esatta cosa che Xero vuole replicare.

Le fatture possono essere stampate direttamente dal software e sono automaticamente numerate in ordine crescente.

Sotto il cofano, le fatture aumentano il conto delle vendite e il conto dei crediti (AR).

Ciclo degli acquisti
Le fatture sono inserite in Xero usando le fatture. Di nuovo, immagina che l’azienda emetta fatture effettive per gli acquisti (acquisti in contanti o in conto). Questo è il solito caso per le utenze e l’inventario. Questa è anche la cosa che Xero vuole replicare.

Le fatture possono essere stampate direttamente dal software e possono essere usate per integrare qualsiasi procedura di approvazione fatta dall’azienda.

Sotto il cofano, le fatture aumentano il conto Acquisti e il conto Debitori (AP).

Ciclo di cassa
Questo coinvolge tutte le transazioni relative alla cassa. Ci sono 4 tipi

• Pagamenti delle fatture – pagamenti delle fatture in sospeso
• Pagamenti delle bollette – pagamenti delle bollette in sospeso
• Denaro ricevuto – ricevute in contanti che non sono pagamenti di fatture. Questo può comportare vendite in contanti ma se devi emettere una fattura, usa la funzione Fatture.
• Denaro speso – esborsi in contanti che non sono pagamenti di fatture. Questo può comportare acquisti in contanti ma se stai per emettere una fattura, usa la funzione Fatture.

Questa è la parte sui cicli di transazione.

Controllo interno

Per il controllo interno, devi capire il concetto di conti di sistema.

Xero ha un articolo completo per capire i conti di sistema qui. Ma per i nostri scopi, discuteremo solo i seguenti conti di sistema

• Conti attivi
• Conti passivi
• Conti bancari (collegati a Bank Feeds)

Questi conti non possono essere usati nei giornali manuali. Questo significa che Xero vuole che tu usi Fatture, Bollette e Transazioni di cassa (Pagamenti di fatture, Pagamenti di bollette, Denaro ricevuto e Denaro speso) per supportare il saldo di questi conti. Questa è l’implementazione di Xero del controllo interno, se vuoi.

Ovviamente, puoi usare la versione non di sistema dei conti AR, AP, e Banca creandoli nella Chart of Accounts (COA). Tuttavia, non è possibile utilizzare i tipi di conto AR, AP e Banca per loro.

La cosa importante da ricordare è che Xero è progettato in modo tale che sarà utile ai proprietari di imprese (non ai contabili) nelle operazioni quotidiane del business.

Caveat: Non stiamo andando giù per la tana del coniglio

Progettare una contabilità è davvero complesso. Ci vorrebbero più post nel blog solo per coprire questo argomento. Quindi, per semplicità, creeremo le seguenti ipotesi (non esattamente come Xero le implementa)

• Pagamenti per fatture e bollette
  Pagamenti per fatture possono pagare due o più fatture in modo completo. Cioè, non permetteremo pagamenti parziali. Lo stesso vale per Bill Payments.
• Inventario
  Non useremo elementi di inventario qui. Per le vendite o gli acquisti, useremo direttamente il conto Inventario piuttosto che creare degli elementi di inventario che siano mappati sul conto Inventario.

Questo è tutto per le nostre ipotesi. Dopo aver progettato il nostro database, il lettore può sollevare queste ipotesi e provare ad imitare Xero il più possibile.

Fondamenti di SQL

Ora, prima di ricostruire la nostra versione della struttura del database di Xero, facciamo un corso accelerato sui database.

Un database è una collezione di tabelle. Ogni tabella consiste di righe di dati chiamati record. Le colonne sono chiamate campi.

Il programma per lavorare con un database è chiamato Database Management System o DBMS. Come semplice analogia, il DBMS è un programma Excel, il database è una cartella di lavoro Excel e la tabella è un foglio di lavoro Excel.

Ci sono due differenze principali tra un database e una cartella di lavoro Excel.

La presentazione dei dati è separata dalla memorizzazione dei dati.

Si intende che non è possibile modificare i dati in un database andando direttamente sui dati e modificandoli. (Altri programmi DBMS hanno GUI che permettono di accedere direttamente ai dati in un database e modificarli come un foglio di calcolo. Ma sotto il cofano, quell’azione emette un comando SQL).

Le tabelle sono di solito collegate tra loro per formare una relazione.

Le relazioni possono essere uno-a-uno, uno-a-molti, o molti-a-molti.

Relazioni uno-a-uno significa “una riga della tabella è collegata solo a una riga di un’altra tabella e viceversa”. Un esempio potrebbe essere il nome del dipendente al numero di identificazione fiscale.

Questo tipo è di solito incluso in una singola tabella Dipendenti poiché non c’è davvero alcun beneficio nel separare i dati in due tabelle.

Uno-a-molti, invece, significa “una riga della tabella è collegata solo a una o più righe di un’altra tabella ma non viceversa”. Un esempio è Fatture a InvoiceLines. Una fattura può avere più righe, ma una linea di fattura appartiene solo a una particolare fattura.

E come avrete capito, molti-a-molti significa “una riga di tabella è collegata solo a una o più righe di un’altra tabella e viceversa”. Un esempio potrebbe essere un sistema che implementa i pagamenti parziali.

Una fattura può essere pagata parzialmente da diverse transazioni di pagamento e un pagamento può pagare parzialmente diverse fatture.

Come fa un database a conoscere queste relazioni?
È semplice. È attraverso l’uso di chiavi primarie e straniere.

Le chiavi primarie sono necessarie per distinguere una riga dall’altra. Identificano in modo unico ogni riga di dati in una tabella.

Le chiavi straniere, invece, sono chiavi primarie di un’altra tabella. Quindi, mettendo in relazione le chiavi primarie e le chiavi esterne, le relazioni del database sono persistenti.

Per uno a molti, il lato “uno” contiene la chiave primaria e il “molti” contiene questa primaria come chiave esterna. Nel nostro esempio precedente, per ottenere tutte le righe appartenenti a una fattura, interroghiamo la tabella InvoiceLines dove la chiave esterna è uguale a un particolare numero di fattura.

Per molti a molti, la relazione è suddivisa in due relazioni uno a molti attraverso l’uso di una terza tabella chiamata tabella “di unione”. Per esempio, il nostro sistema di pagamento parziale avrà la tabella Fatture, la tabella Pagamenti e la tabella InvoicePayments come tabella di unione. Le chiavi primarie della tabella InvoicePayments saranno una chiave composta dalle chiavi primarie della tabella Invoices e Payments come segue

Si noti che la tabella di unione non contiene altri dati in quanto non ha nessun altro scopo a parte unire le tabelle Fatture e Pagamenti.

Per ottenere le fatture pagate da una certa transazione di pagamento, diciamo PAY 1, uniamo le tabelle Fatture e Pagamenti attraverso la tabella di unione e interroghiamo il payment_id = "PAY 1".

Ecco le basi di un database. Ora siamo pronti a progettare il nostro database.

Come semplice analogia, il DBMS è il programma Excel, il database è la cartella di lavoro Excel e la tabella è un foglio di lavoro Excel.

Progettare la nostra implementazione di Xero

Ora che abbiamo una comprensione di base di Xero per iniziare a creare uno schizzo della struttura del database. Prendete nota che userò il formato Table_Name. Si tratta di parole in maiuscolo-prima lettera separate da underscore. Userò anche nomi pluralizzati per i nomi delle tabelle.

Per il ciclo di vendita, avremo le seguenti tabelle

• Fatture
• Clienti – un cliente può avere molte fatture ma una fattura non può appartenere a molti clienti
• Fattura_Pagamenti – ricordate la nostra assunzione per ora che c’è una relazione uno-a-molti tra Fattura_Pagamenti e Fatture rispettivamente (nessun pagamento parziale)
• Invoice_Lines – questa è la tabella di unione tra Fatture e COA. Un conto può apparire in più fatture e una fattura può avere più conti.
• Carta dei conti (COA)

Per il ciclo degli acquisti, avremo le seguenti tabelle

• Fatture
• Fornitori – un fornitore può avere molte fatture ma una fattura non può appartenere a molti fornitori
• Bill_Payments – ricordate la nostra assunzione per ora che c’è una relazione uno-a-molti tra Bill_Payments e Bills rispettivamente
• Bill_Lines – questa è la tabella di unione tra Bills e COA. Un conto può apparire in più fatture e una fattura può avere più conti.
• COA – lo stesso con quanto detto sopra nel Ciclo di vendita. Metto qui solo per completezza.

Per il Ciclo di Cassa, avremo le seguenti tabelle (tabelle di pagamento già create sopra)

• Received_Moneys – può avere un cliente opzionale
• Received_Money_Lines – questa è la tabella tra Received_Moneys e COA
• Spent_Moneys – può avere un opzionale Supplier
• Spent_Money_Lines – questa è la tabella di unione tra Spent_Moneys e COA

Conceptually, la struttura del nostro database è la seguente

Questo diagramma è chiamato Entity-Relationship Diagram o ERD nel linguaggio dei database. Le relazioni uno-a-molti sono designate da 1 – M e molte-a-molti da M – M.

Le tabelle di unione non sono mostrate nel diagramma di cui sopra perché sono implicite nelle tabelle con relazioni molti-a-molti.

Implementazione della nostra versione in SQL

Ora è il momento di implementare il nostro modello in SQL. Cominciamo prima a definire alcune convenzioni.

La chiave primaria avrà il nome del campo/colonna id, e la chiave esterna avrà il formato table_id dove table è il nome della tabella del lato “molti” in forma singolare. Per esempio, nella tabella Fatture, la chiave esterna sarà customer_id.

Time per il codice SQL. Eccolo qui.

Un paio di cose qui:

• I comandi SQL non sono sensibili alle maiuscole, CREATE TABLE è uguale a create table
• Il IF EXISTS e IF NOT EXISTS sono opzionali. Li ho usati solo per evitare errori nei miei comandi SQL. Per esempio, se faccio cadere una tabella non esistente, SQLite darà un errore. Inoltre, ho messo IF NOT EXISTS sul comando crea tabella in modo da non sovrascrivere accidentalmente una tabella esistente.
• Fate attenzione al comando DROP TABLE! Cancellerà una tabella esistente senza preavviso anche se ha dei contenuti.
• I nomi delle tabelle possono anche essere scritti tutto maiuscolo o no. Se i nomi delle tabelle hanno degli spazi, devono essere racchiusi tra parentesi quadre (`). Non sono sensibili alle maiuscole e alle minuscole. SELECT * FROM Customers è uguale a select * from customers.

Anche se l’SQL è un po’ rilassato per quanto riguarda la sintassi, dovresti cercare di mantenere la coerenza nel tuo codice SQL.

Prendi nota anche delle relazioni mostrate nell’ERD sopra. Ricordate anche che la chiave esterna è sul lato molti.

L’ordine è importante perché alcune tabelle servono come dipendenza di un’altra a causa della chiave esterna. Per esempio, dovete creare prima Invoice_Payments prima della tabella Invoice perché la prima è una dipendenza della seconda. Il trucco qui è di iniziare con i bordi dell’ERD perché sono quelli con il minor numero di chiavi esterne.

Potresti anche scaricare un database di esempio in SQLite senza contenuto in questo link.

Per visualizzarlo, puoi usare il SQLite Browser gratuito e open-source. Scaricalo qui!

Aggiungimento di contenuti al nostro database

Ora che abbiamo il database di esempio, inseriamo i dati in esso. I dati di esempio possono essere scaricati da qui – basta dividerli in CSV come necessario.

Prendete nota che i crediti sono mostrati come positivi e i crediti come negativi.

Per questo post, ho solo usato la funzione di importazione di DB Browser per importare CSV dal file Excel di cui sopra. Per esempio, per importare Customers.csv

• Seleziona la tabella Customers

• Vai su File > Import > Table from CSV file e scegli Customers.csv

• Clicca Ok/Yes a tutte le successive richieste per importare i dati.

Se dai il seguente comando SQL, dovrebbe mostrare tutti i clienti nel nostro database

Creazione di rapporti finanziari dal nostro database

Per provare che il nostro database funziona come un sistema contabile grezzo, creiamo il bilancio di prova.

Il primo passo è quello di creare le viste delle transazioni per le nostre fatture, fatture, denaro ricevuto e denaro speso. Il codice sarà il seguente

Nelle prime due dichiarazioni, sto usando una CTE (con la parola chiave recursive). Le CTE sono utili perché sto combinando 4 tabelle per ottenere un’unica vista per le transazioni Invoice e i pagamenti corrispondenti. Puoi saperne di più sulle CTE in SQLite qui.

Dopo aver eseguito il comando di cui sopra, il tuo database dovrebbe avere le seguenti 4 viste.

Infine, creiamo il codice per il Trial Balance o TB in breve. Notate che il TB è solo una raccolta dei saldi delle nostre transazioni prendendo nota delle regole che abbiamo stabilito quando abbiamo progettato il nostro database.

Il codice è il seguente

Il codice qui sopra contiene più query SQL unite dal comando union all. Ho annotato ogni query per mostrare ciò che ognuna cerca di ottenere.

Per esempio, la prima query cerca di ottenere tutti i crediti per le transazioni di Fattura (soprattutto Vendite). La seconda per gli addebiti delle transazioni di Fattura (principalmente Acquisti) e così via.

Eseguendola si dovrebbe ottenere il seguente TB.

Si può mettere questo in Excel per controllare che i debiti siano uguali ai crediti (cosa che ho fatto). Il totale dei debiti e dei crediti è 14115 e -14115 rispettivamente.

Ora! Ce l’hai fatta

Creare un sistema contabile è davvero complesso. Abbiamo essenzialmente esplorato l’intera gamma della progettazione di database – dai concetti all’ERD alla creazione e all’interrogazione. Datevi una pacca sulla spalla per essere arrivati fin qui.

Prendete nota che abbiamo deliberatamente limitato il nostro database per concentrarci di più sui concetti. Puoi sollevarlo e provare a costruirne un altro senza le restrizioni.

Ecco fatto! Ora sei un ninja dell’SQL! Congratulazioni!

Guarda il mio secondo libro Accounting Database Design che sarà presto pubblicato su Leanpub!

Guarda anche il mio primo libro PowerQuery Guide to Pandas su Leanpub.

Seguimi su Twitter e Linkedin.