14.- Trabajar con más de una tabla

Introducción

Ahora que ya nos hemos relajado un rato y nos hemos divertido un poco mandando emilios a todas nuestras amistades, vamos a seguir adelante. Ahora vamos a ver cómo se trabaja con php y mysql cuando nos encontramos con más de una tabla. Y cómo lo primero es lo primero…

¿Por qué es necesario separar la información en varias tablas?

Lo mejor es verlo sobre un ejemplo práctico. Supongamos que queremos que otras personas contribuyan a nuestra base de datos de refranes. Para saber de quién es cada refrán deberíamos anotar también el autor y no estaría mal guardar también el email por si necesitamos ponernos en contacto con ellos.

Podríamos cambiar nuestra tabla, añadiéndole dos campos: autor e email, lo podemos hacer fácilmente con ALTER

ALTER TABLE refranero ADD COLUMN autor VARCHAR(80);
ALTER TABLE refranero ADD COLUMN email VARCHAR(150);

La estructura después de los cambios nos quedaría así:

Image

Ahora podemos asignarle a cada refrán su autor con el email de contacto utilizando UPDATE.

Una vez actualizada, nos quedaría algo parecido a esto:

Image

Estamos encantados, incluso podemos incluso hacernos una lista de autores con sus emails:

SELECT DISTINCT autor, email FROM refranero;

Image

Hasta ahora todo parece precioso, pero nos vamos a encontrar con una serie de problemas.

  • ¿Qué ocurre si uno de los autores, por ejemplo pepe, cambia de email? Empezaría a enviar refranes utilizando su nueva dirección pero los refranes anteriores a esa fecha seguirían teniendo el antiguo email. Podrías llegar a pensar que se trata de dos personas distintas. Si se toma la molestia de informarte de que ha cambiado de email, podrías tratar de cambiarlos todos para que tuvieran el nuevo, pero con que se te despistara uno ya tendrías los datos incorrectos. Este tipo de problema se llama: anomalía de actualización.
  • Si por alguna razón decidieras borrar los refranes de pepe, perderías también su email, con lo que te quedarías sin datos de contacto, estarías perdiendo los datos de un colaborador, solo porque no te acaban de convencer sus refranes. Anomalía de borrado se llama esto.
  • También podría suceder que pepe un día ponga pepe, otro día ponga jose, al cabo de un tiempo ponga pepe pérez

Este tipo de problemas se arreglan en un periquete si separamos la información sobre los autores de la información sobre los refranes lo que nos lleva a la primera regla de oro de las bases de datos: Guardar las cosas por separado.

Lo que vamos a hacer es crear una tabla para nuestros autores por un lado, y por otro lado vamos a modificar nuestra tabla de refranes para incluir un campo que nos nos permita relacionar cada oveja con su pareja, perdón cada refrán con su autor.

Image

Ahora podemos ver dos tablas, en una hay varios refranes y en la otra la lista de autores. La columa autorID de la tabla refranero es la que establece la relación entre las dos tablas, indicando que pepe ha enviado los refranes, 5,3 y 6, Anita los refranes 7 y 8 y Juan el refrán número 2. Además como ahora cada autor consta una sola vez y de forma completamente independiente, hemos evitado todos los problemas que veíamos antes.

Lo importante es que como tenemos que guardar dos cosas distintas (autores y refranes) lo hemos hecho en tablas distintas. Cada tipo de dato que queremos guardar deberíamos guardarlo en su propia tabla.

Montarnos estas tablas a partir de lo que ya no tenemos, no es difícil, basta con seguir los siguientes pasos.

Primero nos creamos la tabla autores

CREATE TABLE autores (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(150),
email VARCHAR(150),
);

En nuestra tabla refranero, primero nos libramos de los dos campos extra que creamos para añadir el autor y el email

ALTER TABLE refranero DROP COLUMN autor;
ALTER TABLE refranero DROP COLUMN email;

Y luego le añadimos la columna id de autor que nos permitirá asignar un autor a cada refrán

ALTER TABLE refranero ADD COLUMN autorID INT;

Cómo trabajar con tablas múltiples

Con los datos separados en dos tablas, lo de mostrar los datos puede parecernos algo más complicado. Cuando teníamos una sola tabla lo hacíamos con un código simple:

<?php

// Seleccionamos todos los refranes de la base de datos
$resultado = @mysql_query("SELECT * FROM refranero");

if (!$resultado) {
echo ("<p> Me temo que te has colado en la query:" . mysql_error() . "</p>");
exit();
}

// mostramos todos los refranes
while ($fila = mysql_fetch_array($resultado)) {
echo ("<p>ID: " . $fila['ID'] . "<br />");
echo ("Refrán: " . $fila['refran'] . "<br />");
echo ("Fecha: " . $fila['fecha'] . "<br /></p>");

}
?>

Ahora las cosas cambian un poco, vamos a necesitar hacer “joins” que te permiten tratar los datos almacenados en distintas tablas como si estuvieran en una sola. Son la gasolina que nos da la verdadera potencia de las bases de datos relacionales.

En nuestro caso las columnas que nos interesan son refrán, y fecha en la tabla refranero y autor e email en la tabla autores, la condición que necesitamos es que la columna aid de la tabla refranero, sea igual a la columna autorID de la tabla autores.

¿Cómo lo hacemos?

SELECT refran, fecha, autor, email FROM refranero, autores WHERE autorID = autores.ID;

Cómo en las dos tablas tenemos una columna ID, hemos especificado el nombre de la tabla a la que nos referimos con el término ID (autores.ID). Si no lo especificas, mysql no sabrá a cual de las columnas ID te refieres y te mostrará el siguiente error:

Quote ERROR 1052: column ‘ID’ in where clause is ambiguous

Ahora que ya sabemos como extraer nuestros datos cuando los tenemos repartidos por varias tablas, vamos a reescribir nuestro código

<?php

// Seleccionamos todos los refranes de la base de datos
$resultado = @mysql_query("SELECT refran, fecha, autor, email FROM refranero, autores WHERE autorID = autores.ID");

if (!$resultado) {
echo ("<p> Me temo que te has colado en la query:" . mysql_error() . "</p>");
exit();
}

// mostramos todos los refranes
while ($fila = mysql_fetch_array($resultado)) {
echo ("<p>ID: " . $fila['ID'] . "<br />");
echo ("Refrán: " . $fila['refran'] . "<br />");
echo ("Fecha: " . $fila['fecha'] . "<br /></p>");
echo ("Autor: " . $fila['autor'] . "<br /></p>");
echo ("email: " . $fila['email'] . "<br /></p>");

}
?>

Esta posibilidad de combinar los datos guardados en tablas distintas es el quid de la cuestión y a medida que vayáis practicando y familiarizándoos veréis lo realmente útil que es.

Veamos otro ejemplo, supongamos que queremos sacar sólo las queries que ha mandado pepe. En este caso nuestra query sría

SELECT refran FROM refranero, autores WHERE nombre=”pepe” AND autorID = autores.ID

En este caso los resultados que vamos a obtener vienen todos de la tabla refranero pero utilizamos los datos de la tabla autores para saber exactamente cuales queremos mostrar.

Tipos de relaciones

Los relaciones entre los datos repartidos en las diversas tablas pueden ser de varios tipos:

Relaciones uno a uno: para este tipo de relación basta con una sola tabla. Un ejemplo lo hemos visto en la tabla de autores. A cada Autor le corresponde un email y a cada email le corresponde un solo autor. Por tanto, no hace falta separar los datos y se pueden reflejar en una sola tabla.

Relaciones muchos a uno: es algo más complicada que la anterior pero ya la hemos visto también. Cada uno de nuestros refranes está asociada con un autor, en cambio cada autor puede estar asociado a varios refranes. Ya hemos visto la problemática de este tipo de soluciones. Separando los datos en dos tablas y usando una columna ID para guardar la relación, arreglamos los problemas.

Relaciones uno a muchos: este tipo de relación todavía no lo hemos visto así que vamos con un ejemplo. Hasta ahora hemos asumido que cada autor tiene una sola dirección de correo. Pero si quisiéramos que cada autor pudiera dar de alta varios emails, nos encontraríamos con una relación de este tipo: un autor puede tener muchos emails, pero cada email pertenece a un solo autor.

Mucha gente intenta abordar este tipo de relaciones intentando guardar varios valores en un solo campo:

Image

Parece simple ¿eh? Pues intenta imaginar el código PHP que necesitarías para obtener un determinado email para un autor. Además tendrías que permitir valores muy largos para el campo email, lo que te llevaría a un gasto innecesario de disco duro ya que la mayoría de los autores sólo tendrían un email.

La solución para una relación uno a muchos es muy parecido a la que vimos para la relación muchos a uno. Simplemente hay que darle la vuelta. Hay que separar la tabla autores en dos tablas: autores e emails, y asociar el email con el autor utilizando una columna autorID en la tabla de emails

Image

Con un simple select podríamos saber los emails que corresponden a un determinado autor:


SELECT email FROM autores, emails WHERE nombre=”pepe” AND autorID = autores.ID

Relaciones complejas (de muchos a muchos)

Supongamos que nuestro refranero tiene mucho éxito y empieza a crecer tanto que empieza a ser difícil de manejar. Así que decides crear categorías para ordenar las cosas un poco.

Cómo eres un alumno aplicado y recuerdas lo que acabamos de ver, identificas las categorías como una cosa distinta y les creas su propia tabla:

CREATE TABLE categorías (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cat_nom VARCHAR(100),
);

Ahora te toca asignar tus refranes a las distintas categorías y de repente te das cuenta de que cada refrán puede pertenecer a varias categorías, y además una categoría va a incluir varios refranes, estamos ante una relación de muchos a muchos.

La forma correcta de guardar este tipo de relaciones es utilizando una tabla intermedia. Es una tabla que no guarda datos, pero que se utiliza para definir relaciones entre los datos de otras tablas. Nos quedaría así:

Image

Lo que hace nuestra tabla cat_refran es guardar en una columna el ID del refran y en la otra el ID que identifica a la categoría a la que pertenece. Una tabla de este tipo se crea igual que cualquier otra, la única diferencia está en la clave.

Hasta ahora siempre hemos utilizado el campo ID como campo clave primario. Asignar un campo clave primario a una columna lo que significa es que en ese campo no se pueden introducir datos duplicados, todos tienen que ser distintos,

En el caso de nuestra tabla intermedia, no vamos a utilizar una sola columna como clave. Cada ID de un refrán puede aparecer varias veces, ya que un refrán puede pertenecer a varias categorías. También los id de categoría pueden aparecer varias veces ya que una categoría puede tener asignados varios refranes. Lo que no queremos es que uno de estos pares pueda aparecer más de una vez. Así que vamos a crear una clave primaria que abarque las dos columnas.

CREATE TABLE tw_categ_refran  (
RID INT NOT NULL ,
CID INT NOT NULL ,
PRIMARY KEY ( RID , CID )
);

Ahora ya hemos creado una tabla intermedia con las dos columnas como clave primaria lo que además, mejorará las búsquedas.

Ahora que ya tenemos nuestra tabla intermedia y nuestra tabla de categorías rellenas, vamos a practicar algunas queries:

Vamos a recuperar todos los refranes asignados a la categoría animales:

SELECT refran FROM refranero, categorias, categ_refran
WHERE cat = “animales”
AND CID = categorias.ID
AND RID = refranero.ID;

Vamos a listar todos las categorías que contengan refranes que empiecen por “A”.

SELECT cat FROM refranero, categorias, categ_refran
WHERE refran LIKE “A%”
AND CID = categorias.ID
AND RID = refranero.ID;

Vamos a hacer una query que utiliza también la tabla de autores, así que esta vez nos vamos enfrentar con 4 tablas: vamos a obtener una lista de los autores que han enviado algún refrán a la categoría autores

SELECT autor FROM refranero,autores, categorias, categ_refran
WHERE cat= ”animales”
AND CID = categorias.ID
AND RID = refranero.ID
AND autorID = autores.ID;

En este tema hemos hecho sólo una pequeña introducción sobre como trabajar con bases de datos. Para una visión más profunda, consultad el manual de amanda de introducción a las bases de datos.

Volver an índice

2 responses to “14.- Trabajar con más de una tabla

  1. ewnklr

    excelente explicacion! muy clara, gracias!

  2. todo bien explicado ,… es lo q ue andaba buscando ……….. gracisa

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s