MODELO ENTIDAD
RELACIONAL
En el desarrollo de software para empresas, el
almacenamiento de la información de un modo organizado es fundamental… la
mayoría de los casos en los que el programador contesta “no se puede hacer” a
un requerimiento de un cliente se debe a un error en el modelado de la base de
datos que funciona como soporte a la aplicación. En este artículo voy a
intentar explicar, con un ejemplo práctico, un buen modelado de datos.
Como, de alguna forma, estamos especializados en el
software de gestión de empresas de enseñanza, voy a utilizar un ejemplo de uno
de esos modelos: la gestión de matriculación de los alumnos, incluyendo los
recibos que tienen que pagar, y el pago parcial de los mismos. Voy a explicar
en este artículo el funcionamiento del proceso (para que podamos hacer el
seguimiento de la implementación), las tablas que utilizamos y los campos (de
forma resumida) que componen cada una de las tablas. De paso, daré una idea de
los índices, procedimientos almacenados y triggers que nos pueden resultar
útiles para que el rendimiento de la base de datos sea bueno.
Descripción del proceso de matriculación (el
caso de uso)
Vamos a imaginarnos que nos encontramos en una
academia de idiomas, en la que los alumnos se matriculan y asisten a clase de
forma temporal. En este caso me voy a centrar en lo que se llaman “grupos
abiertos”, es decir, grupos en los que cualquiera se puede matricular (en
oposición a los grupos de empresa o grupos cerrados, que suelen funcionar de
forma diferente).
Cuando llegamos a la academia, se nos ofrece un
folleto o catálogo de productos y servicios, en el que se detallan los
diferentes cursos en los que nos podemos matricular, y las diferentes formas de
pago que podemos utilizar. Seleccionamos uno de los cursos, la forma de pago
que más nos conviene, el horario al que vamos a asistir, y con esta información
nos matriculamos. Como forma de pago, en este caso, vamos a utilizar un pago
mensual, y queremos que se nos domicilie el pago a través de nuestra cuenta
bancaria.
En la academia, llegado este punto, introducen en su
sistema de información nuestros datos y nos imprimen el contrato de prestación
de servicios, en el que se incluyen todos nuestros datos, el curso en el que
nos hemos matriculado y todos los pagos que vamos a tener que realizar mientras
estemos matriculados. Nos piden, de paso, que paguemos una reserva de plaza,
que es una pequeña cantidad del primer recibo.
En el siguiente día de clase, nos presentamos, y el
profesor comprueba en su hoja de asistencia que estamos incluidos en el grupo…
nos da la bienvenida, y empezamos a estudiar.
El modelo de datos
A partir de aquí haré una descripción de la estructura
de tablas y columnas para almacenar la información de este proceso. Primero,
algunas generalidades sobre cómo crear los campos.
Generalidades
Hay algunas cosas básicas a la hora de modelar el
modelo de datos que usamos como convenciones (nomenclatura, cosas así). Por
ejemplo:
1. La clave primaria de las
tablas siempre es un identificador autoincremental. Todas las tablas tienen así
un identificador interno, mantenido por el sistema. Así, las claves ajenas son
más fáciles de mantener.
2. En general, nosotros no
solemos poner campos requeridos… preferimos hacer la gestión dentro de la
lógica de negocio. Nunca se sabe lo que te vas a encontrar, y se nos han dado
casos de campos de los que estábamos completamente seguros que eran requeridos
y hemos tenido que quitar la marca.
3. No se duplica información.
Es decir, una de las reglas básica es que la misma información no puede estar
en dos sitios, salvo…
4. En muchos casos, creamos
campos calculados, que permiten acceder de forma rápida a información… por
ejemplo, el importe pendiente de un recibo, en realidad, se calcula como el
importe total del recibo menos la suma de los pagos parciales… como hacer este
cálculo cada vez que nos hace falta ralentiza el funcionamiento del sistema,
hacemos un campo calculado que se mantiene automáticamente (en nuestro caso, a
través de Triggers de la base de datos). La información está duplicada en dos
sitios, sí, pero por motivos de rendimiento (y siempre está sincronizada).
5. En los nombres de los
campos no ponemos caracteres especiales (ni acentos, ni espacios, etc.). Aunque
el gestor de base de datos lo admita, no lo hacemos, porque luego nunca se sabe
desde dónde vas a tener que acceder.
Descripción
de las entidades
El primer paso para hacer el modelo de datos es
identificar las entidades (tablas) que vamos a tener. Según el caso de uso
descrito, las tablas necesarias son las siguientes (al menos, son las que
nosotros usamos):
·
Cursos: almacena la oferta
formativa del centro. Representa el catálogo o folleto que te dan al llegar al
centro.
·
Formas
de Pago: para cada
curso, las distintas opciones de pago que existen (es parte del folleto también).
Trimestral, mensual, anual, etc.
·
Grupos: dentro de cada curso,
los diferentes horarios a los que se puede asistir. En este caso, el modelo que
utilizamos es bastante más complejo que el que voy a describir aquí… en un
artículo posterior lo describiré en detalle.
·
Clientes: el que paga… puede
ser el mismo que el alumno, pero también puede que no.
·
Medios
de pago: Contiene los
diferentes métodos que los clientes pueden usar para pagar (contado, domiciliación
bancaria, etc.), incluyendo las cuentas bancarias del cliente.
·
Alumnos: la gente que va a clase.
Los clientes pueden ser empresas (personas jurídicas), los alumnos son personas
físicas. Un mismo cliente puede tener múltiples alumnos.
·
Matrículas: Refleja en qué curso nos
matriculamos, las fechas, la forma de pago, etc. De forma física, se refleja en
el contrato que te dan para firmar.
·
Recibos: almacena los recibos
que el cliente tiene que pagar (o ha pagado) en el centro.
·
Pagos: esta tabla refleja
los pagos que el cliente ha hecho (un recibo no necesariamente se paga de una
vez). Como antes, la gestión de recibos y pagos que hacemos en realidad es más
compleja de lo que voy a describir aquí. En otro artículo haré una descripción
más completa.
·
Alumnos
en grupos: refleja los
alumnos que están asignados a los distintos grupos. El alumno puede cambiar de
grupo, y no queremos perder esa información histórica, así que necesitamos una
tabla para gestionarlo.
Aquí podéis ver el modelo gráficamente:
Con PK se marcan las claves primarias, y con FK, las
claves ajenas… algunas líneas se cruzan, no lo puedo evitar. Las flechas
indican que una tabla es ‘hija’ de otra, con la punta de flecha apuntando al
padre.
Como podéis ver, sólo están indicados los campos que
forman el modelo de datos… las claves primarias y las claves ajenas, que en
cualquier caso deben estar ocultas al usuario final. En la siguiente sección
describiremos los campos de cada tabla.
Campos para
las entidades
Sólo describiré los campos más importantes, y no
incluiré los campos de clave primaria y ajena que se describen en el gráfico.
Cursos
·
nombre: varchar(100)
·
descripción: Memo. Se utilizará, en el contrato que se imprime para el
cliente, para hacer una descripción larga del curso en el que el alumno se está
matriculando. En uno de los sistemas que tenemos, en lugar de tener un campo
memo, tenemos una tabla separada en la que se guardan, por tipologías, distintos campos memos, que se imprimen
en distintos lugares del contrato.
·
fecha inicio: date
·
fecha fin: date
Formas de pago
·
nombre: varchar(100)
·
importe: float. Es el importe de cada recibo que se cobrará
·
numero meses: integer. El número de meses de cada recibo. Si es 1, se
creará un recibo cada mes mientras dure la matriculación, si es 3, uno cada
tres meses, etc. En algún sistema hemos hecho, en lugar de esto, una estructura
de plantillas de recibos, con fechas, descripciones, etc. personalizadas. Eso
permite más flexibilidad y más control, pero el modelo es bastante más
complejo.
·
numero orden: integer. A la hora de presentárselo al cliente, poder mostrar
primero las que más nos interesen.
·
importe matricula: float. Si además del importe del curso hay un importe de
matrícula, se marca aquí.
·
concepto matricula. El concepto del recibo de matrícula, si creamos uno.
Grupos
·
nombre: varchar(100)
·
código: varchar(20). Siempre viene bien tener una codificación además del
nombre. Por ejemplo, en algunos sistemas lo utilizamos para guardar el código
del grupo en la Fundación Tripartita.
·
fecha inicio: date
·
fecha_fin: date. Por defecto, las del curso al que pertenece el grupo, y
además estas fechas no pueden estar fuera de las fechas del curso al que
pertenecen.
·
lugar: varchar(100) de impartición del grupo. En general, hacemos una
gestión de aulas, pero eso lo ampliaré en otro artículo.
·
notas: memo, del grupo
·
horario: varchar(100) del grupo. En realidad, el horario se trata como una
tabla por debajo de esta, pero no voy a entrar en tanto nivel de detalle ahora.
·
maximo_alumnos: Integer. Máximo número de alumnos permitidos en el grupo.
·
numero_alumnos: Integer. Es el número de alumnos existentes en el grupo.
Este campo es de sólo lectura para el usuario, y es calculado, a través de una
serie de Triggers en la base de datos, para poder saber rápidamente el número
de alumnos activos en cada grupo sin tener que estar sumando.
Clientes
·
nombre: varchar(100). En nuestros sistemas, normalmente, este es el único
campo requerido (por código, no en la base) que tenemos. Así, el usuario puede
dar de alta el registro aunque no tenga todos los datos, y volver después.
·
primer_apellido: varchar(100)
·
segundo_apellido: varchar(100)
·
nombre_completo: varchar(300): Esto es un campo calculado, que se mantiene
con Triggers, para poder coger de forma rápida el conjunto Nombre+’
‘+primer_apellido+’ ‘+segundo_apellido
·
direccion: memo
·
codigo_postal: varchar(20): no hay que ser tacaño… de vez en cuando hay que
meter una dirección extranjera y el código postal puede ser más grande.
·
población: varchar(50)
·
notas_internas: memo
·
etc. de datos personales (profesión, teléfonos, email, etc.)
Alumnos
·
nombre: varchar(100). Lo mismo que en clientes, pero lo requerido es nombre
y primer apellido (en clientes es sólo nombre por si
·
primer_apellido: varchar(100)
·
segundo_apellido: varchar(100)
·
nombre_completo: varchar(300):
·
etc. de datos personales (profesión, teléfonos, email, etc.)
Medios de pago
·
tipo_medio: Integer. Normalmente tiene una tabla asociada con los tipos de
medios de pago, que suelen ser: Sin Pago, Contado, Banco
·
nombre_titular: varchar(100)
·
direccion_titular: memo
·
entidad: varchar(4)
·
oficina: varchar(4)
·
dc: varchar(2)
·
numero_cuenta: varchar(10). Si el tipo_medio es banco, entonces se tiene
que rellenar la información bancaria del cliente.
·
por_defecto: boolean. Se suele preguntar el medio de pago, pero teniendo
uno por defecto, para no tener que rellenarlo siempre. Normalmente, cada
cliente, al crearse, se crea un medio de pago “contado”, y se le pone por
defecto.
Matrículas
Además de los datos de curso, forma de pago, medio de
pago, alumno y cliente (esto último puede parece redundante, pero no lo es…
podemos tener el caso (yo lo he visto) de un alumno que se matricula para
estudiar, digamos, inglés y francés… el inglés lo paga el padre y el francés la
madre. Así, es necesario que cada matrícula esté asociada con el alumno, y
también con el cliente), necesitamos los siguientes campos:
·
fecha_inicio: date.
·
fecha_fin: date. Por defecto, las del curso, pero hay gente que puede
matricularse después o terminar antes (si se da de baja, por ejemplo).
·
importe: real. Por defecto, el de la forma de pago escogida, pero puede ser
también distinto… descuentos por familiares, cosas así. Suele ser buena idea
dejarlo abierto, para que el cliente lo pueda cambiar.
·
motivo_baja: varchar(100). Normalmente, los motivos de baja son una tabla
separada, para luego poder obtener estadísticas de número de bajas por tipo,
cosas así.
Recibos
·
fecha_emision: date
·
fecha_cobro_completo: date
·
numero_recibo: varchar(20)
·
concepto: varchar(50)
·
importe_recibo: float.
·
importe_pendiente: float. Es un campo de sólo lectura, actualizado a través
de triggers, que permite acceder a la información sin tener que sumar.
Pagos
·
fecha: date
·
importe: real
·
forma_cobro: varchar(20). Normalmente es una tabla separada, igual que el
caso de los tipos de baja. Puede ser: contado, transferencia, tarjeta, talón,
etc.
Alumnos en grupos
·
fecha_inicio: date
·
fecha_fin: date. Suele ser una intersección entre la duración del grupo y
la de la matrícula, pero cuando el alumno cambia de grupo, para una matrícula
puede haber varios registros de alumnos en grupos. Hay que tener en cuenta
también la posibilidad de que en un mismo curso, pagando más, un alumno pueda
asistir a varios grupos (esto también lo he visto).
Triggers
y procedimientos almacenados
El modelo de datos y la lógica del negocio están muy
estrechamente relacionados. Los sistemas de base de datos nos permiten
desarrollar triggers y procedimientos almacenados, lo que es muy conveniente
para dejar trozos de la lógica de negocio asociados con la base de datos, tanto
por motivos de organización del código como por motivos de rendimiento (un
procedimiento almacenado es varios órdenes de magnitud más rápido que hacer el
mismo proceso a través de un lenguaje de programación).
En el ejemplo que estoy describiendo, hay varios
triggers y procedimientos que se usan:
·
Actualización de los campos “NombreCompleto” de alumnos y clientes:
normalmente, es un trigger BEFORE INSERT y BEFORE UPDATE, que actualiza el
campo en base al contenido del nombre y los apellidos.
·
Actualización del campo “ImportePagado” de recibos, AFTER INSERT, UPDATE y
DELETE de pagos, que actualiza el campo ImportePendiente de recibos como la
suma de los pagos de ese recibo.
·
Es habitual hacer un procedimiento CREARRECIBOS, que se ejecuta en el
proceso de creación de la matrícula (o un trigger AFTER INSERT), que crea los
recibos de la matrícula en base al importe y forma de pago seleccionadas.
No hay comentarios:
Publicar un comentario