Pildoritas de Bases de Datos: Diseño de Bases de Datos

Con este post se da el pistolezado de salida a las Pildoritas de Bases de Datos, que como ya avancé en el anterior artículo va a girar en torno al Diseño de Bases de datos. Para adentrarnos en este tema vamos a hacer un pequeño ejemplo con el que primeramente realicemos un modelo Entidad / Relación, para después pasar a su posterior Normalización.

Por tanto, vamos a comenzar enunciando un pequeño problema práctico enfocado en el sector de los servicios TIC:

Una importante cárnica del sector de la informática decide que es buena idea hacer una aplicación donde administrar los proyectos que tiene dado de alta. Dicha aplicación tendrá un registro de los departamentos que componen cada proyecto, así como de los empleados asignados a los mismos.

Vamos a suponer que esta cárnica ha vislumbrado durante un pequeño lapsus de tiempo el camino del buen desarrollo software y tiene unos departamentos estructurados y comunes a todos los proyectos (usan metodologías, esas leyendas urbanas que sólo conoces a través de un sello en la documentación). Por tanto, un proyecto puede tener de 1..N departamentos y un departamento a su vez puede estar de 1..N proyectos

Los empleados pueden trabajar para varios proyectos, pero no podrán pertenecer a más de un departamento por proyecto al mismo tiempo: Como en la vida real, ¡programadores multitarea!!

Por último, como a nuestra cárnica le encanta subcontratar a otras mini-cárnicas pertenecientes a antiguos jefes de dicha cárnica, no hay restricción alguna de que en un departamento y proyecto los empleados puedan pertenecer a multitud de empresas diferentes.

Modelo Entidad / Relación

Un primer acercamiento, que no el único, para construir la bases de datos que compondría dicha aplicación sería el siguiente:

Entidad Relacion - Ejemplo
Para la realización de los modelos Entidad / Relación he utilizado un software con licencia GPL, el DBDesigner, uno de los más conocidos. Es la primera vez que lo utilizo y pese a algunos inconvenientes, mi opinión del mismo es bastante buena. Hay otros muchos programas gratuitos muy buenos y todos funcionan más o menos igual. Si vais a utilizar MySQL os recomiendo su GUI Tool, el MYSQL Workbench, el cual tiene una función bastante buena de modelos entidad relación y su paso automático a tablas bajo el motor MySQL.

Partes del Modelo Entidad / Relación

No vamos a extendernos en demasía en la explicación del modelo Entidad / Relación, ya que se presupone cierto conocimiento de los lectores de estas pildoritas, pero si que vamos a destacar las partes más importantes del mismo y a identificarlas en nuestro ejemplo para mayor claridad.

Entidad

Representa una “cosa” con existencia propia (algo así como una clase en Java). En este caso las entidades son: Empleado, Compañía, Proyecto y Departamento.

Atributos

Son las propiedades que definen a una entidad. Por ejemplo, en el caso de Empleado sus atributos son: DNI, Nombre, Apellidos, Teléfono y Puesto.

Algunos atributos específicos se denominan claves. Se llaman claves porque a partir de este atributo (o conjunto de atributos) podemos identificar inequívocamente a un individuo de esa entidad de cualquier otro (algo así como identificar objetos de una misma clase).

Por último, tenemos las relaciones, que es cómo interactúan entre sí las entidades. Hay diferentes tipos de relaciones teniendo en cuenta su cardinalidad. Los tipos de cardinalidades son los siguientes:

Relaciones

  • Uno a Uno (1…1): Una entidad A se relaciona únicamente con una entidad B y viceversa. Por ejemplo, un empleado tiene un único DNI y un DNI pertenece a un único empleado. Por norma general una de las entidades pasará a ser atributo de la otra.
  • Uno a Varios (1..N): Una entidad A se relaciona con cero o varias entidades B, pero la entidad B sólo se relaciona con una única entidad de A. Un ejemplo de esta relación es la existente entre Empleado y Compañía. La Compañía puede tener 0..N empleados pero un empleado sólo pertenecerá a una única compañía. Esta relación se traduce en el modelo físico en una FK (clave foránea) de la entidad A (en nuestro ejemplo compañía) en la entidad B (en este caso empleados).
  • Varios a Varios (N..M): Una entidad A se relaciona con cero o varias entidades B y viceversa. Por ejemplo, en nuestro ejemplo una relación de este tipo sería la existente entre proyectos y departamentos. Un proyecto puede tener 0..N departamentos y un departamento a su vez puede ser parte de 0..N proyectos. Esta relación se traduce en la creación de una tabla intermedia donde se vincularan las claves de ambas entidades.

Una vez hemos realizado una primera aproximación funcional de los datos, vamos a normalizarla para evitar datos redundantes.

Normalización

La Normalización de Bases de Datos son una serie de reglas a tener en cuenta una vez hemos pasado el modelo Entidad / Relación a modelo relacional. Esto significa que tenemos que usar bases de datos relacionales, nada de NoSQL y tecnologías similares. Esto tampoco es una gran restricción, ya que la mayoría de tecnologías utilizadas hoy en día siguen este modelo: SQL Server, Oracle, MySQL, PostgreSQL

Gracias a la normalización se consigue que nuestra base de datos no tenga problemas de actualización de los datos en las tablas, que se protega la integridad de los datos y que estos no sean redundantes.

En concreto hablaremos de las tres formas normales. Hay 5 y otra llamada BCNF, pero con cubrir las tres primeras se cubren las necesidades de la gran mayoría de bases de datos. Si queréis entrar más en detalles os recomiendo comenzar por el artículo de Normalización de la Wikipedia.

Primera Forma Normal (1FN)

  • Todos los atributos son atómicos. Un atributo es atómico si los elementos del dominio son indivisibles, mínimos.
  • La tabla contiene una llave primaria única.
  • La llave primaria no contiene atributos nulos.
  • No debe existir variación en el número de columnas.
  • Los Campos no llave deben identificarse por la llave (Dependencia Funcional)
  • Debe Existir una independencia del orden tanto de las filas como de las columnas, es decir, si los datos cambian de orden no deben cambiar sus significados
  • Una tabla no puede tener múltiples valores en cada columna.
  • Los datos son atómicos (a cada valor de X le pertenece un valor de Y y viceversa).

Como se puede observar nuestro modelo Entidad / Relación cumple todas estas premisas. Esto se debe a que cualquier Bases de datos relacional cumple la Primera Forma Normal, así que hagas con el motor que hagas tu bases de datos estas normas serán de obligado cumplimiento si es una bases de datos relacional, y como ya he comentado, en el mercado es lo que encontrarás el 90% de las veces.

Segunda Forma Normal (2FN)

  • El modelo es 1FN
  • Los atributos que no forman parte de ninguna clave dependen de forma completa de la clave principal

Esto se puede traducir en que, si tenemos en una tabla más de una clave primaria, no debe haber dependencias parciales. Por poneros un ejemplo:

Entidad Relacion - No 2FN
Como podéis ver, hemos añadido un atributo a la propia relación entre empleados, proyectos y departamentos, en este caso la compañia. La compañía depende del empleado, pero no así del resto de claves, como son el proyecto y departamento. Por tanto, esta relación no sería 2FN. Como podéis percataros si dejáramos el esquema así tendríamos datos redundantes. Por ejemplo, si un empleado estuviera en dos proyectos diferentes, tendríamos dos veces repetido el dato de la compañía. Por tanto, lo aconsejable es que la compañía se relacione directamente con el empleado, como es nuestro caso.

Algunos habréis pensado, ¿y por qué la compañía no es un atributo como tal de la tabla empleados? Ahora veréis por qué, pillines 😛

Tercera Forma Normal (3FN)

  • El modelo es 2FN
  • Ningún atributo no-primario de la tabla es dependiente transitivamente de una clave primaria.

¿Qué significa esto en cristiano? Pues que si tienes un campo o grupo de campos cuyos valores pueden repetirse en más de un registro, se deben colocar dichos campos en una tabla independiente.

¡Oh, Wait! ¡Nuestro modelo no está en 3FN, Unbelievable!

Como se puede ver, la tabla Empleado tiene un atributo, Puesto, que puede repetirse para varios empleados diferentes. La forma de conseguir que nuestro modelo si sea 3FN es convertir el atributo Puesto en una tabla independiente, lo cual dejaría nuestro modelo Entidad / Relación de la siguiente forma:

Entidad-Relacion-3FN

Si seguimos buscando encontraremos más campos susceptibles de ser separados como entidades. El Area del Departamento y el Tipo de Compañia deberían ser separados en tablas independientes si queremos cumplir la 3FN, pero hay que tener claro que la aplicación de las formas normales no siempre es viable ni acertada de aplicar.

Por ejemplo, aunque la 3FN es recomendable ya que evitamos datos repetidos, el utilizar muchas tablas pequeñas puede perjudicar al rendimiento de nuestra bases de datos e incluso superar la capacidad de memoria de la que dispone nuestro servidor. Muchas veces es recomendable que los datos que cambien con frecuencia estén en 3FN y no así los restantes, controlando las repeticiones (por ejemplo al realizar un borrado) vía aplicación, por ejemplo.

Es más, se pueden realizar diferentes modelos de entidad / relación que cumplan las funcionalidades especificadas por el cliente igual de bien.

Por ejemplo, el modelo que hemos utilizado durante todo el ejemplo, si os fijáis, no cumple en sí mismo uno de los puntos importantes, y es que podemos tener un empleado que trabaje para diferentes departamentos del mismo proyecto. ¿Significa que mi modelo esté mal? Por supuesto que no, y no sólo porque yo sea jodidamente awesome, que también, sino porque, por ejemplo, este control lo podemos realizar desde un trigger en la misma BBDD o por medio del propio código fuente de la aplicación.

No obstante, si queremos dejar el modelo de entidad relación niquelado con respecto al enunciado inicial, deberíamos cambiarlo y dejarlo así:

Entidad-Relacion-Final

Paso a Tablas

Y ya para acabar, lo más sencillo, el paso a tablas. Esto tiene 0 de mérito, porque estas cosas ya la hacen los programas como el DBDesigner, no obstante, os dejo aquí el script de bases de datos autogenerado para que os hagáis una idea de cómo se acaban creando los elementos de la base de datos.

CREATE TABLE Compañia (
  idCompañia INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Nombre VARCHAR(20) NOT NULL,
  CIF VARCHAR(20) NOT NULL,
  Tipo VARCHAR(20) NOT NULL,
  PRIMARY KEY(idCompañia)
);

CREATE TABLE Departamento (
  idDepartamento INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Nombre VARCHAR(20) NOT NULL,
  Área VARCHAR(20) NOT NULL,
  PRIMARY KEY(idDepartamento)
);

CREATE TABLE Empleado (
  DNI VARCHAR(20) NOT NULL,
  Puesto_idPuesto INTEGER UNSIGNED NOT NULL,
  Compañia_idCompañia INTEGER UNSIGNED NOT NULL,
  Nombre VARCHAR(20) NOT NULL,
  Apellidos VARCHAR(45) NOT NULL,
  Telefono INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(DNI),
  INDEX Empleado_FKIndex1(Compañia_idCompañia),
  INDEX Empleado_FKIndex2(Puesto_idPuesto)
);

CREATE TABLE Empleado_has_Proyecto_has_Departamento (
  Proyecto_has_Departamento_Departamento_idDepartamento INTEGER UNSIGNED NOT NULL,
  Proyecto_has_Departamento_Proyecto_idProyecto INTEGER UNSIGNED NOT NULL,
  Empleado_DNI VARCHAR(20) NOT NULL,
  PRIMARY KEY(Proyecto_has_Departamento_Departamento_idDepartamento, Proyecto_has_Departamento_Proyecto_idProyecto, Empleado_DNI),
  INDEX Empleado_has_Proyecto_has_Departamento_FKIndex1(Empleado_DNI),
  INDEX Empleado_has_Proyecto_has_Departamento_FKIndex2(Proyecto_has_Departamento_Proyecto_idProyecto, Proyecto_has_Departamento_Departamento_idDepartamento)
);

CREATE TABLE Proyecto (
  idProyecto INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Nombre VARCHAR(20) NOT NULL,
  Fecha inicio DATE NOT NULL,
  Fecha Fin DATE NOT NULL,
  PRIMARY KEY(idProyecto)
);

CREATE TABLE Proyecto_has_Departamento (
  Proyecto_idProyecto INTEGER UNSIGNED NOT NULL,
  Departamento_idDepartamento INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(Proyecto_idProyecto, Departamento_idDepartamento),
  INDEX Proyecto_has_Departamento_FKIndex1(Proyecto_idProyecto),
  INDEX Proyecto_has_Departamento_FKIndex2(Departamento_idDepartamento)
);

CREATE TABLE Puesto (
  idPuesto INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Nombre VARCHAR(20) NULL,
  Descripción VARCHAR(45) NULL,
  PRIMARY KEY(idPuesto)
);

Bueno, con esto acabamos nuestra primera Pildorita de Bases de Datos. Si os ha gustado, cosa que dudo, estad atentos al blog para los próximos posts 🙂

Índice de Pildoritas

Share