Este es el consejo que siempre doy cuando alguien me pregunta cómo aprender a programar. Prácticamente, significa que tienes que resolver cosas o elegir proyectos que sean relevantes para ti – ya sea en tu trabajo o en tu vida personal.
Ir sin más a tutoriales en Youtube, leer libros de programación, copiar código de posts de Reddit, etc. no te llevará a ninguna parte si estás empezando a aprender a programar.
En este post, voy a mostrarte cómo construir una burda base de datos contable usando SQLite.
Entonces, ¿por qué crear una base de datos contable? ¿Por qué no simplemente copiar los datos públicos, meterlos en SQLite y practicar a partir de ahí?
La razón es que crear una base de datos contable es lo suficientemente avanzado como para cubrir todos los aspectos de las bases de datos y de SQL – desde las consultas hasta las uniones, pasando por las vistas y los CTEs.
Al venir de un entorno contable, creo que este es el mejor proyecto para aprender SQL. Después de todo, la programación es una herramienta para resolver problemas. Por lo tanto, bien podría «resolver» uno difícil para aprender plenamente SQL.
Me inspiré para crear un sistema de contabilidad utilizando SQL observando cómo funciona Xero. Para aquellos que no están familiarizados con él, Xero es un software de contabilidad en la nube originado en Nueva Zelanda. Ahora se ha expandido a Australia, Estados Unidos, Canadá y el Reino Unido.
Lo bueno de Xero es que tiene una interfaz agradable y limpia y un montón de aplicaciones para elegir para ampliar su funcionalidad.
Aviso: no soy un ingeniero ni desarrollador de Xero y estas observaciones pueden no corresponder exactamente a cómo funciona el sistema, ya que siempre se actualiza. Ciertamente, el SQL presentado aquí no es el diseño SQL exacto que utiliza Xero ya que su sistema necesita escalar. Pero este es un proyecto muy interesante así que hagámoslo!
Contabilidad 101
Antes de que te emociones demasiado, hagamos primero un curso acelerado de contabilidad.
La ecuación contable fundamental es
Activo = Pasivo + Patrimonio
Esa ecuación tiene básicamente tres partes
El activo son todos los recursos de la entidad.
El pasivo es lo que la empresa debe.
Y el Patrimonio, la acumulación de toda la inversión del propietario, los giros, los beneficios o las pérdidas.
El lado derecho describe cómo se han financiado los activos: a través del Pasivo o del Patrimonio Neto.
Podemos ampliar la ecuación anterior para desglosar el Patrimonio
Estas 5 cuentas -Activo, Pasivo, Patrimonio, Ingresos, &Gastos- son los tipos de cuentas que se suelen ver en un sistema contable.
Luego está el concepto de Débito y Crédito. Podría seguir y discutir estos dos en profundidad, pero para este post, todo lo que necesitas saber es que en cada transacción:
Débito = Crédito
Estas dos ecuaciones generalmente gobiernan lo que está sucediendo en todo el ciclo contable. Estas dos ecuaciones también nos servirán de guía para crear nuestra propia base de datos contable.
Al venir de una formación contable, creo que este es el mejor proyecto para aprender SQL. Al fin y al cabo, la programación es una herramienta para resolver problemas. Por lo tanto, bien podría «resolver» uno difícil para aprender completamente SQL.
La implementación de la contabilidad de Xero
Lo importante es recordar que Xero está diseñado de tal manera que será útil para los propietarios de negocios (no para los contables) en las operaciones del día a día del negocio.
Como tal, está diseñado en torno a los ciclos de las transacciones y el control interno.
Ciclos de transacción
Los ciclos de transacción básicos son los siguientes
Ciclo de ventas
Ciclo de compras
Ciclo de caja
Xero implementa estos ciclos de la siguiente manera
Ciclo de ventas Las ventas se introducen en Xero mediante facturas. Imagina que la empresa emite facturas reales en papel por las ventas (ventas al contado o a cuenta). Esto es exactamente lo que Xero quiere replicar.
Las facturas se pueden imprimir directamente desde el software y se numeran automáticamente en orden creciente.
Bajo el capó, las facturas aumentan la cuenta de Ventas y la cuenta de Deudores (AR).
Ciclo de Compras Las facturas se introducen en Xero utilizando Facturas. De nuevo, imagine que la empresa emite facturas reales por las compras (compras al contado o a cuenta). Este es el caso habitual de los servicios públicos y el inventario. Esto es también lo que Xero quiere replicar.
Las facturas se pueden imprimir directamente desde el software y se pueden utilizar para complementar cualquier procedimiento de aprobación realizado por la empresa.
Bajo el capó, las facturas aumentan la cuenta de Compras y la cuenta de Cuentas por Pagar (AP).
Ciclo de Caja Esto implica todas las transacciones que pertenecen a la Caja. Hay 4 tipos
Pagos de Facturas – pagos de las Facturas pendientes
Pagos de Facturas – pagos de las Facturas pendientes
Dinero Recibido – recibos de efectivo que no son pagos de facturas. Esto puede implicar ventas en efectivo pero si va a emitir una factura, utilice la función Facturas.
Dinero gastado – desembolsos en efectivo que no son pagos de facturas. Esto puede implicar compras en efectivo, pero si va a emitir una factura, utilice la función Facturas.
Esta es la parte de los ciclos de transacciones.
Control interno
Para el control interno, necesita entender el concepto de cuentas del sistema.
Xero tiene un artículo completo para entender las cuentas del sistema aquí. Pero para nuestros propósitos, sólo vamos a discutir las siguientes cuentas del sistema
Cuentas por cobrar
Cuentas por pagar
Cuentas bancarias (vinculadas a los feeds bancarios)
Estas cuentas no se pueden utilizar en los diarios manuales. Esto significa que Xero quiere que usted utilice Facturas, Facturas y Transacciones de Efectivo (Pagos de Facturas, Pagos de Facturas, Dinero Recibido y Dinero Gastado) para apoyar el balance de estas cuentas. Esta es la implementación de Xero de control interno si se quiere.
Por supuesto, usted puede utilizar la versión no sistema de la AR, AP, y las cuentas bancarias mediante la creación de ellos en el Plan de Cuentas (COA). Sin embargo, no puede utilizar los tipos de cuentas AR, AP y Bank para ellos.
Lo importante es recordar que Xero está diseñado de tal manera que será útil para los propietarios de negocios (no contadores) en las operaciones del día a día del negocio.
Caveat: No vamos a bajar a la madriguera del conejo
Diseñar una contabilidad es realmente complejo. Se necesitarían varias entradas del blog sólo para cubrir este tema. Por lo tanto, para simplificar, vamos a crear los siguientes supuestos (no es exactamente como Xero implementa estos)
Pagos de facturas y recibos Los pagos de facturas pueden pagar dos o más facturas en su totalidad. Es decir, no vamos a permitir pagos parciales. Lo mismo ocurre con los Pagos de Facturas.
Inventario No vamos a utilizar artículos de inventario aquí. Para las ventas o compras, vamos a utilizar la cuenta de Inventario directamente en lugar de crear artículos de inventario que se asignan a la cuenta de Inventario.
Eso es todo para nuestras suposiciones. Después de diseñar nuestra base de datos, el lector puede levantar estos supuestos y tratar de imitar a Xero tanto como sea posible.
Básicos de SQL
Ahora, antes de reconstruir nuestra versión de la estructura de la base de datos de Xero, vamos a tener un curso intensivo sobre las bases de datos.
Una base de datos es una colección de tablas. Cada tabla consta de filas de datos llamados registros. Las columnas se llaman campos.
El programa para trabajar con una base de datos se llama Sistema de Gestión de Bases de Datos o SGBD. Como una simple analogía, DBMS es a un programa de Excel, la base de datos es a un libro de trabajo de Excel y la tabla es a una hoja de trabajo de Excel.
Hay dos diferencias principales entre una base de datos y un libro de trabajo de Excel.
La presentación de los datos está separada del almacenamiento de los datos.
Es decir, no se pueden editar los datos en una base de datos yendo directamente a los datos y editándolos. (Otros programas DBMS tienen GUIs que le permiten acceder directamente a los datos en una base de datos y editarlos como una hoja de cálculo. Pero bajo el capó, esa acción emite un comando SQL).
Las tablas suelen estar vinculadas entre sí para formar una relación.
Las relaciones pueden ser de uno a uno, de uno a muchos o de muchos a muchos.
La relación de uno a uno significa que «una fila de la tabla está relacionada con una sola fila de otra tabla y viceversa». Un ejemplo sería el nombre del empleado con el número de identificación fiscal.
Este tipo suele incluirse en una sola tabla Empleados ya que realmente no hay ningún beneficio de separar los datos en dos tablas.
Una-a-muchos por otro lado significa «una fila de la tabla está relacionada con sólo una o más filas en otra tabla, pero no viceversa». Un ejemplo es el de Facturas a Líneas de Factura. Una factura puede tener varias líneas, pero una línea de factura sólo pertenece a una factura en particular.
Y como habrás adivinado, many-to-many significa «una fila de la tabla está relacionada con sólo una o más filas en otra tabla y viceversa». Un ejemplo sería un sistema que implementa pagos parciales.
Una factura puede ser pagada parcialmente por diferentes transacciones de pago y un pago puede pagar parcialmente diferentes facturas.
¿Cómo una base de datos conoce estas relaciones? Es sencillo. Es mediante el uso de claves primarias y foráneas.
Las claves primarias son necesarias para distinguir una fila de otra. Identifican de forma única cada fila de datos en una tabla.
Las claves foráneas, por otro lado, son claves primarias de otra tabla. Por lo tanto, al relacionar las claves primarias y las claves foráneas, se persisten las relaciones de la base de datos.
Para uno-a-muchos, el lado «uno» contiene la clave primaria y el «muchos» contiene esta primaria como su clave foránea. En nuestro ejemplo anterior, para obtener todas las líneas que pertenecen a una factura, consultamos la tabla InvoiceLines donde la clave foránea es igual a un número de factura en particular.
Para muchos a muchos, la relación se descompone en dos relaciones de uno a muchos mediante el uso de una tercera tabla llamada tabla de «unión». Por ejemplo, nuestro sistema de pagos parciales tendrá la tabla Facturas, la tabla Pagos y la tabla FacturasPagos como tabla de unión. Las claves primarias de la tabla FacturasPagos serán una clave compuesta formada por las claves primarias de la tabla Facturas y Pagos de la siguiente manera
Tablas para implementar pagos parciales de Facturas
Tenga en cuenta que la tabla de unión no contiene ningún otro dato ya que no tiene ningún otro propósito aparte de unir las tablas de Facturas y Pagos.
Para obtener las facturas pagadas por una determinada operación de pago, digamos PAY 1, unimos las tablas Facturas y Pagos a través de la tabla de unión y consultamos por el payment_id = "PAY 1".
Eso es todo para los fundamentos de una base de datos. Ahora estamos listos para diseñar nuestra base de datos.
Como una simple analogía, DBMS es al programa de Excel, la base de datos es al libro de Excel y la tabla es a una hoja de cálculo de Excel.
Diseñando nuestra implementación de Xero
Ahora que tenemos una comprensión básica de Xero para empezar a crear un boceto de su estructura de base de datos. Tenga en cuenta que voy a utilizar el formato Table_Name. Es decir, palabras con mayúsculas separadas por guiones bajos. También voy a usar nombres pluralizados para los nombres de las tablas.
Para el ciclo de ventas, vamos a tener las siguientes tablas
Facturas
Clientes – un cliente puede tener muchas facturas pero una factura no puede pertenecer a muchos clientes
Factura_Pagos – recuerda nuestra suposición por ahora de que hay una relación de uno a muchos entre Factura_Pagos y Facturas respectivamente (no hay pagos parciales)
Factura_Líneas – esta es la tabla de unión entre Facturas y COA. Una cuenta puede aparecer en varias facturas y una factura puede tener varias cuentas.
Tabla de Cuentas (COA)
Para el Ciclo de Compras, vamos a tener las siguientes tablas
Facturas
Proveedores – un proveedor puede tener muchas facturas pero una factura no puede pertenecer a muchos proveedores
Facturas_Pagos – recuerde nuestra suposición por ahora de que hay una relación de uno a muchos entre Facturas_Pagos y Facturas respectivamente
Facturas_Líneas – esta es la tabla de unión entre Facturas y COA. Una cuenta puede aparecer en varias facturas y una factura puede tener varias cuentas.
COA – lo mismo que lo anterior en el Ciclo de Ventas. Sólo poner aquí para completar.
Para el Ciclo de Caja, vamos a tener las siguientes tablas (tablas de pago que ya hemos creado arriba)
Received_Money – puede tener un Cliente opcional
Received_Money_Lines – esta es la tabla de unión Tabla de unión entre Received_Money y COA
Spent_Moneys – puede tener un Supplier
Spent_Money_Lines – es la tabla de unión entre Spent_Moneys y COA
Conceptualmente, la estructura de nuestra base de datos es la siguiente
Modelo de base de datos contable
Este diagrama se denomina Diagrama Entidad-Relación o ERD en la jerga de las bases de datos. Las relaciones de uno a muchos se designan con 1 – M y de muchos a muchos con M – M.
Las tablas de unión no se muestran en el diagrama anterior ya que están implícitas en las tablas con relaciones de muchos a muchos.
Implementación de nuestra versión en SQL
Ahora es el momento de implementar nuestro modelo en SQL. Empecemos por definir primero algunas convenciones.
La clave primaria tendrá nombre de campo/columna de id, y la clave foránea tendrá el formato table_id donde table es el nombre de la tabla del lado «muchos» en singular. Por ejemplo, en la tabla Facturas, la clave foránea será customer_id.
Hora para el código SQL. Aquí está.
Un par de cosas aquí:
Los comandos SQL no distinguen entre mayúsculas y minúsculas, CREATE TABLE es lo mismo que create table
Los IF EXISTS y IF NOT EXISTS son opcionales. Sólo los he utilizado para evitar errores en mis comandos SQL. Por ejemplo, si elimino una tabla inexistente, SQLite dará un error. Además, pongo IF NOT EXISTS en el comando create table para que no anulemos accidentalmente ninguna tabla existente.
¡Cuidado con el comando DROP TABLE! Eliminará una tabla existente sin avisar aunque tenga contenido.
Los nombres de las tablas también pueden escribirse en mayúsculas o no. Si los nombres de las tablas tienen espacios, deben ir acompañados de puntos suspensivos (`). No se distingue entre mayúsculas y minúsculas. SELECT * FROM Customers es lo mismo que select * from customers.
Aunque SQL es un poco relajado con respecto a la sintaxis, debe esforzarse por mantener la coherencia en su código SQL.
Tome nota también de las relaciones mostradas en el ERD anterior. Recuerde también que la clave foránea está en el lado de los muchos.
El orden es importante ya que algunas tablas sirven como una dependencia a otra debido a la clave foránea. Por ejemplo, tiene que crear primero Invoice_Payments antes de la tabla Invoice ya que la primera es una dependencia de la segunda. El truco aquí es empezar por los bordes del ERD ya que son los que tienen el menor número de claves foráneas.
También puedes descargar una base de datos de ejemplo en SQLite sin contenido en este enlace.
Para visualizarla, puedes utilizar el SQLite Browser gratuito y de código abierto. Descárgalo aquí.
Añadir contenido a nuestra base de datos
Ahora que tenemos la base de datos de ejemplo, vamos a introducir datos en ella. Los datos de la muestra se pueden descargar desde aquí – sólo hay que dividirlos en CSVs según sea necesario.
Tenga en cuenta que los créditos se muestran como positivos y los créditos como negativos.
Para este post, acabo de utilizar la función de importación de DB Browser para importar CSVs desde el archivo de Excel anterior. Por ejemplo, para importar Customers.csv
Seleccione la tabla Customers
Vaya a File > Import > Table from CSV file and choose the Customers.csv
Haga clic en Ok/Sí a todas las indicaciones sucesivas para importar los datos.
Si emite el siguiente comando SQL, debería mostrar todos los Clientes de nuestra base de datos
Creación de informes financieros a partir de nuestra base de datos
Para demostrar que nuestra base de datos funciona como un burdo sistema contable, vamos a crear el Balance de Comprobación.
El primer paso es crear las vistas de las transacciones para nuestras Facturas, Facturas, Dinero_Recibido y Dinero_Gastado. El código será el siguiente
En las dos primeras sentencias, estoy utilizando un CTE (con la palabra clave recursive). Los CTEs son útiles ya que estoy combinando 4 tablas para obtener una única vista de las transacciones de la factura y los pagos correspondientes. Puedes aprender más sobre CTEs en SQLite aquí.
Después de ejecutar el comando anterior, tu base de datos debería tener las siguientes 4 vistas.
Vistas de transacciones
Por último, creamos el código para el Balance de Comprobación o TB para abreviar. Tened en cuenta que el TB no es más que una colección de los saldos de nuestras transacciones teniendo en cuenta las reglas que establecimos cuando diseñamos nuestra base de datos.
El código es el siguiente
El código anterior contiene múltiples consultas SQL unidas por el comando union all. He anotado cada consulta para mostrar lo que cada uno está tratando de lograr.
Por ejemplo, la primera consulta trata de obtener todos los créditos de las transacciones de Facturas (en su mayoría Ventas). La segunda para los débitos de las transacciones de Facturas (en su mayoría Compras) y así sucesivamente.
Ejecutarla debería dar como resultado el siguiente TB.
Puedes poner esto en Excel para comprobar que los débitos son iguales a los créditos (lo que yo hice). Los débitos y créditos totales son 14115 y -14115 respectivamente.
¡Hola! Lo has conseguido
Crear un sistema contable es realmente complejo. Esencialmente, hemos explorado toda la gama de diseño de bases de datos, desde los conceptos hasta el ERD, pasando por la creación y la consulta. Date una palmadita en la espalda por haber llegado hasta aquí.
Toma nota de que hemos limitado deliberadamente nuestra base de datos para centrarnos más en los conceptos. Puedes levantar estos y tratar de construir otra sin las restricciones.
¡Eso es todo! ¡Ahora eres un ninja de SQL! Enhorabuena!
Foto de Ian Stauffer en Unsplash
¡Consulta mi segundo libro Diseño de bases de datos contables que pronto se publicará en Leanpub!
Mira también mi primer libro PowerQuery Guide to Pandas en Leanpub.
