Parte II · Conectar datos

Capítulo 5 — La colección (relación N:M)

En el capítulo anterior conectaste tu primera pareja de tablas. Un jugador tiene muchos mazos, pero cada mazo es de un solo jugador. Lo resolviste con una clave foránea: una columna en mazo que apunta al dueño. Limpio y sencillo.

Pero hay una parte de Kriaturas que esa solución no alcanza, y es justo la más importante del juego: la colección. Las cartas que tiene cada jugador.

Piénsalo un momento. DragoFuego99 tiene muchas cartas: Chispín, Flamita, una Poción… Hasta aquí parece un 1:N más, como los mazos. Pero dale la vuelta. La carta Chispín, ¿de quién es? No es de DragoFuego99 y de nadie más. La tienen LunaVerde, PixelPunk y miles de jugadores a la vez. Una misma carta, en muchas colecciones.

Así que tenemos muchos por los dos lados: un jugador tiene muchas cartas, y una carta la tienen muchos jugadores. Eso ya no es uno a muchos. Es muchos a muchos, y la clave foránea de un solo lado se queda corta. En este capítulo vas a aprender el truco que lo resuelve, y de paso a hacer tu primer JOIN: la orden que vuelve a juntar tablas que habías separado.

¿Por qué importa?

Tu colección es el corazón de Kriaturas. Sin ella no hay nada: no puedes armar mazos, no puedes jugar, no puedes presumir de tener la criatura más rara del juego. Cualquier juego de cartas, de cromos o de objetos que hayas tocado guarda, por dentro, "quién tiene qué". Y casi siempre es una relación muchos a muchos.

Imagina que intentas guardarla sin saber el truco. Se te ocurre una idea aparentemente cómoda: añadir a la tabla jugador una columna cartas y meter ahí todas sus cartas, separadas por comas.

id	alias	cartas
1	DragoFuego99	Chispín, Chispín, Chispín, Flamita, Poción

Y se rompe enseguida. ¿Cómo buscas quién tiene a Chispín? Tendrías que rebuscar dentro de un texto, en todas las filas, a ver en cuáles aparece la palabra. ¿Y cómo sabes que esa "Chispín" es la carta con id = 1 y no otra con el mismo nombre? No lo sabes. Acabas de tirar por la borda todo lo que ganaste en el capítulo 2 con las claves primarias.

Probemos al revés: una columna dueños dentro de cada carta, con todos los jugadores que la tienen. Mismo desastre, pero del otro lado. Una carta popular tendría miles de nombres apretujados en una sola casilla.

El problema de fondo es siempre el mismo: estás intentando meter muchos valores en una casilla. Y una casilla guarda un dato, no una lista. La solución, como vas a ver, no es agrandar las casillas. Es crear una tabla nueva cuyo único trabajo sea anotar parejas: "este jugador tiene esta carta".

El truco: una tabla en medio

Vamos a pensarlo despacio, porque esta idea la vas a usar el resto de tu vida con las bases de datos.

Tienes dos tablas, jugador y carta, cada una con su clave primaria. Lo que quieres guardar no es un dato del jugador ni un dato de la carta. Es un dato del vínculo entre los dos: el hecho de que "el jugador 1 tiene la carta 2". Ese hecho no cabe en ninguna de las dos tablas, porque no pertenece a una sola: vive justo en medio.

Así que le damos su propia tabla. Una tabla intermedia (también la llaman tabla de cruce): una tabla cuyo trabajo es conectar las otras dos. Cada fila de esta tabla es una pareja. La vamos a llamar coleccion:

jugador_id	carta_id	cantidad
1	1	3
1	2	1
1	6	1
2	3	2

Léela como una lista de hechos. La primera fila dice: "el jugador 1 tiene la carta 1, y tiene 3 copias". La segunda: "el jugador 1 tiene la carta 2, 1 copia". Y así. Recordando el reparto de siempre, el jugador 1 es DragoFuego99 y la carta 1 es Chispín, así que esa primera fila significa: DragoFuego99 tiene 3 copias de Chispín.

Fíjate en las dos primeras columnas. jugador_id apunta a la tabla jugador, exactamente como la clave foránea del capítulo 4. Y carta_id apunta a la tabla carta, de la misma manera. La tabla intermedia tiene dos claves foráneas, una hacia cada lado:

   jugador            coleccion              carta
 ┌────┬────────┐   ┌──────────┬─────────┐   ┌────┬──────────┐
 │ id │ alias  │   │jugador_id│carta_id │   │ id │ nombre   │
 ├────┼────────┤   ├──────────┼─────────┤   ├────┼──────────┤
 │ 1  │ Drago… │◀──┼─ 1       │   1    ─┼──▶│ 1  │ Chispín  │
 │ 2  │ Luna…  │   │  1       │   2    ─┼──▶│ 2  │ Flamita  │
 └────┴────────┘   │  1       │   6    ─┼──▶│ 6  │ Poción   │
                   │  2     ──┼─────────┼─▶ ...
                   └──────────┴─────────┘

Cada fila de coleccion toca las dos tablas a la vez: con la izquierda señala a un jugador, con la derecha a una carta. Y aquí está lo bonito: con esta tabla en medio, los dos "muchos" caben sin problema. ¿DragoFuego99 tiene cinco cartas distintas? Pues cinco filas en coleccion con jugador_id = 1. ¿La carta Chispín la tienen mil jugadores? Pues mil filas con carta_id = 1. Muchos por un lado, muchos por el otro, y ni un solo dato repetido: el alias del jugador sigue viviendo una sola vez en su tabla, y el nombre de la carta una sola vez en la suya.

Ese es el truco. Una relación N:M (muchos a muchos) no se guarda con una clave foránea, sino con una tabla intermedia que recoge las parejas.

Crear la tabla intermedia

Vamos a crearla de verdad. Es un CREATE TABLE como los que ya conoces, pero con un par de novedades que merecen explicación:

SQL

CREATE TABLE coleccion (
    jugador_id INTEGER,
    carta_id   INTEGER,
    cantidad   INTEGER,
    PRIMARY KEY (jugador_id, carta_id),
    FOREIGN KEY (jugador_id) REFERENCES jugador(id),
    FOREIGN KEY (carta_id)   REFERENCES carta(id)
);

Vamos línea a línea:

jugador_id INTEGER y carta_id INTEGER: las dos columnas que forman la pareja. Cada una guarda un id, una hacia jugador y otra hacia carta.
cantidad INTEGER: cuántas copias de esa carta tiene ese jugador. Volvemos a esto en un momento; es un detalle que vale oro.
PRIMARY KEY (jugador_id, carta_id): aquí hay algo nuevo. La clave primaria no es una columna, sino dos a la vez.
Las dos líneas FOREIGN KEY: declaran que jugador_id apunta a jugador.id y carta_id a carta.id. Igual que en el capítulo 4, pero por partida doble. A partir de aquí la base de datos vigila las dos: no podrás meter en coleccion ni un jugador ni una carta que no existan.

Parémonos en la clave primaria, que es la primera vez que la ves así.

Una clave hecha de dos columnas

En el capítulo 2 te dejé caer una idea "para saber más": que la clave primaria puede estar formada por varias columnas a la vez. Ese momento ha llegado.

Piensa qué identifica una fila de coleccion. ¿Sirve el jugador_id solo? No: DragoFuego99 aparece en varias filas (tiene varias cartas). ¿Sirve el carta_id solo? Tampoco: Chispín aparece en varias filas (la tienen varios jugadores). Por separado, ninguno de los dos identifica una fila sin repetirse.

Pero juntos sí. La pareja (jugador_id, carta_id) no se repite nunca: no tiene sentido anotar dos veces que el jugador 1 tiene la carta 1. O la tiene o no la tiene. Por eso la clave primaria es la pareja.

Eso es una clave primaria compuesta: una clave primaria formada por dos o más columnas, que solo es única cuando se miran todas a la vez. Y de regalo te impide un error: como la base de datos no permite repetir la clave primaria, no podrás anotar dos veces la misma pareja jugador-carta. Si DragoFuego99 consigue otra Chispín, no añades una fila nueva: subes su cantidad de 3 a 4. Que es, precisamente, para lo que está esa columna.

El dato que vive en el vínculo

Esa columna cantidad esconde una idea elegante. ¿Dónde guardas cuántas copias de Chispín tiene DragoFuego99? No es un dato del jugador (no es "suyo", cambia según la carta). No es un dato de la carta (no es "suyo", cambia según el jugador). Es un dato de la relación entre los dos: de esa pareja concreta.

Por eso vive en la tabla intermedia. Las tablas de cruce pueden tener sus propios atributos, y son justo los datos que solo tienen sentido para la pareja. Aquí es cantidad. En otro juego podría ser la fecha en que conseguiste la carta, o si la tienes marcada como favorita. Cuando un dato no sabes en qué tabla meterlo porque "depende de las dos", casi siempre la respuesta es la tabla intermedia.

Llenar la colección

Con la tabla creada, vamos a anotar las cartas de nuestros jugadores. Recuerda el reparto: el jugador 1 es DragoFuego99, el 2 es LunaVerde; la carta 1 es Chispín, la 2 Flamita, la 3 Burbujo, la 6 Poción.

SQL

INSERT INTO coleccion (jugador_id, carta_id, cantidad) VALUES (1, 1, 3);
INSERT INTO coleccion (jugador_id, carta_id, cantidad) VALUES (1, 2, 1);
INSERT INTO coleccion (jugador_id, carta_id, cantidad) VALUES (1, 6, 1);
INSERT INTO coleccion (jugador_id, carta_id, cantidad) VALUES (2, 3, 2);
INSERT INTO coleccion (jugador_id, carta_id, cantidad) VALUES (2, 4, 1);

Léelo como frases: DragoFuego99 tiene 3 Chispín, 1 Flamita y 1 Poción; LunaVerde tiene 2 Burbujo y 1 Hojarasca. La tabla coleccion queda así:

jugador_id	carta_id	cantidad
1	1	3
1	2	1
1	6	1
2	3	2
2	4	1

Mira la tabla y date cuenta de lo que tienes delante. Solo números. No hay ni un alias ni un nombre de carta a la vista, y aun así está todo: cada fila conecta un jugador real con una carta real a través de sus id. Es eficiente y no repite nada. Pero también es ilegible para una persona: nadie quiere leer "1 tiene 1". Para volver a ver alias y nombres, necesitamos juntar las tablas otra vez. Y para eso existe el JOIN.

El reencuentro: tu primer JOIN

Aquí conviene parar y mirar lo que llevas hecho en el libro, porque hay una tensión interesante.

Llevas cinco capítulos separando datos. Sacaste los jugadores a su tabla, las cartas a la suya, los mazos a la suya. Todo para no repetir nada y no contradecirte. Y está bien: es la base de un buen diseño.

Pero ahora pagas el precio. La información está repartida. Si quieres responder "¿qué cartas tiene DragoFuego99, con sus nombres?", el dato no está en ninguna tabla sola: el alias está en jugador, los nombres están en carta, y quién tiene qué está en coleccion. La respuesta vive en las tres a la vez.

El JOIN (combinación) es la orden que las vuelve a juntar. Si separar fue guardar las piezas en cajones distintos para tenerlas ordenadas, el JOIN es abrir los cajones y recomponer la figura cuando la necesitas. Separamos para guardar; juntamos para preguntar.

Vamos a responder esa pregunta: las cartas de DragoFuego99, con sus nombres.

SQL

SELECT jugador.alias, carta.nombre, coleccion.cantidad
FROM jugador
JOIN coleccion ON jugador.id = coleccion.jugador_id
JOIN carta     ON carta.id   = coleccion.carta_id
WHERE jugador.alias = 'DragoFuego99';

Parece mucho de golpe, pero cada trozo es una idea que ya conoces. Vamos despacio:

SELECT jugador.alias, carta.nombre, coleccion.cantidad: las columnas que quieres ver. Como ahora hay varias tablas en juego, escribimos tabla.columna para que no haya dudas de cuál es cuál.
FROM jugador: empezamos por la tabla de jugadores.
JOIN coleccion ON jugador.id = coleccion.jugador_id: pega la tabla coleccion, emparejando cada jugador con sus filas de colección. El ON es la parte importante: dice cómo se conectan las dos tablas. "Une la fila del jugador con las filas de colección donde el jugador_id coincida con su id."
JOIN carta ON carta.id = coleccion.carta_id: y ahora pega la tabla carta, emparejando cada fila de colección con su carta por el carta_id.
WHERE jugador.alias = 'DragoFuego99': el filtro de siempre, el del capítulo 3. De todo lo que ha juntado, quédate solo con las filas de DragoFuego99.

El resultado es exactamente lo que querías:

alias	nombre	cantidad
DragoFuego99	Chispín	3
DragoFuego99	Flamita	1
DragoFuego99	Poción	1

Léelo y disfrútalo un segundo: alias de verdad, nombres de verdad, cantidades de verdad. La base de datos ha ido a tres tablas distintas, ha seguido los hilos de los id y ha recompuesto la foto. Tú solo dijiste qué querías y cómo se conectan las tablas; el trabajo de cruzarlas lo hizo ella.

Esa es la pareja de ideas con la que se construyen las bases de datos de verdad: separas los datos para guardarlos bien, y los juntas con JOIN para preguntarles cosas.

Una nota sobre estilos. Verás SQL antiguo que hace los JOIN de otra manera: listando las tablas separadas por comas y poniendo las conexiones en el WHERE (FROM jugador, coleccion, carta WHERE jugador.id = coleccion.jugador_id AND …). Funciona y hace lo mismo, pero mezcla las conexiones con los filtros y se lía enseguida. El JOIN ... ON separa cada cosa en su sitio y se lee mucho mejor. Usa este; es el recomendado hoy.

Ejercicio resuelto

Enunciado. Un mazo no es solo un nombre: es un conjunto de cartas. Igual que un jugador tiene muchas cartas y una carta la tienen muchos jugadores, un mazo contiene muchas cartas y una misma carta puede estar en muchos mazos. Diseña la tabla que guarda qué cartas hay en cada mazo, mete las cartas del "Mazo eléctrico" (id = 1, de DragoFuego99) y luego consulta su contenido con los nombres de las cartas.

Solución, paso a paso.

¿Qué tipo de relación es? Muchos a muchos entre mazo y carta: un mazo tiene muchas cartas, una carta está en muchos mazos. Misma situación que la colección. Así que misma solución: una tabla intermedia.
Diseño la tabla. La llamo mazo_carta. Dos claves foráneas (una a mazo, otra a carta), clave primaria compuesta por las dos, y un atributo propio del vínculo: cuántas copias de esa carta lleva el mazo.

sql CREATE TABLE mazo_carta ( mazo_id INTEGER, carta_id INTEGER, cantidad INTEGER, PRIMARY KEY (mazo_id, carta_id), FOREIGN KEY (mazo_id) REFERENCES mazo(id), FOREIGN KEY (carta_id) REFERENCES carta(id) );

Fíjate en que es la misma plantilla que coleccion, cambiando jugador por mazo. No es casualidad: todas las relaciones N:M se resuelven igual. Una vez ves el patrón, lo repites de carrerilla.

Meto las cartas del Mazo eléctrico. Digamos que lleva 2 Chispín (carta 1) y 2 Flamita (carta 2):

sql INSERT INTO mazo_carta (mazo_id, carta_id, cantidad) VALUES (1, 1, 2); INSERT INTO mazo_carta (mazo_id, carta_id, cantidad) VALUES (1, 2, 2);

Consulto su contenido con nombres. Quiero ver el nombre del mazo y los nombres de sus cartas, así que junto las tres tablas igual que antes:

sql SELECT mazo.nombre, carta.nombre, mazo_carta.cantidad FROM mazo JOIN mazo_carta ON mazo.id = mazo_carta.mazo_id JOIN carta ON carta.id = mazo_carta.carta_id WHERE mazo.id = 1;

Resultado:

nombre (mazo)	nombre (carta)	cantidad
Mazo eléctrico	Chispín	2
Mazo eléctrico	Flamita	2

El patrón es idéntico al de la colección, solo cambian los nombres de las tablas. Si lo has seguido sin perderte, ya sabes modelar y consultar cualquier relación muchos a muchos. Es una de las herramientas que más vas a usar.

Errores comunes

Intentar meter "muchas cartas" en una columna del jugador. El error del que va medio capítulo. Una lista de cartas separadas por comas, o una columna carta1, carta2, carta3…, parece más rápido y siempre acaba mal: no puedes buscar, no puedes contar, no puedes cruzar. Muchos a muchos se guarda con una tabla intermedia, no estrujando valores en una casilla.

Olvidar una de las dos claves foráneas. La tabla intermedia tiene dos lados. Si solo declaras la clave foránea hacia jugador y te olvidas de la de carta, la base de datos dejará de vigilar ese lado y podrás colar un carta_id que no existe. Las relaciones N:M siempre llevan dos.
Hacer un JOIN sin la condición ON. Este es famoso, y conviene conocerlo. Si juntas dos tablas pero no dices cómo se conectan, la base de datos no se queja: combina cada fila de una con cada fila de la otra. Si una tabla tiene 100 filas y la otra 100, te devuelve 10.000. Eso se llama producto cartesiano, y casi nunca es lo que quieres. Cuando una consulta te devuelva un montón absurdo de filas repetidas, lo primero que debes mirar es si te falta el ON.
Anotar dos veces la misma pareja. Querer "sumar" una carta a la colección con un INSERT nuevo cuando esa pareja jugador-carta ya existe. La clave primaria compuesta lo impide (bien hecho por su parte). Si DragoFuego99 ya tiene Chispín y consigue otra, no insertas otra fila: actualizas su cantidad.

Resumen

En este capítulo modelaste la parte más viva de Kriaturas, la colección, y aprendiste a juntar tablas:

Una relación N:M (muchos a muchos) aparece cuando los dos lados tienen muchos del otro: un jugador tiene muchas cartas y una carta la tienen muchos jugadores. La clave foránea de un solo lado, la del capítulo 4, no basta.
Se resuelve con una tabla intermedia (o de cruce): una tabla nueva cuyas filas son las parejas. Lleva dos claves foráneas, una hacia cada tabla, y su clave primaria suele ser la pareja de ambas (una clave primaria compuesta).
La tabla intermedia puede tener atributos propios del vínculo, como cantidad: datos que no son de una tabla ni de la otra, sino de la relación.
Para volver a unir lo que separaste, usas el JOIN. Con JOIN ... ON le dices a la base de datos qué tablas juntar y por qué columnas se conectan; ella recompone la información repartida.
Un JOIN sin condición de unión produce un producto cartesiano: todas las combinaciones posibles. Acuérdate del ON.

Con esto tu modelo ya tiene varias tablas hablando entre sí: jugador, carta, mazo, coleccion y mazo_carta. Empieza a ser un sistema de verdad… y también empieza a ser difícil de tener entero en la cabeza. Antes de seguir añadiendo piezas, conviene parar y dibujarlo: ver de un vistazo qué entidades hay y cómo se conectan. Eso es el diseño conceptual, y es justo lo que harás en el próximo capítulo.

Glosario y para saber más

Relación N:M (muchos a muchos): vínculo entre dos tablas en el que cada fila de una puede relacionarse con muchas de la otra, y al revés (un jugador tiene muchas cartas y una carta la tienen muchos jugadores). - Tabla intermedia (o de cruce): tabla que representa una relación N:M. Cada fila es una pareja; lleva una clave foránea hacia cada una de las tablas que conecta (coleccion, mazo_carta). - Clave primaria compuesta: clave primaria formada por dos o más columnas juntas; ninguna basta por sí sola, pero la combinación es única ((jugador_id, carta_id) en coleccion). - JOIN (combinación): operación que junta dos o más tablas en una sola consulta, emparejando sus filas según una condición (ON). Vuelve a unir los datos que se habían repartido en tablas distintas. - Producto cartesiano: resultado de combinar todas las filas de una tabla con todas las de otra, lo que pasa cuando un JOIN no lleva condición de unión. Casi siempre es un error.

Para saber más:

El JOIN que has usado solo muestra las filas que casan por los dos lados. Si un jugador no tuviera ninguna carta, no aparecería en el resultado. Existen variantes (LEFT JOIN y compañía) para decir "enséñame también a los que no tienen nada"; las verás en el capítulo de consultas avanzadas.
Aquí has mirado qué cartas tiene cada jugador. La pregunta gemela es cuántas: "¿cuántas cartas distintas tiene DragoFuego99?", "¿cuál es la carta más popular?". Para contar y agrupar hace falta otra herramienta (GROUP BY y las funciones de agregado), que también llega en el capítulo de consultas que molan.