Principios de diseño de bases de datos

1 . Introducción
2 . Almacenar sólo la información necesaria
3 . Pedir sólo lo necesario y ser explícito
4 . Normalizar las estructuras de tablas
5 . Seleccionar el tipo de dato apropiado
6 . Utilizar índices apropiadamente
7 . Usar consultas REPLACE
8 . Usar tablas temporales
9 . Usar una versión reciente de MySQL
10 . Consideraciones finales

Cedido por MySQL Hispano.

Introducción

Uno de los pasos cruciales en la construcción de una aplicación que maneje una base de datos, es sin duda, el diseño de la base de datos. Si las tablas no son definidas apropiadamente, podemos tener muchos dolores de cabeza al momento de ejecutar consultas a la base de datos para tratar de obtener algún tipo de información.

No importa si nuestra base de datos tiene sólo 20 registros, o algunos cuantos miles, es importante asegurarnos que nuestra base de datos está correctamente diseñada para que tenga eficiencia y usabilidad a lo largo del tiempo.

En este artículo, se mencionarán algunos principios básicos del diseño de base de datos y se tratarán algunas reglas que se deben seguir cuando se crean bases de datos. Dependiendo de los requerimientos de la base de datos, el diseño puede ser algo complejo, pero con algunas reglas simples que tengamos en la cabeza será mucho más fácil crear una base de datos perfecta para nuestro siguiente proyecto.

Construir grandes aplicaciones en MySQL resulta fácil con herramientas como Apache, Perl, PHP, y Python. Asegurarse de que son rápidas, sin embargo, requiere algo más que perspicacia. MySQL tiene una bien merecida reputación de ser un servidor de bases de datos muy rápido que también es muy fácil de configurar y usar, además de que en los últimos años su popularidad ha crecido notablemente debido a que se utiliza en infinidad de sitios web que requieren hacer uso de una base de datos. Sin embargo, pocos usuarios sabemos algo más que crear una base de datos y escribir algunas búsquedas contra ella.

Después de leer este artículo debemos ser capaces de entender algunas técnicas que nos ayudarán a diseñar bases de datos MySQL para construir mejores aplicaciones. Vamos a suponer que se tiene un conocimiento básico del lenguaje SQL, y de MySQL, pero no vamos a asumir que se tiene mucha experiencia en alguno de los dos.

Almacenar sólo la información necesaria

Parece de sentido común, pero muchas personas suelen tomar el enfoque de "sumidero de cocina" para el diseño de bases de datos. A menudo pensamos en todo lo que quisiéramos que estuviera almacenado en una base de datos y diseñamos la base de datos para guardar dichos datos. Hemos de ser realistas acerca de nuestras necesidades y decidir qué información es realmente necesaria. Frecuentemente podemos generar algunos datos sobre la marcha sin tener que almacenarlos en una tabla de una base de datos. En estos casos también tiene sentido hacer esto desde el punto de vista del desarrollo de la aplicación.

Por ejemplo, una tabla de productos para un catálogo en línea puede contener nombres, descripciones, tamaños, pesos y precios de varios productos. Además del precio, puede que se quieran guardar los impuestos y los gastos de envío asociados con cada producto. Pero realmente no hay ninguna necesidad de hacer esto. Primero, tanto los impuestos como los gastos de envío pueden ser calculados sobre la marcha (ya sea por nuestra aplicación, o por MySQL). Segundo, si cambiamos los impuestos o los gastos de envío, tendríamos que escribir las búsquedas necesarias para actualizar los impuestos y los gastos de envío en cada registro del producto.

Algunas veces pensamos que agregar campos a las tablas de una base de datos una vez que han sido creadas es demasiado difícil, así que nos vemos impulsados a definir tantas columnas como se pueda. Bueno, esto simplemente es un concepto erróneo, ya que en MySQL podemos usar el comando ALTER TABLE para modificar la definición de una tabla en cualquier momento para que se adecue a nuestras necesidades cambiantes.

Por ejemplo, si en algún momento nos damos cuenta que necesitamos agregar una columna de popularidad a nuestra tabla productos (tal vez queramos que nuestros clientes califiquen los productos en nuestro catálogo), podríamos hacer lo siguiente:

ALTER TABLE productos ADD popularidad INTEGER;

Pedir sólo lo necesario y ser explícito

Igual que decir "almacenar sólo lo necesario", esto puede parecer un poco más de sentido común, sin embargo, esto no suele ser considerado muy a menudo. ¿Por qué?. Porque cuando una aplicación está en desarrollo los requerimientos suelen cambiar, de tal forma que muchas de las búsquedas terminan pareciéndose a esto:

SELECT * FROM algunaTabla;

Obtener todas las columnas de una tabla es simplemente lo más conveniente que podemos hacer cuando no estamos seguros de qué campos necesitamos. Sin embargo, a medida que las tablas crecen y cambian, esto puede convertirse en un problema de rendimiento. A la larga es mucho mejor tardarnos un tiempo extra después de nuestro desarrollo inicial y decidir exactamente qué es lo que necesitamos en nuestras búsquedas. En concreto, es mucho mejor especificar las columnas de forma explícita:

SELECT nombre, precio, descripcion FROM productos;

Esto se relaciona con un punto que tiene que ver más con el mantenimiento del código que con el rendimiento. La mayoría de los lenguajes de programación (Perl, Python, PHP, Java, etc) nos permiten acceder a los resultados de una consulta por los nombres de los campos y por su posición numérica. Para el ejemplo anterior, podemos acceder al campo 0, o al campo nombre y obtener los mismos resultados.

A la larga es mejor usar los nombres de columnas que sus posiciones numéricas. ¿Por qué? Porque las posiciones relativas de columnas en una tabla o en un resultado de una consulta pueden variar. Por ejemplo, pueden variar en una tabla como resultado de un ALTER TABLE, o bien, cambiarán en una consulta como resultado de que alguien rescriba la búsqueda y se olvide de actualizar la lógica de la aplicación apropiadamente.

Claro está, ¡aún debemos ser cuidadosos cuando cambiemos los nombres de las columnas! Pero si usamos nombres en vez de posiciones numéricas, podemos usar la capacidad de búsqueda y reemplazo de nuestro editor para encontrar el código que hemos de cambiar en caso de que cambie el nombre de una columna.

Normalizar las estructuras de tablas

Si nunca antes hemos oído hablar de la "normalización de datos", no debemos temer. Mientras que la normalización puede parecer un tema complejo, nos podemos beneficiar ampliamente al entender los conceptos más elementales de la normalización.

Una de las formas más fáciles de entender esto es pensar en nuestras tablas como hojas de cálculo. Por ejemplo, si quisiéramos seguir la pista de nuestra colección de CDs en una hoja de cálculo, podríamos diseñar algo parecido a lo que se muestra en la siguiente tabla.

+------------+-------------+--------------+ ..  +--------------+
| album      | track1      | track2       |     | track10      |
+------------+-------------+--------------+ ..  +--------------+
| Antrologia | Tarzan Boy  | Life is life | ..  | Square rooms |
|            | (Baltimora) | (Opus)       | ..  | (Al Corley)  |
+------------+-------------+--------------+ ..  +--------------+

Esto parece razonable. Sin embargo el problema es que el número de pistas que tiene un CD es bastante variable. Esto significa que con este método tendríamos que tener una hoja de cálculo realmente grande para albergar todos los datos, que en los peores casos podrían ser de hasta 20 pistas. Esto en definitiva no es nada bueno.

Uno de los objetivos de una estructura de tabla normalizada es minimizar el número de "celdas vacías". En el caso de la tabla de CDs que estamos tratando, tendríamos muchas de estas celdas si permitiéramos CDs de 20 pistas o más. En el caso de que las listas de campos pueden expandirse "hacia la derecha", como en este ejemplo de los CDs, nos da una pista de que necesitamos dividir los datos en dos o más tablas a las que luego podamos acceder de forma conjunta para obtener los datos que necesitemos.

Mucha gente nueva en los sistemas de bases de datos relacionales no sabe en verdad lo que significa "relacional" en RDBMS (Relational Database Management System). En términos simples, grupos parecidos de información son almacenados en distintas tablas que luego pueden ser "juntadas" (relacionadas) basándose en los datos que tengan en común. Desafortunadamente esto suena bastante académico y vago, sin embargo, en nuestra base de datos de CDs podemos ejemplificar una situación concreta en la que veremos cómo normalizar los datos.

El darnos cuenta de que cada lista de CDs tiene un conjunto fijo de atributos (título, artista, año, género) y un conjunto variable de atributos (el número de pistas) nos da una idea de cómo dividir los datos en múltiples tablas que luego podamos relacionar entre sí. Podemos crear una tabla que contenga una lista de todos los álbumes y sus atributos fijos y otra que contenga una lista de todas las pista de esos álbumes. De esta forma, en vez de pensar en forma horizontal (como con la hoja de cálculo), pensamos en forma vertical y dejamos una estructura de tablas como la que se muestra a continuación.

CREATE TABLE album (
   id_album INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
   titulo VARCHAR(80) NOT NULL );

CREATE TABLE pista (
   id_album INTEGER NOT NULL,
   numero   INTEGER NOT NULL,
   titulo   VARCHAR(80) NOT NULL );

El identificador de álbum (id_album) es lo que relaciona las distintas pistas de un álbum dado. El campo id_album en la tabla pista coincide con el campo id_album en la tabla album, de tal manera que para obtener una lista de todas las pistas de un álbum dado, podríamos realizar una consulta como esta:

SELECT pista.numero, pista.nombre FROM album, pista
WHERE album.titulo = "El titulo del album"
AND album.id_album = pista.id_album

Esta estructura es a la vez flexible y eficiente. La flexibilidad está en el hecho que podemos agregar datos al sistema posteriormente sin tener que rescribir lo que ya tenemos. Por ejemplo, si quisiéramos agregar la información de los artistas de cada álbum, lo único que tenemos que hacer es crear una tabla artista que esté relacionada a la tabla album de la misma manera que la tabla pista. Por lo tanto, no tendremos que modificar la estructura de nuestras tablas actuales, simplemente agregar la que hace falta.

La eficiencia se refiere al hecho de que no tenemos duplicación de datos, y tampoco tenemos grandes cantidades de "celdas vacías". De esta manera MySQL no tiene que almacenar más datos de los necesarios, ni gastar recursos al revisar las áreas vacías en nuestras tablas.

El objetivo principal del diseño de bases de datos es generar tablas que modelan los registros en los que guardaremos nuestra información. Es importante que esta información se almacene sin redundancia para que se pueda tener una recuperación rápida y eficiente de los datos. A través de la normalización tratamos de evitar ciertos defectos que nos conduzcan a un mal diseño y que lleven a un procesamiento menos eficaz de los datos.

Podríamos decir que estos son los principales objetivos de la normalización:

• Controlar la redundancia de la información.
• Evitar pérdidas de información.
• Capacidad para representar toda la información.
• Mantener la consistencia de los datos.

Si somos novatos en el ambiente de las bases de datos relacionales pudiéramos pensar que con la normalización nuestros datos tienen una apariencia extraña, sin embargo, esto le permite a MySQL ser muy eficiente al momento de almacenar y recuperar los datos de las tablas, además de que nos da la flexibilidad de crecer y escalar nuestras aplicaciones sin la necesidad de reestructurar una base de datos a cada momento.

Seleccionar el tipo de dato apropiado

Una vez identificadas todas las tablas y columnas que necesita la base de datos, debemos determinar el tipo de dato de cada campo. Existen tres categorías principales que pueden aplicarse prácticamente a cualquier aplicación de bases de datos:

• Texto
• Números
• Fecha y hora

Cada uno de éstos presenta sus propias variantes, por lo que la elección del tipo de dato correcto no sólo influye en el tipo de información que se puede almacenar en cada campo, sino que afecta al rendimiento global de la base de datos.

A continuación se dan algunos consejos que nos ayudarán a elegir un tipo de dato adecuado para nuestras tablas:

• Identificar si una columna debe ser de tipo texto, numérico o de fecha.

  Esto suele ser un paso demasiado sencillo. Valores eminentemente numéricos como códigos postales o cantidades monetarias deben tratarse como campos de texto si decidimos incluir sus signos de puntuación, pero obtendremos mejores resultados si los almacenamos como números y solucionamos la cuestión del formato de alguna otra forma.

• Elegir el subtipo más apropiado para cada columna.

  Los campos de longitud fija (como CHAR) son generalmente más rápidos que los de longitud variable (como VARCHAR), aunque ocupan más espacio en disco.

  El tamaño de cada campo debe restringirse al mínimo en función de cuál pudiera ser la entrada más grande. Por ejemplo, si el valor en una columna de tipo entero es menor de mil, lo mejor es configurar esta columna como un SMALLINT de tres dígitos sin signo (lo que permite exactamente 999 valores distintos).

• Configurar la longitud máxima para las columnas de texto y numéricas, así como otros atributos.

  Puede que nosotros tengamos preferencias distintas, pero el factor más importante es siempre ajustar al máximo la información de cada campo en lugar de usar siempre tipos TEXT e INT genéricos (e ineficientes).

Utilizar índices apropiadamente

Los índices son un sistema especial que utilizan las bases de datos para mejorar su rendimiento global. Al definir índices en las columnas de una tabla, se le indica a MySQL que preste atención especial a dichas columnas.

MySQL permite definir hasta 32 índices por cada tabla y cada índice puede incorporar hasta 16 columnas. Aunque un índice de varias columnas puede no resultar de utilidad obvia a primera vista, lo cierto es que resulta muy útil a la hora de realizar búsquedas frecuentes sobre un mismo conjunto de columnas.

Dado que los índices hacen que las consultas se ejecuten más rápido, podemos estar incitados a indexar todas las columnas de nuestras tablas. Sin embargo, lo que tenemos que saber es que el usar índices tiene un precio. Cada vez que hacemos un INSERT, UPDATE, REPLACE, o DELETE sobre una tabla, MySQL tiene que actualizar cualquier índice en la tabla para reflejar los cambios en los datos.

¿Así que, cómo decidimos usar índices o no?. La respuesta es "depende". De manera simple, depende que tipo de consultas ejecutamos y que tan frecuentemente lo hacemos, aunque realmente depende de muchas otras cosas.

La razón para tener un índice en una columna es para permitirle a MySQL que ejecute las búsquedas tan rápido como sea posible (y evitar los escaneos completos de tablas). Podemos pensar que un índice contiene una entrada por cada valor único en la columna. En el índice, MySQL debe contar cualquier valor duplicado. Estos valores duplicados decrementan la eficiencia y la utilidad del índice.

Así que antes de indexar una columna, debemos considerar que porcentaje de entradas en la tabla son duplicadas. Si el porcentaje es demasiado alto, seguramente no veremos alguna mejora con el uso de un índice.

Otra cosa a considerar es qué tan frecuentemente los índices serán usados. MySQL puede usar un índice para una columna en particular si dicha columna aparece en la cláusula WHERE en una consulta. Si muy rara vez usamos una columna en una cláusula WHERE, seguramente no tiene mucha sentido indexar dicha columna. De esta manera, probablemente sea más eficiente sufrir el escaneo completo de la tabla las raras ocasiones en que se use esta columna en una consulta, que estar actualizando el índice cada vez que cambien los datos de la tabla.

Ante la duda, no tenemos otra alternativa que probar. Siempre podemos ejecutar algunas pruebas sobre los datos de nuestras tablas con y sin índices para ver como obtenemos los resultados más rápidamente. Lo único a considerar es que las pruebas sean lo más realistas posibles.

Usar consultas REPLACE

Existen ocasiones en las que deseamos insertar un registro a menos de que éste ya se encuentre en la tabla. Si el registro ya existe, lo que quisiéramos hacer es una actualización de los datos. En lugar de escribir el código que cumpla con esta lógica, y tener que ejecutar varias consultas, lo mejor es usar la sentencia REPLACE de MySQL.

Usar tablas temporales

Cuando estamos trabajando con tablas muy grandes, suele suceder que ocasionalmente necesitemos ejecutar algunas consultas sobre un pequeño subconjunto de una gran cantidad de datos. En vez de ejecutar estas consultas sobre la tabla completa y hacer que MySQL encuentre cada vez los pocos registros que necesitamos, puede ser mucho más rápido seleccionar dichos registros en una tabla temporal y entonces ejecutar nuestras consultas sobre esta tabla.

Crear una tabla temporal es tan sencillo como agregar la palabra TEMPORARY a una sentencia típica CREATE TABLE:

CREATE TEMPORARY TABLE tabla_temp
(
   campo1 tipoDato,
   campo2 tipoDeDato,
   ...
);

Una tabla temporal existe mientras dure la conexión a MySQL. Cuando se interrumpe la conexión MySQL remueve automáticamente la tabla y libera el espacio que ésta usaba. Nosotros podemos por supuesto eliminar esta tabla mientras estamos conectados a MySQL. Si una tabla nombrada tabla_temp ya existe en nuestra base de datos al momento de crear una tabla temporal con el mismo nombre, la tabla temporal oculta a la tabla no temporal.

MySQL también permite especificar que una tabla temporal sea creada en memoria si dicha tabla se declara del tipo HEAP:

CREATE TEMPORARY TABLE tabla_temp
(
   campo1 tipoDato,
   campo2 tipoDeDato,
   ...
) TYPE = HEAP;

Ya que las tablas del tipo HEAP son almacenadas en memoria, las consultas sobre estas tablas son ejecutadas mucho más rápido que en las tablas en disco no temporales. Sin embargo las tablas HEAP son ligeramente diferentes de una tabla normal y tienen algunas limitaciones propias.

Como en las sugerencias previas, lo único que nos queda es probar si con las tablas temporales nuestras consultas se ejecutan más rápidamente que usando la tabla que contiene una gran cantidad de datos. Si los datos están bien indexados puede que las tablas temporales no nos sean de mucha utilidad.

Usar una versión reciente de MySQL

La recomendación es simple y concreta, siempre que esté en nuestras manos, debemos usar la versión más reciente de MySQL que se encuentre disponible. Además de que las nuevas versiones frecuentemente incluyen muchas mejoras, cada vez son más estables y más rápidas. De esta manera, a la vez que sacamos provecho de las nuevas características incorporadas en MySQL, veremos significativos incrementos en la eficiencia de nuestro servidor de bases de datos.

Consideraciones finales

El último paso del diseño de la base de datos es adoptar determinadas convenciones de nombres. Aunque MySQL es muy flexible en cuanto a la forma de asignar nombre a las bases de datos, tablas y columnas, he aquí algunas reglas que es conveniente observar:

• Utilizar caracteres alfanuméricos.
• Limitar los nombres a menos de 64 caracteres (es una restricción de MySQL).
• Utilizar el guión bajo (_) para separar palabras.
• Utilizar palabras en minúsculas (esto es más una preferencia personal que una regla).
• Los nombres de las tablas deberían ir en plural y los nombres de las columnas en singular (es igual una preferencia personal).
• Utilizar las letras ID en las columnas de clave primaria y foránea.
• En una tabla, colocar primero la clave primaria seguida de las claves foráneas.
• Los nombres de los campos deben ser descriptivos de su contenido.
• Los nombres de los campos deben ser unívocos entre tablas, excepción hecha de las claves.

Los puntos anteriores corresponden muchos de ellos a preferencias personales, más que a reglas que debamos de cumplir, y en consecuencia muchos de ellos pueden ser pasados por alto, sin embargo, lo más importante es que la nomenclatura utilizada en nuestras bases de datos sea coherente y consistente con el fin de minimizar la posibilidad de errores al momento de crear una aplicación de bases de datos.