Comment construire un système de comptabilité en utilisant SQLite

.

Scratch your own itch.

C’est le conseil que je donne toujours quand quelqu’un me demande comment apprendre la programmation. En pratique, cela signifie que vous devez résoudre des choses ou choisir des projets qui sont pertinents pour vous – que ce soit dans votre travail ou dans votre vie personnelle.

Aller indéfiniment voir des tutoriels sur Youtube, lire des livres de programmation, copier du code à partir de posts Reddit, etc. ne vous mènera nulle part si vous commencez à apprendre la programmation.

Dans ce post, je vais vous montrer comment construire une base de données comptable brute en utilisant SQLite.

Alors, pourquoi créer une base de données comptable ? Pourquoi ne pas simplement copier les données publiques, les pousser vers SQLite, et pratiquer à partir de là ?

La raison est que la création d’une base de données comptable est assez avancée pour couvrir tous les aspects des bases de données et de SQL – des requêtes aux jointures en passant par les vues et les CTE.

Venant d’un milieu comptable, je pense que c’est le meilleur projet pour apprendre SQL. Après tout, la programmation est un outil pour résoudre des problèmes. Par conséquent, autant en « résoudre » un difficile pour apprendre pleinement SQL.

J’ai eu l’inspiration de créer un système comptable en utilisant SQL en observant comment Xero fonctionne. Pour ceux qui ne le connaissent pas, Xero est un logiciel de comptabilité en nuage originaire de Nouvelle-Zélande. Il s’est maintenant étendu à l’Australie, aux États-Unis, au Canada et au Royaume-Uni.

La bonne chose à propos de Xero est qu’il a une belle interface propre et beaucoup d’applications à choisir pour étendre ses fonctionnalités.

Disclaimer : Je ne suis pas un ingénieur ni un développeur chez Xero et ces observations peuvent ne pas correspondre exactement à la façon dont le système fonctionne car il est toujours mis à jour. Certainement, le SQL présenté ici n’est pas la conception SQL exacte que Xero utilise car leur système doit évoluer. Mais c’est un projet très intéressant, alors faisons-le !

Comptabilité 101

Avant de trop vous exciter, faisons d’abord un cours accéléré sur la comptabilité.

L’équation comptable fondamentale est

Actifs = Passifs + Capitaux propres

Cette équation comporte essentiellement trois parties

• Les actifs sont toutes les ressources de l’entité.
• Les passifs sont ce que l’entreprise doit.
• et les capitaux propres, l’accumulation de tous les investissements, les tirages, les bénéfices ou les pertes du propriétaire.

La partie droite décrit comment les actifs ont été financés – soit par les Passifs, soit par les Capitaux propres.

Nous pouvons étendre l’équation ci-dessus pour décomposer les capitaux propres

Actifs = Passifs + capitaux propres d’ouverture + revenus – dépenses

Ces 5 comptes – actifs, passifs, capitaux propres, revenus, & dépenses – sont les types de comptes que vous voyez généralement dans un système comptable.

Puis il y a le concept de débit et de crédit. Je pourrais continuer et discuter de ces deux en profondeur mais pour ce post, tout ce que vous devez savoir est que dans chaque transaction :

Débit = Crédit

Ces deux équations régissent généralement ce qui se passe dans l’ensemble du cycle comptable. Ces deux équations serviront également de guide pour créer notre propre base de données comptables.

Venant d’un milieu comptable, je pense que c’est le meilleur projet pour apprendre SQL. Après tout, la programmation est un outil pour résoudre des problèmes. Par conséquent, autant en « résoudre » un difficile pour apprendre pleinement SQL.

La mise en œuvre de la comptabilité par Xero

La chose importante à retenir est que Xero est conçu de telle sorte qu’il sera utile aux propriétaires d’entreprises (et non aux comptables) dans les opérations quotidiennes de l’entreprise.

En tant que tel, il a été conçu autour des cycles de transaction et du contrôle interne.

Cycles de transaction

Les cycles de transaction de base sont les suivants

• Cycle des ventes
• Cycle des achats
• Cycle de la trésorerie

Xero met en œuvre ces cycles comme suit

Cycle des ventes
Les ventes sont saisies dans Xero à l’aide de factures. Imaginez que l’entreprise émette de véritables factures papier pour les ventes (ventes au comptant ou sur compte). C’est exactement ce que Xero veut reproduire.

Les factures peuvent être imprimées directement à partir du logiciel et elles sont automatiquement numérotées par ordre croissant.

Sous le capot, les factures augmentent le compte des ventes et le compte des créances (AR).

Cycle des achats
Les factures sont saisies dans Xero à l’aide des factures. Là encore, imaginez que l’entreprise émette des factures réelles pour des achats (achats au comptant ou sur compte). C’est le cas habituel pour les services publics et les stocks. C’est également ce que Xero veut reproduire.

Les factures peuvent être imprimées directement à partir du logiciel et peuvent être utilisées pour compléter toute procédure d’approbation effectuée par l’entreprise.

Sous le capot, les factures augmentent le compte des achats et le compte des comptes fournisseurs (AP).

Cycle de la trésorerie
Ce cycle implique toutes les transactions relatives à la trésorerie. Il en existe 4 types

• Paiements de factures – paiements des factures en cours
• Paiements de factures – paiements des factures en cours
• Monnaie reçue – rentrées d’argent qui ne sont pas des paiements de factures. Cela peut impliquer des ventes au comptant mais si vous devez émettre une facture, utilisez la fonction Factures.
• Argent dépensé – décaissements en espèces qui ne sont pas des paiements de factures. Cela peut impliquer des achats en espèces mais si vous allez émettre une facture, utilisez la fonctionnalité Factures.

C’est la partie sur les cycles de transaction.

Contrôle interne

Pour le contrôle interne, vous devez comprendre le concept des comptes système.

Xero a un article complet pour comprendre les comptes système ici. Mais pour nos besoins, nous allons seulement discuter des comptes système suivants

• Comptes clients
• Comptes fournisseurs
• Comptes bancaires (liés aux flux bancaires)

Ces comptes ne peuvent pas être utilisés dans les journaux manuels. Cela signifie que Xero veut que vous utilisiez les factures, les factures et les transactions en espèces (paiements de factures, paiements de factures, argent reçu et argent dépensé) pour soutenir le solde de ces comptes. C’est la mise en œuvre du contrôle interne par Xero, si vous voulez.

Bien sûr, vous pouvez utiliser la version non système des comptes AR, AP et Banque en les créant dans le plan comptable (COA). Cependant, vous ne pouvez pas utiliser les types de comptes AR, AP et Banque pour eux.

La chose importante à retenir est que Xero est conçu de telle manière qu’il sera utile aux propriétaires d’entreprises (et non aux comptables) dans les opérations quotidiennes de l’entreprise.

Caveat : nous ne descendons pas dans le trou du lapin

Concevoir une comptabilité est vraiment complexe. Il faudrait plusieurs articles de blog juste pour couvrir ce sujet. Par conséquent, pour la simplicité, nous allons créer les hypothèses suivantes (pas exactement comment Xero les met en œuvre)

• Paiements de factures et de factures
  Les paiements de factures peuvent payer deux ou plusieurs factures entièrement. Ce qui veut dire qu’on ne va pas autoriser les paiements partiels. Il en va de même pour les paiements de factures.
• Inventaire
  Nous n’allons pas utiliser d’éléments d’inventaire ici. Pour les ventes ou les achats, nous allons utiliser directement le compte Inventaire plutôt que de créer des articles d’inventaire qui ont mappé sur le compte Inventaire.

C’est tout pour nos hypothèses. Après avoir conçu notre base de données, le lecteur peut lever ces hypothèses et essayer d’imiter Xero autant que possible.

Bases de SQL

Maintenant, avant de reconstruire notre version de la structure de la base de données de Xero, ayons un cours intensif sur les bases de données.

Une base de données est une collection de tables. Chaque table est constituée de lignes de données appelées enregistrements. Les colonnes sont appelées champs.

Le programme permettant de travailler avec une base de données est appelé système de gestion de base de données ou SGBD. Par simple analogie, le SGBD est un programme Excel, la base de données est un classeur Excel et la table est une feuille de calcul Excel.

Il y a deux différences principales entre une base de données et un classeur Excel.

La présentation des données est séparée du stockage des données.

Ce qui signifie que vous ne pouvez pas modifier les données dans une base de données en allant directement sur les données et en les modifiant. (D’autres programmes de SGBD ont des interfaces graphiques qui vous permettent d’accéder directement aux données dans une base de données et de les modifier comme une feuille de calcul. Mais sous le capot, cette action émet une commande SQL).

Les tables sont généralement liées les unes aux autres pour former une relation.

Les relations peuvent être de type un à un, un à plusieurs ou plusieurs à plusieurs.

La relation un à un signifie « une ligne de la table est liée à une seule ligne d’une autre table et vice versa ». Un exemple serait le nom de l’employé au numéro d’identification fiscale.

Ce type est généralement inclus dans une seule table Employés car il n’y a vraiment aucun avantage à séparer les données en deux tables.

Un-à-plusieurs d’autre part signifie « une ligne de la table est liée à seulement une ou plusieurs lignes dans une autre table, mais pas vice versa ». Un exemple est celui des Invoices to InvoiceLines. Une facture peut avoir plusieurs lignes mais une ligne de facture n’appartient qu’à une facture particulière.

Et comme vous l’avez peut-être deviné, many-to-many signifie « une ligne de table est liée à seulement une ou plusieurs lignes dans une autre table et vice versa ». Un exemple serait un système qui met en œuvre des paiements partiels.

Une facture peut être payée partiellement par différentes transactions de paiement et un paiement peut payer partiellement différentes factures.

Comment une base de données connaîtrait ces relations ?
C’est simple. C’est par l’utilisation de clés primaires et étrangères.

Les clés primaires sont nécessaires pour distinguer une ligne d’une autre. Elles identifient de manière unique chaque ligne de données dans une table.

Les clés étrangères, quant à elles, sont des clés primaires provenant d’une autre table. Par conséquent, en mettant en relation les clés primaires et les clés étrangères, les relations de la base de données sont persistées.

Pour one-to-many, le côté « un » contient la clé primaire et le côté « many » contient cette primaire comme clé étrangère. Dans notre exemple ci-dessus, pour obtenir toutes les lignes appartenant à une facture, nous interrogeons la table InvoiceLines où la clé étrangère est égale à un numéro de facture particulier.

Pour le many to many, la relation est décomposée en deux relations one to many grâce à l’utilisation d’une troisième table appelée table « joining ». Par exemple, notre système de paiement partiel aura la table Invoices, la table Payments et la table InvoicePayments comme table de jonction. Les clés primaires de la table InvoicePayments seront une clé composite composée des clés primaires de la table Invoices et Payments comme suit

.

Prenez note que la table de jonction ne contient aucune autre donnée car elle n’a pas d’autre but que de joindre les tables Factures et Paiements.

Pour obtenir les factures payées par une certaine transaction de paiement, disons PAY 1, nous joignons les tables Factures et Paiements à travers la table de jonction et nous faisons une requête pour le payment_id = "PAY 1".

C’est tout pour les bases d’une base de données. Nous sommes maintenant prêts à concevoir notre base de données.

Par analogie simple, le SGBD est au programme Excel, la base de données est au classeur Excel et la table est à une feuille de calcul Excel.

Conception de notre implémentation de Xero

Maintenant que nous avons une compréhension de base de Xero pour commencer à créer une ébauche de sa structure de base de données. Prenez note que je vais utiliser le format Table_Name. Il s’agit de mots en majuscules séparés par des traits de soulignement. Je vais également utiliser des noms au pluriel pour les noms de tables.

Pour le cycle de vente, nous allons avoir les tables suivantes

• Factures
• Clients – un client peut avoir plusieurs factures mais une facture ne peut pas appartenir à plusieurs clients
• Paiements_factures – rappelez-vous notre hypothèse pour l’instant qu’il y a une relation un à plusieurs entre les Paiements_factures et les Factures respectivement (pas de paiements partiels)
• Lignes_factures – c’est la table de jonction entre les Factures et l’ACO. Un compte peut apparaître dans plusieurs factures et une facture peut avoir plusieurs comptes.
• Plateau des comptes (COA)

Pour le cycle des achats, nous aurons les tables suivantes

• Factures
• Fournisseurs – un fournisseur peut avoir plusieurs factures mais une facture ne peut pas appartenir à plusieurs fournisseurs
• Factures_Paiements – souvenez-vous de notre hypothèse pour l’instant qu’il y a une relation un à plusieurs entre les factures et les paiements respectivement
• Factures_Lignes – c’est la table de jonction entre les factures et le COA. Un compte peut apparaître dans plusieurs factures et une facture peut avoir plusieurs comptes.
• COA – même chose que ce qui précède dans le cycle de vente. Juste mettre ici pour l’exhaustivité.

Pour le cycle de trésorerie, nous aurons les tables suivantes (les tables de paiement que nous avons déjà créées ci-dessus)

• Monnaie reçue – peut avoir un client optionnel
• Lignes de monnaie reçue – c’est la table de jonction entre la monnaie reçue et les lignes de monnaie. Received_Moneys – peut avoir un fournisseur facultatif
• Spent_Moneys – peut avoir un fournisseur facultatif
• Spent_Money_Lines – c’est la table de jonction entre Spent_Moneys et COA

Conceptuellement, notre structure de base de données est la suivante

Ce diagramme est appelé diagramme entité-relation ou ERD dans le langage des bases de données. Les relations un à plusieurs sont désignées par 1 – M et plusieurs à plusieurs par M – M.

Les tables de jonction ne sont pas représentées dans le diagramme ci-dessus car elles sont implicites dans les tables avec des relations plusieurs à plusieurs.

Implémentation de notre version en SQL

Maintenant il est temps d’implémenter notre modèle en SQL. Commençons d’abord par définir quelques conventions.

La clé primaire aura un nom de champ/colonne de id, et la clé étrangère aura le format table_id où table est le nom de la table du côté « many » au singulier. Par exemple, dans la table Factures, la clé étrangère sera customer_id.

Heure pour le code SQL. Le voici.

Une ou deux choses ici:

• Les commandes SQL ne sont pas sensibles à la casse, CREATE TABLE est identique à create table
• Les IF EXISTS et IF NOT EXISTS sont facultatifs. Je les ai juste utilisés pour éviter les erreurs dans mes commandes SQL. Par exemple, si je dépose une table inexistante, SQLite donnera une erreur. De même, je mets IF NOT EXISTS sur la commande create table pour ne pas écraser accidentellement une table existante.
• Faites attention avec la commande DROP TABLE ! Elle supprimera une table existante sans avertissement même si elle a du contenu.
• Les noms de table peuvent également être écrits tout en majuscules ou non. Si les noms de table comportent des espaces, ils doivent être entourés de barres obliques inversées (`). Ils ne sont pas sensibles à la casse. SELECT * FROM Customers est identique à select * from customers.

Même si SQL est un peu relax en ce qui concerne la syntaxe, vous devez vous efforcer de maintenir la cohérence dans votre code SQL.

Prenez également note des relations indiquées dans l’ERD ci-dessus. Rappelez-vous également que la clé étrangère se trouve du côté des nombreux.

L’ordre est important car certaines tables servent de dépendance à une autre en raison de la clé étrangère. Par exemple, vous devez créer d’abord Invoice_Payments avant la table Invoice car la première est une dépendance de la seconde. L’astuce ici est de commencer par les bords de l’ERD car ce sont ceux qui ont le moins de clés étrangères.

Vous pourriez également télécharger une base de données exemple en SQLite sans contenu dans ce lien.

Pour la visualiser, vous pouvez utiliser le navigateur SQLite gratuit et open-source. Téléchargez-le ici !

Ajouter du contenu à notre base de données

Maintenant que nous avons la base de données échantillon, entrons-y des données. Les données de l’échantillon peuvent être téléchargées ici – il suffit de les décomposer en CSV selon les besoins.

Prenez note que les crédits sont affichés comme positifs et les crédits comme négatifs.

Pour ce post, j’ai juste utilisé la fonctionnalité d’importation de DB Browser pour importer les CSV à partir du fichier Excel ci-dessus. Par exemple, pour importer Customers.csv

• Sélectionner la table Customers

• Aller dans File > Import > Table from CSV file et choisir Customers.csv

• Cliquez sur Ok/Yes à toutes les invites successives pour importer les données.

Si vous émettez la commande SQL suivante, elle devrait montrer tous les clients de notre base de données

Création de rapports financiers à partir de notre base de données

Pour prouver que notre base de données fonctionne comme un système comptable brut, créons la balance de vérification.

La première étape consiste à créer les vues de transaction pour nos factures, nos factures, les transactions Received_Moneys et Spent_Moneys. Le code sera le suivant

Dans les deux premières déclarations, j’utilise un CTE (avec le mot-clé recursive). Les CTE sont utiles car je combine 4 tables pour obtenir une vue unique pour les transactions de factures et les paiements correspondants. Vous pourriez en apprendre plus sur les CTEs dans SQLite ici.

Après avoir exécuté la commande ci-dessus, votre base de données devrait avoir les 4 vues suivantes.

Enfin, nous créons le code pour la balance de vérification ou TB pour faire court. Notez que la TB est juste une collection des soldes de nos transactions en prenant note des règles que nous avons établies lorsque nous avons conçu notre base de données.

Le code est le suivant

Le code ci-dessus contient plusieurs requêtes SQL jointes par la commande union all. J’ai annoté chaque requête pour montrer ce que chacune essaie d’obtenir.

Par exemple, la première requête essaie d’obtenir tous les crédits des transactions de la Facture (principalement les Ventes). La seconde pour les débits des transactions de Factures (principalement des Achats) et ainsi de suite.

L’exécuter devrait donner le TB suivant.

Vous pouvez mettre cela dans Excel pour vérifier que les débits sont égaux aux crédits (ce que j’ai fait). Le total des débits et des crédits est respectivement de 14115 et -14115.

Hourra ! Vous avez réussi

Créer un système comptable est vraiment complexe. Nous avons essentiellement exploré toute la gamme de la conception d’une base de données – des concepts à l’ERD en passant par la création et l’interrogation de celle-ci. Félicitez-vous d’être arrivé aussi loin.

Prenez note que nous avons délibérément limité notre base de données pour nous concentrer davantage sur les concepts. Vous pouvez les lever et essayer d’en construire une autre sans les restrictions.

C’est tout ! Vous êtes maintenant un ninja SQL ! Félicitations!

Voyez mon deuxième livre Accounting Database Design bientôt publié sur Leanpub !

Chez aussi mon premier livre PowerQuery Guide to Pandas sur Leanpub.

Suivez-moi sur Twitter et Linkedin.