Como Construir um Sistema de Contabilidade usando SQLite

Raspe a sua própria comichão.

Este é o conselho que dou sempre que alguém me pergunta como aprender programação. Praticamente, isso significa que você tem que resolver coisas ou escolher projetos que são relevantes para você – seja no seu trabalho ou na sua vida pessoal.

Sem dúvida, ir a tutoriais no Youtube, ler livros de programação, copiar código de posts Reddit, etc. não vai levar a lugar nenhum se você está começando a aprender programação.

Neste post, vou mostrar como construir uma base de dados de contabilidade bruta usando SQLite.

Então, por que criar uma base de dados de contabilidade? Porque não apenas copiar dados públicos, empurrá-los para SQLite, e praticar a partir daí?

A razão é que a criação de uma base de dados de contabilidade é avançada o suficiente para cobrir todos os aspectos das bases de dados e SQL – desde consultas a joinins até views e CTEs.

Com base em contabilidade, penso que este é o melhor projecto para aprender SQL. Afinal, a programação é uma ferramenta para resolver problemas. Assim, mais vale “resolver” um difícil de aprender SQL.

I got the inspiration to create an accounting system using SQL by observing how Xero works. Para aqueles que não estão familiarizados com ele, Xero é um software de contabilidade em nuvem originado na Nova Zelândia. Ele agora se expandiu para Austrália, EUA, Canadá e Reino Unido.

O bom do Xero é que ele tem uma interface limpa e um monte de aplicativos para escolher para estender sua funcionalidade.

Disclaimer: Eu não sou engenheiro nem desenvolvedor no Xero e essas observações podem não corresponder exatamente a como o sistema funciona, pois ele está sempre atualizado. Certamente, o SQL aqui apresentado não é o projeto SQL exato que Xero usa como o seu sistema precisa para escalar. Mas este é um projeto muito interessante, então vamos fazê-lo!

Contagem 101

Antes de se entusiasmar demais, vamos fazer um curso intensivo primeiro sobre contabilidade.

A equação fundamental da contabilidade é

Assets = Passivo + Capital Próprio

Essa equação tem basicamente três partes

• Assets são todos os recursos da entidade.
• As responsabilidades são o que a empresa deve.
• e Capital Próprio, a acumulação de todos os investimentos, desenhos, lucros ou perdas do proprietário.

O lado direito descreve como os activos foram financiados – seja através do Passivo ou do Capital Próprio.

Podemos expandir a equação acima para decompor o Capital Próprio

Assets = Passivo + Capital Próprio Inicial + Receitas – Despesas

Estas 5 contas – Ativos, Passivos, Capital Próprio, Receitas, & Despesas – são os tipos de contas que você normalmente vê em um sistema contábil.

Então existe o conceito de Débito e Crédito. Eu poderia continuar e discutir estes dois em profundidade, mas para este post, tudo que você precisa saber é que em cada transação:

Débito = Crédito

Estas duas equações geralmente governam o que está acontecendo em todo o ciclo contábil. Estas duas equações também servirão como um guia na criação da nossa própria base de dados contabilística.

Posto de um fundo contabilístico, penso que este é o melhor projecto para aprender SQL. Afinal, a programação é uma ferramenta para resolver problemas. Portanto, mais vale “resolver” um difícil de aprender SQL.

Xero’s Implementation of Accounting

O importante a lembrar é que Xero foi projetado de tal forma que será útil para os proprietários de negócios (não contadores) nas operações do dia-a-dia do negócio.

Como tal, foi desenhado em torno de ciclos de transacções e controlo interno.

Ciclos de transação

Os ciclos básicos de transação são os seguintes

• Ciclo de vendas
• Ciclo de compras
• Ciclo de caixa

Xero implementa estes ciclos da seguinte forma

Ciclo de vendas
Vendas são introduzidas em Xero usando Facturas. Imagine o negócio emitindo facturas reais em papel para vendas (vendas a dinheiro ou por conta). Isto é exactamente o que Xero quer replicar.

As facturas podem ser impressas directamente do software e são automaticamente numeradas em ordem crescente.

Acima do capô, as facturas aumentam a conta de Vendas e a conta de Clientes (AR).

Ciclo de Compras
As facturas são introduzidas em Xero utilizando Facturas. Novamente, imagine o negócio emitindo contas reais para compras (compras à vista ou por conta). Este é o caso habitual dos serviços públicos e do Inventário. Isto também é o que Xero quer replicar.

As contas podem ser impressas diretamente do software e podem ser usadas para complementar qualquer procedimento de aprovação feito pelo negócio.

Abaixo do capô, as contas aumentam a conta Compras e a conta Contas a Pagar (AP).

Ciclo de Caixa
Aplicação de todas as transações referentes a Caixa. Existem 4 tipos

• Pagamentos de Faturas – pagamentos das faturas pendentes
• Pagamentos de Contas – pagamentos das faturas pendentes
• Recebimentos de Dinheiro – recebimentos de dinheiro que não são pagamentos de faturas. Isso pode envolver vendas em dinheiro, mas se você for emitir uma fatura, use o recurso Faturas.
• Dinheiro gasto – pagamentos em dinheiro que não são pagamentos de faturas. Isto pode envolver compras em dinheiro, mas se você vai emitir uma fatura, use o recurso de Contas.

Esta é a parte dos ciclos de transação.

Controle Interno

Para controle interno, você precisa entender o conceito de contas do sistema.

Xero tem um artigo abrangente para entender as contas do sistema aqui. Mas para os nossos propósitos, vamos discutir apenas as seguintes contas do sistema

• Contas a Receber
• Contas a Pagar
• Contas Bancárias (ligadas a Feeds Bancárias)

Estas contas não podem ser usadas em Revistas Manuais. Isto significa que Xero quer que você use Faturas, Contas e Transações à vista (Pagamentos de Faturas, Pagamentos de Contas, Dinheiro Recebido e Dinheiro Gastado) para suportar o saldo dessas contas. Esta é a implementação de controle interno de Xero se você for.

Obviamente, você pode usar a versão não-sistema das contas AR, AP, e Banco, criando-as no Plano de Contas (COA). No entanto, você não pode usar o AR, AP, e tipos de contas bancárias para eles.

O importante a lembrar é que Xero é projetado de tal forma que será útil aos proprietários de negócios (não contadores) nas operações diárias do negócio.

Caveat: We’re Not Going Down The Rabbit Hole

Desenhar uma contabilidade é realmente complexo. Serão necessários vários posts no blog apenas para cobrir este tópico. Assim, por simplicidade, vamos criar as seguintes suposições (não exatamente como Xero as implementa)

• Pagamentos de Faturas e Contas
  Pagamentos de Faturas podem pagar duas ou mais faturas integralmente. Ou seja, não vamos permitir pagamentos parciais. O mesmo é verdade com Pagamentos de Faturas.
• Inventário
  Não vamos usar itens de inventário aqui. Para vendas ou compras, vamos usar a conta de Inventário diretamente em vez de criar itens de inventário mapeados para a conta de Inventário.

É isso para as nossas suposições. Após projetar nossa base de dados, o leitor pode levantar essas suposições e tentar imitar Xero o máximo possível.

Base doSQL

Agora antes de reconstruir nossa versão da estrutura da base de dados do Xero, vamos fazer um curso intensivo sobre bases de dados.

Uma base de dados é uma coleção de tabelas. Cada tabela consiste em linhas de dados chamadas registros. As colunas são chamadas campos.

O programa para trabalhar com um banco de dados é chamado de Sistema de Gerenciamento de Banco de Dados ou SGBD. Como uma analogia simples, SGBD é para o programa Excel, banco de dados é para a pasta de trabalho Excel e tabela é para uma planilha Excel.

Existem duas diferenças principais entre um banco de dados e uma pasta de trabalho Excel.

Apresentação de dados é separada do armazenamento de dados.

Meaning, não é possível editar os dados em um banco de dados, indo diretamente para os dados e editando-os. (Outros programas do SGBD têm GUIs que permitem acessar diretamente os dados em uma base de dados e editá-los como uma planilha de cálculo. Mas sob a capa, essa ação emite um comando SQL).

Tables are usually linked to each other to form a relationship.

Relationships can be one-to-one, one-to-many, or many-to-many.

One-to-one relationship means “a table row is related to only one row in another table and vice versa”. Um exemplo seria o nome do empregado para o número de identificação fiscal.

Este tipo é normalmente incluído em uma única tabela Empregados, pois não há realmente nenhum benefício em separar os dados em duas tabelas.

Um-para-muitos na mão significa “uma linha da tabela está relacionada a apenas uma ou mais linhas em outra tabela, mas não vice versa”. Um exemplo é o Invoices to InvoiceLines. Uma factura pode ter várias linhas, mas uma linha de facturação pertence apenas a uma determinada factura.

E como já deve ter adivinhado, muitas significa “uma linha da tabela está relacionada apenas com uma ou mais linhas noutra tabela e vice-versa”. Um exemplo seria um sistema que implementa pagamentos parciais.

Uma factura pode ser paga parcialmente por diferentes transacções de pagamento e um pagamento pode pagar diferentes facturas parcialmente.

Como é que uma base de dados conhece estas relações?
É simples. É através do uso de chaves primárias e estrangeiras.

As chaves primárias são necessárias para distinguir uma linha da outra. Elas identificam exclusivamente cada linha de dados em uma tabela.

As chaves estrangeiras, por outro lado, são chaves primárias de outra tabela. Assim, relacionando as chaves primárias e as chaves estrangeiras, as relações do banco de dados são persistidas.

Para um-para-muitos, o lado “um” contém a chave primária e o lado “muitos” contém esta chave primária como sua chave estrangeira. No nosso exemplo acima, para obter todas as linhas pertencentes a uma fatura, consultamos a tabela Linhas de Fatura onde a chave estrangeira é igual a um determinado número de fatura.

Para muitas a muitas, a relação é dividida em duas relações de uma para muitas, através do uso de uma terceira tabela chamada tabela “join”. Por exemplo, nosso sistema de pagamento parcial terá a tabela Faturas, tabela Pagamentos e tabela Faturas-Pagamentos como a tabela de junção. As chaves primárias da tabela de Faturas-Pagamentos serão uma chave composta composta pelas chaves primárias da tabela de Faturas e Pagamentos da seguinte forma

Notem que a tabela de adesão não contém quaisquer outros dados, uma vez que não tem qualquer outra finalidade para além da adesão às tabelas de Facturas e Pagamentos.

Para obter as facturas pagas por uma determinada operação de pagamento, digamos PAY 1, juntamos as tabelas Facturas e Pagamentos através da tabela de junção e consultamos para a tabela de junção payment_id = "PAY 1".

É tudo para os princípios básicos de uma base de dados. Estamos agora prontos para projetar nosso banco de dados.

Como uma simples analogia, SGBD é para o programa Excel, banco de dados é para a pasta de trabalho Excel e tabela é para uma planilha Excel.

Desenhando nossa implementação do Xero

Agora temos um entendimento básico do Xero para começarmos a criar um esboço rudimentar de sua estrutura de banco de dados. Note que eu vou usar o formato Table_Name. Isso é maiúsculo-primeira letra separada por sublinhados. Também vou usar nomes pluralizados para os nomes das tabelas.

Para o ciclo de vendas, vamos ter as seguintes tabelas

• Facturas
• Clientes – um cliente pode ter muitas facturas mas uma factura não pode pertencer a muitos clientes
• Factura_Pagamentos – lembre-se da nossa suposição, por agora, de que existe uma relação de um para muitos entre Factura_Pagamentos e Facturas, respectivamente (sem pagamentos parciais)
• Linhas_Facturas – esta é a tabela de junção entre Facturas e COA. Uma conta pode aparecer em várias faturas e uma fatura pode ter várias contas.
• Ciclo de Contas (COA)

Para o Ciclo de Compras, vamos ter as seguintes tabelas

• Contas
• Fornecedores – um fornecedor pode ter muitas contas mas uma conta não pode pertencer a muitos fornecedores
• Contas_Pagamentos – lembre-se da nossa suposição por enquanto de que há uma relação de um-para-muitos entre Contas_Pagamentos e Contas respectivamente
• Contas_Linhas – esta é a tabela de junção entre Contas e COA. Uma conta pode aparecer em múltiplas contas e uma conta pode ter múltiplas contas.
• COA – o mesmo com as contas acima no Ciclo de Vendas. Basta colocar aqui para completar.

Para o Ciclo de Vendas, vamos ter as seguintes tabelas (Tabelas de pagamento já criadas acima)

• Received_Moneys – pode ter um Cliente opcional
• Received_Money_Lines – esta é a adesão tabela entre Received_Moneys e COA
• Spent_Moneys – pode ter um Fornecedor opcional
• Spent_Money_Lines – esta é a tabela de junção entre Spent_Moneys e COA

Conceptualmente, A nossa estrutura de base de dados é a seguinte

Este diagrama chama-se Diagrama de Relacionamento Entidade ou ERD na linguagem da base de dados. As relações um-para-muitos são designadas por 1 – M e muitos-para-muitos por M – M.

As tabelas de junção não são mostradas no diagrama acima, pois elas estão implícitas nas tabelas com muitos-para-muitos relacionamentos.

Implementando nossa versão em SQL

Agora é hora de implementar nosso modelo em SQL. Vamos começar por definir algumas convenções primeiro.

A chave primária terá o nome do campo/coluna de id, e a chave estrangeira terá o formato table_id onde table é o nome da tabela do lado “muitos” em forma singular. Por exemplo, na tabela Facturas, a chave estrangeira será customer_id.

Tempo para o código SQL. Aqui está.

Um par de coisas aqui:

• Os comandos doSQL não são sensíveis a maiúsculas/minúsculas, CREATE TABLE é o mesmo que create table
• Os IF EXISTS e IF NOT EXISTS são opcionais. Eu só os usei para evitar erros nos meus comandos SQL. Por exemplo, se eu deixar cair uma tabela inexistente, o SQLite dará um erro. Também coloquei IF NOT EXISTS no comando create table para que não substituamos acidentalmente nenhuma tabela existente.
• Tenha cuidado com o comando DROP TABLE! Ele irá apagar uma tabela existente sem aviso prévio mesmo que ela tenha conteúdo.
• Nomes de mesa também podem ser escritos em maiúsculas ou não. Se os nomes das tabelas tiverem espaços, eles devem ser fechados com barras de fundo (`). Eles não são sensíveis a maiúsculas e minúsculas. SELECT * FROM Customers é o mesmo que select * from customers.

Even embora SQL seja um pouco relaxante em relação à sintaxe, você deve se esforçar para manter a consistência no seu código SQL.

Toma nota também das relações mostradas na ERD acima. Lembre-se também que a chave estrangeira está em muitos lados.

Order é importante, pois algumas tabelas servem como uma dependência de outra por causa da chave estrangeira. Por exemplo, é necessário criar primeiro a Fatura_Pagamentos antes da tabela Fatura, já que a primeira é uma dependência da segunda. O truque aqui é começar com as bordas da ERD pois são as que têm o menor número de chaves estrangeiras.

Você também pode baixar uma base de dados de exemplo em SQLite sem conteúdo neste link.

Para visualizá-la, você pode usar o SQLite Browser gratuito e de código aberto. Faça o download aqui!

Adicionando conteúdo ao nosso banco de dados

Agora temos o banco de dados de amostra, vamos inserir dados nele. Os dados de amostra podem ser baixados daqui – basta quebrar para CSVs conforme necessário.

Notem que os créditos são mostrados como positivos e os créditos como negativos.

Para este post, eu usei o recurso de importação do DB Browser para importar CSVs do arquivo Excel acima. Por exemplo, para importar Clientes.csv

• Selecionar a tabela Clientes

• Ir para Arquivo > Importar > Tabela do arquivo CSV e escolher os Clientes.csv

2300>

• Clique Ok/Sim para todas as solicitações de importação de dados.

Se você emitir o seguinte comando SQL, ele deve mostrar todos os Clientes em nossa base de dados

Criar Relatórios Financeiros de Nossa Base de Dados

Para provar que nossa base de dados funciona como um sistema de contabilidade bruta, vamos criar o Balancete de Experiência.

O primeiro passo é criar as visões de transações para nossas faturas, contas, recebimentos_de_money, e transações de_money_despendido. O código será o seguinte

Nos dois primeiros comandos, estou usando um CTE (com a palavra-chave recursive). Os CTEs são úteis já que estou combinando 4 tabelas para obter uma visão única das transações de Fatura e dos pagamentos correspondentes. Você poderia aprender mais sobre as CTEs no SQLite aqui.

Após executar o comando acima, sua base de dados deverá ter as 4 vistas seguintes.

Finalmente, nós criamos o código para o Balancete ou TB para abreviar. Note que TB é apenas uma coleção dos saldos das nossas transações tomando nota das regras que estabelecemos quando projetamos nossa base de dados.

O código é o seguinte

O código acima contém múltiplas consultas SQL unidas pelo comando union all. Anotei cada consulta para mostrar o que cada uma está tentando alcançar.

Por exemplo, a primeira consulta tenta obter todos os créditos para as transações de Nota Fiscal (principalmente Vendas). A segunda para os débitos das transações de Faturamento (principalmente Compras) e assim por diante.

Executar deve resultar na seguinte TB.

Pode colocar isso no Excel para verificar se os débitos são iguais aos créditos (o que eu fiz). O total de débitos e créditos são 14115 e -14115 respectivamente.

Hooray! Você fez isto

Criar um sistema de contabilidade é realmente complexo. Exploramos essencialmente toda a gama do design da base de dados – desde os conceitos à ERD, passando pela criação, até à consulta. Dê uma palmadinha nas costas para chegar até aqui.

Note que nós deliberadamente limitamos nossa base de dados para focar mais nos conceitos. Você pode levantá-los e tentar construir outro sem as restrições.

É isso aí! Agora você é um ninja SQL! Parabéns!

Verifiquem meu segundo livro Accounting Database Design a ser publicado em breve no Leanpub!

Cheque também meu primeiro livro PowerQuery Guide to Pandas no Leanpub.

Siga-me no Twitter e no Linkedin.