14
Mar 13

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

pildoras bbdd

En el anterior artículo de Pildoritas de Bases de Datos hablamos largo y tendido del Diseño de Bases de Datos, en concreto sobre la realización de un digrama Entidad / Relación y su posterior normalización.

Cuando un modelo de datos está normalizado hasta la tercera forma normal nuestro diseño ya es bastante bueno. Gracias a la normalización no deberías tener datos redundantes, ni columnas iguales con enumeraciones (proyecto 1, proyecto2…proyecto n), etc.

No obstante, hay otras consideraciones que debemos tener en cuenta y que paso a mencionar a continuación. Son algunas recomendaciones que creo os resultarán de ayuda si tenéis algún día que realizar el diseño de una BBDD.

No guardes datos innecesarios ni consultes campos que no requieras

Hay que grabarse a fuego una verdad que a veces obviamos: No hay nada más costoso en tiempo que el acceso a BBDD. Por tanto, siempre tenemos que evitar acceder a ella de forma “gratuita”.

Mucha gente piensa eso de que no hay nada malo en guardar todo lo posible en BBDD. De hecho, cuanto más guarde pues mejor, no vaya a ser que un futuro haya datos que no almacenaba y luego hagan falta.

Hay que tener presente que la BBDD consiste en pedir a un servicio (muchas veces en otra máquina) que lea de disco una información y nos la devuelva, así que cuanto mayor sea el tamaño de estas tablas, mayor tiempo nos llevará acceder a ellas.

Es por ello que aquellos datos que puedan calcularse a partir de otros no deben de estar almacenados en la bases de datos. Así mismo, si tenemos que recoger datos de BBDD, deberemos de recuperar los campos que sean necesarios, no hacer, por ejemplo, un SELECT * FROM tabla.

Selecciona el tipo de dato y longitud adecuados

Parece una tontería pero veo a mucha gente poco cuidadosa en este aspecto. Por ejemplo, a la hora de crear un campo de texto, mucha gente decide crear un campo VARCHAR por pura costumbre, cuando a lo mejor en un caso concreto los valores de un campo siempre van a tener una longitud fija y merece la pena mejor crearlo de tipo CHAR, ya que ocupa menos. Otro ejemplo es crear un campo de tipo DATETIME cuando lo que se necesita almacenar es sólo la fecha y valdría un campo DATE. En general, hay que pararse un segundo a ver de qué tipo de dato es cada atributo y con qué longitud debemos crear cada campo.

También hay que tener en cuenta la integridad de datos. En el ejemplo del anterior artículo el Nombre y Apellidos del empleado no deberían aceptar caracteres nulos, o el DNI, en caso de que no fuera una PK, debería de ser único. Podemos pensar que esa validación no tiene sentido si no vamos a recibir ese campo vacío nunca por imposición de la lógica de negocio, pero hay amigos, los caminos del desarrollo software son inescrutables, así que mejor poner control desde BBDD no vaya a ser que nos llevemos una sorpresita y a algún proceso le de por saltarse esta norma y meter a un pobre empleado sin nombre ni apellidos.

Crea índices cuando sea posible

Los índices funcionan un poco como el índice de los libros (así a groso modo y por no liar mucho) y sirven para agilizar el tiempo de ejecución de las consultas de SQL. Usando la analogía del libro, si queremos ir al Capítulo 5, en vez de ir página a página hasta que lleguemos a él, mejor mirar el índice e ir a la página directamente.

La primera pregunta que nos asalta a todos cuando oímos por primera vez hablar sobre ellos es “¿Por qué no ponemos índices en todas las columnas?

A parte de que guardar las relaciones de los índices y los datos de la columna ocupa espacio (a veces más que la propia tabla), tenemos que tener en cuenta que cada vez que insertemos, borremos o actualicemos en la BBDD, tendremos también que consumir tiempo en actualizar la relación del índice.

Por tanto, tener demasiados índices en el fondo acaba empeorando el tiempo de respuesta global del acceso a la base de datos, por lo que crearemos un índice cuando tengamos claro que es beneficioso.

¿Cómo sabemos que es beneficioso? Pues es difícil saberlo. Lo mejor con los índices siempre es realizar un explain plan antes y después de añadir el índice para ver si mejora la respuesta, pero intentaremos marcar unas pautas generales que nos ayudarán a la hora de crear índices eficientes.

Para empezar los índices deben encontrarse más como filtro de nuestras consultas (es decir, en el WHERE) que ser parte de los datos que obtenemos (los campos indicados tras el SELECT).

Tampoco es buena idea marcar como índice aquella columna que tiene muchos valores idénticos. Por ejemplo, una columna booleana que indica True o False no tendría sentido colocarla como índice. Por tanto, un índice debe ser un campo donde muchos de los valores sean distintos. Si la tabla tiene pocos registros tampoco merece la pena crear un índice, ya que no mejorará sustancialmente la el tiempo de respuesta.

Por último, hay que tener también en cuenta cuando se crea un índice la regla de la izquierda. Si filtramos por dos campos en el WHERE muy a menudo, más que indicar dos índices por cada campo deberíamos crear un índice multicolumna. A la hora de utilizar dicho índice multicolumna tenemos que tener en cuenta la regla de la izquierda que os comentaba: Hay que incluir en el WHERE las columnas indexadas por orden de izquierda a derecha al igual están definidas en el índice o si no, la consulta no usará dicho índice. Además, en los índices multicolumna si sólo hacemos referencia a uno de sus campos no usaremos el índice completo, por lo que hay que tener muy claro cuando definir un índice de este tipo en vez de uno simple. Hablaré sobre este tema de nuevo en la parte de optimización SQL.

Define una hoja de estilos y mantenla siempre

En algunos casos, como MySQL, hay diferenciación entre mayúsculas y minúsculas en el nombre de las tablas, es por ello que viene bien definir un estándar a la hora de ponerles un nombre: Todas en mayúscula o en formato camelCase. Tampoco es recomendable utilizar caracteres especiales a la hora de nombrar tablas o campos.

Si todos los campos se deciden nombrar en singular, esto tiene que ser así siempre. Por otra parte, si estas desarrollando una aplicación que puede ser utilizada por gente de otros países es recomendable usar siempre nombres en inglés.

Resumiendo, márcate tu propio estándar o usa uno ya existente, pero sigue ese estándar a muerte.

Las reglas que yo suelo usar, pero que pueden ser perfectamente otras, son las siguientes:

  • Separa las palabras a partir de un guión bajo o siguiendo el estilo camelCase. En mi caso prefiero la primera opción.
  • Nombra la tabla y los campos en singular.
  • Si tienes muchas tablas que pueden ser agrupadas en grupos lógicos, utiliza un prefijo que te ayude a identificarlas.
  • Intenta que los nombres sean descriptivos y evita abreviaciones, pero sin que sean demasiado largos.
  • Los campos Primary Key deben empezar por el prefijo ID.
  • Primero se crearán los campos PK, seguidos luego de las FK.
  • Escribir todo en mayúsculas o minúsculas. En mi caso soy más de la primera opción.
  • No utilizar caracteres numéricos, al menos en el nombre de las tablas.
  • Los campos que estén en varias tablas (como las FK) deben tener el mismo nombre.

Bueno, con este post ya acabamos la parte de Diseño de Bases de Datos. En la siguiente pildorita vamos a empezar ya un poco con la sintáxis SQL, ¡así que estaros atentos!

Índice de Pildoritas

Share
28
Feb 13

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

pildoras bbdd
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