Los Sistemas de facturación están en la mayoría de Tiendas, Supermercados, Almacenes, y otras Organizaciones comerciales, para Optimizar el Proceso de Venta y los Reportes de flujo de ingresos. Este tipo de sistemas se caracterizan por almacenar los datos de los clientes que compran en la empresa, emitir facturas generadas en base a la cantidad de productos que han sido comprados por los clientes, manejar inventarios y muchas funciones mas.
En este artículo estudiaremos el diseño de una base de datos para un sistema de facturación, a través de un enfoque generalizado. Veremos que entidades, atributos y relaciones existen y como normalizar las anomalías ocurrentes.
Diseño De La Base De Datos
La primera tarea es identificar todas las entidades posibles que vayan a intervenir en el proceso de facturación de la empresa. Normalmente estas entidades ya están definidas previamente en el Modelo de datos para la elaboración de los Componentes de Software.
También puedes explorar en los Requerimientos funcionales, Historias de usuario, Reglas de negocio, el Formato de las Facturas emitidas, los Diseños de formularios y Reportes.
A continuación te mostraré algunas recopilaciones descriptivas para modelar la base de datos:
- Registrar el Id, Nombre, Apellido, Dirección, Fecha de nacimiento, Teléfono y el Correo electrónico de los clientes de la compañía.
- Registrar para los productos la siguiente información: Código, Nombre, Precio, Número de Existencias y Categoría a la que pertenece.
- Se debe especificar en la factura los Datos del cliente y una tabla que muestre la Especificación del tipo de producto comprado, su precio, la cantidad suministrada y el total parcial. Al final de la factura debe calcularse el valor antes de impuestos y descuentos, y luego calcular el valor total de la compra.
- Un cliente puede pagar con las siguientes formas de pago: Efectivo, Tarjeta de crédito, Tarjeta débito, Paypal y Neteller.
- Un cliente puede generar varias facturas debido a sus distintas compras, pero jamás una misma factura podrá haber sido generada por más de un cliente.
- En una factura pueden contener varios productos vinculados, al igual que todos los productos están posibilitados a aparecer en todas las facturas.
- Un cliente puede pagar el monto total de una factura con varios métodos de pago.
- …
Estas condiciones pueden cambiar dependiendo de las necesidades del cliente, ya que pueden variar sus procesos de facturación o añadir más atributos relevantes para ellos. Pero para los objetivos de este articulo usaremos solo las características esenciales.
Crear El Modelo Relacional A Partir De Los Requerimientos
Bueno, es sencillo pensar en que primero están las personas que dan vida al negocio, es decir, la entidad CLIENTE. Luego viene la satisfacción de las necesidades del cliente a través del PRODUCTO. Y también es necesario entregar una FACTURA para constatar la entrega del producto.
Por el momento CLIENTE, FACTURA y PRODUCTO son las entidades más relevantes, cuya información debe ser almacenada en la base de datos. Veamos el Diagrama Relacional preliminar para comprender este caso:
He puesto los signos de interrogación en las llaves foráneas de FACTURA y PRODUCTO debido a que esta relación muchos a muchos es compleja. Para entenderla quiero mostrarte las partes de una factura con un sencillo diseño perteneciente a una Tienda de artículos para Computadoras.
La cabecera de la factura(Recuadro AMARILLO) contiene los datos del cliente y las característica de la factura. Al final(Recuadro AZUL) se obtiene el resultado total de toda la compra. Como ves en el detalle(Recuadro ROJO) se encuentra una tabla que muestra que productos han sido comprados por el cliente en esta ocasión, la cual representa la relación de factura y producto.
Recuerda que cuando se presenta una relación muchos a muchos debemos reemplazar esa relación por una tabla intermediaria que reciba las claves principales de ambas tablas referenciadas. Ademas de eso, las tablas originales deben tener una relación uno a muchos con la tabla intermedia. Esto permite optimizar y normalizar los datos.
Así que crearemos una nueva tabla llamada DETALLE en alusión a cada renglón detallado en la factura. Veamos como quedaría el diagrama ahora:
¿Por qué se usa la llave compuesta PK= {id_factura, num_detalle} en vez de PK= {id_factura, id_producto}?
Para diferenciar el orden de los items en la factura. Es decir, que al declarar “El tercer producto de la factura N” podamos entender rápidamente a que nos referimos. Algo que visualmente podría ser poco intuitivo de notar con la otra llave candidata.
Considera el siguiente ejemplo de una Tienda de Artículos de Deporte. A continuación se muestra la tabla DETALLE con tres facturas distintas:
| num_detalle | id_factura | id_producto | cantidad | precio |
|---|---|---|---|---|
| 1 | 101 | SDA | 2 | 3 |
| 2 | 101 | SDB | 3 | 5 |
| 1 | 102 | SDA | 1 | 3 |
| 1 | 103 | SDC | 4 | 7 |
Fíjate que en la factura 101 podemos distinguir que el primer item registrado es el producto con código «SDA«, ya que num_detalle es igual a 1. Y que el producto con código «SDB» esta ubicado en el segundo renglón detallado de la factura.
Si la clave primaria fuera id_factura + id_producto no tendríamos claro el orden de los productos comprados. Es por eso que agregar el atributo num_detalle es tan beneficioso para nosotros.
¿Por qué esta el precio del producto en DETALLE y en PRODUCTO al mismo tiempo?
A primera impresión parece redundancia de datos al extremo, pero hay un secreto escondido. La razón por la cual indujimos esta redundancia fue por que los precios de los productos varían en el mercado.
¿Importante?…
¡Claro!, imagina que un Almacén de Ropa está vendiendo la camisa de la selección de Fútbol en 10USD. Debido a que al equipo le está yendo muy bien en los últimos partidos, muchas personas vienen a comprar la camisa. Al dueño se le ocurre subir a 12USD el precio de la camisa, ya que la demanda así lo está imponiendo.
Al final de mes la Tienda de Ropa ejecuta un reporte para ver el ingreso total por ventas, donde se muestra que la cantidad de dinero contado no coincide con el valor del sistema.
¿Que pasó?
Miremos algunos cálculos pretenciosos antes y después del cambio de precio:
| Antes de la manifestación aumentada de demanda |
|---|
| Precio = 10USD |
| Unidades vendidas = 200 |
| Ingresos por ventas esperados = 2000USD |
| Después de la manifestación aumentada de demanda |
|---|
| Precio = 12USD |
| Unidades vendidas = 250 |
| Ingresos por ventas esperados = 3000USD |
| Ingresos por ventas totales esperados = 5000USD |
Los resultados anteriores serían los resultados ideales, pero al no incluir el precio del artículo en la tabla DETALLE, sucederá lo siguiente a la hora de calcular los ingresos totales:
| Cálculo de los ingresos con el precio actual |
| ARTICULO.precio = 12USD |
| Unidades vendidas = 450 |
| Ingresos totales por ventas = 450 x 12USD = 5400USD |
¡Son 400USD de más!, es decir, tu Software está produciendo un error del 8% en el ingreso total por ventas. Algo desastroso para tu reputación. Al no incluir este atributo has calculado los ingresos con el precio actual que refleja ARTICULO.precio, por lo cual todas las unidades valen lo mismo.
No obstante, si incluyes el precio en DETALLE sucederá lo siguiente:
| Calculo de los ingresos con el precio de la ocasión |
|---|
| DETALLE.precio= 10USD |
| Unidades vendidas =200 |
| Ingresos parciales por venta = 2000USD |
| DETALLE.precio = 12USD |
| Unidades vendidas = 350 |
| Ingresos parciales por venta = 3000USD |
| Ingresos totales por ventas = 5000USD |
Con esta redundancia inducida puedes tener el cálculo exacto para cada producto en el momento actual, porque DETALLE cumple la función de mantener el mismo estado para cada renglón en la factura a lo largo del tiempo.
¿Amigo y por qué subtotal y total no son atributos de FACTURA?
Porque son atributos derivados. Es decir, podemos calcularlos a través del precio del producto y la cantidad cuando queramos. Guardarlos en nuestra base de datos sería espacio de almacenamiento adicional.
En cambio, si realizamos los cálculos en tiempo real, a nuestra aplicación solo le tomaría un pequeño instante de tiempo que no interferiría con la velocidad de nuestro Software Comercial. Es un sacrificio de tiempo vs memoria muy efectivo.
Crear tablas aisladas para Atributos Multivaluados
Darnos cuenta que el atributo categoría de la entidad PRODUCTO es poco eficiente. Esto se debe a que la categoría se repetirá un sinnúmero de veces por todos los registros que tengamos(redundancia).
Veamos un ejemplo con los productos de un Supermercado:
| id_producto | nombre | precio | stock | categoría |
|---|---|---|---|---|
| SDA | X-box 360 | 500 | 100 | TECNO |
| SDB | Caja de huevos | 5 | 230 | COMIDA |
| SDC | Aceite de girasol | 6 | 219 | COMIDA |
| SDD | Coca-cola 200ml | 2,3 | 341 | BEBIDA |
De los 4 hay 2 que repiten la categoría COMIDA. ¿Que tal si hubiesen 1000 productos?, la cantidad de espacio usado para almacenar VARCHARS repetidos sería muy grande.
La solución es sencilla, expandiremos nuestro diagrama para quitar el atributo categoría de la entidad PRODUCTO y agregaremos una nueva tabla llamada CATEGORIA. Esta modificación añadirá una relación uno a muchos entre producto y categoría, por lo cual incluiríamos la llave foránea de categoría.
¿Ventajas?
- Mayor claridad e integridad en la base de datos
- Economizar espacio en disco. Rinde mejor el valor de una llave foránea ‘1‘ que el nombre de la categoría ‘COMIDA‘.
- Añadir nuevos atributos como por ejemplo la descripción de la categoría.
- Facilidad en la consulta de productos por categoría y elección de nuevas métricas.
Si el cliente decidiera que sus productos pueden pertenecer a más de una categoría, entonces ya sabes que debes crear una tabla intermedia por la relación muchos a muchos que se genera.
Con la solución implementada, las tablas quedarían así:
| id_producto | nombre | precio | stock | id_categoría |
|---|---|---|---|---|
| SDA | X-box 360 | 500 | 100 | 1 |
| SDB | Caja de huevos | 5 | 230 | 2 |
| SDC | Aceite de girasol | 6 | 219 | 2 |
| SDD | Coca-cola 200ml | 2,3 | 341 | 3 |
| id_categoría | nombre |
|---|---|
| 1 | TECNO |
| 2 | COMIDA |
| 3 | BEBIDA |
Ahora veamos nuestro diagrama:
Pagos en la Base de Datos del Sistema de Facturación
No entraré en muchos detalles sobre la tabla PAGO ya que este tema es muy extenso. Inicialmente podemos considerar datos como Fecha del pago, el Monto pagado y el Método de pago que se usó, ya sea efectivo, tarjeta de débito, etc.
Cuando un Sistema de facturación acepta varias formas de pago electrónico, es necesario realizar una investigación sobre bases de datos e-Commerce. Esta característica expande más el concepto de pagos, generado nuevas tablas y relaciones en el modelo, lo cual queda en tus manos si te encuentras en esa situación.
Es importante tener en cuenta la forma en que se realizarán los pagos, ya que hay negocios donde la factura debe pagarse en un solo pago, otros donde se debe hacer un solo pago pero divido en porcentajes con diferentes formas de pago. También existen acuerdos donde abonas un poco al inicio y en otra fecha pagas el valor restante.
O en el caso de las compras por Internet, se debe generar una Orden que sincronice la Cuenta del cliente, sus Pagos y el Envío del producto.
En mi caso elegí una forma muy sencilla de pago para terminar este articulo, donde el negocio recibe un pago por los productos de la factura y puede usar varios métodos de pago:
Podemos notar que un cliente tiene posibilitado pagar con varias formas de pago su compra, por lo cual creamos una relación uno a muchos entre MODO_PAGO y la factura.