Tipos de relación: 1:1, 1:N, M:N
En resumen: Una relación entre dos tablas es uno a uno, uno a muchos o muchos a muchos, y su tipo decide dónde va la clave foránea y cuándo necesitas una tabla de enlace.
Problema
Una tarjeta y etiquetas
La tienda lanza dos novedades. La primera es una tarjeta de fidelidad: cada cliente puede tener una tarjeta con puntos. La segunda son las etiquetas de los productos, como «vegano», «sin gluten» y «ecológico», para que la web pueda filtrar por ellas.
¿Dónde las guardas? ¿Una columna card_number en customers? ¿Una columna tags en products con el texto «vegano, ecológico»? La respuesta depende de una pregunta: cuántas cosas de un lado corresponden a cuántas del otro.
La idea
Uno a muchos (1:N)
Un cliente tiene muchos pedidos. Cada pedido pertenece a un cliente. Es una relación uno a muchos, que se escribe 1:N.
La clave va en el lado «muchos». Cada pedido dice de quién es con customer_id:
customers
- PK
idinteger nametext *emailtext *citytextjoined_attext *
orders
- PK
idinteger - FK
customer_idinteger *→ customers ordered_attext *statustext *
Al revés no funciona. ¿Cuántas columnas order_1, order_2 debería tener un cliente? María tiene 12 pedidos, y mañana tendrá 13.
Es la relación más común. En la tienda, una categoría y sus productos son así, y también un producto y sus reseñas.
Pruébalo
Productos por categoría
¿Cuántos productos hay en cada categoría que tiene productos? Muestra el nombre de la categoría y la cantidad.
Solución
SELECT c.name, COUNT(*) AS products
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name;Una categoría y sus productos son 1:N, así que unes products.category_id con categories.id y agrupas por categoría. Salen 6 categorías. Faltan «Alimentos» y «Hogar»: son categorías de primer nivel sin productos directamente en ellas, solo subcategorías.
La idea
Uno a uno (1:1)
Cada cliente tiene como máximo una tarjeta, y cada tarjeta pertenece a un cliente. Es uno a uno, 1:1. Se guarda como 1:N, con una clave hacia el cliente, más una regla extra: UNIQUE.
loyalty_cards
- PK
idinteger - FK
customer_idinteger *→ customers numbertext *pointsinteger *issued_attext *
Cinco clientes tienen tarjeta. Prueba a darle una segunda a María:
UNIQUE en customer_id no deja entrar una segunda fila para el mismo cliente. Sin él, la relación sería 1:N.
¿Por qué no columnas en customers? También funciona. A menudo 1:1 son simplemente más columnas en la misma tabla. Una tabla aparte tiene sentido cuando la mayoría de las filas no tienen la segunda parte (15 de los 20 clientes no tienen tarjeta y tendrían celdas vacías), o cuando tiene vida propia: la tarjeta se pierde, se reemplaza, tiene sus propios puntos.
Pruébalo
Quién todavía no tiene tarjeta
Marketing quiere ofrecer una tarjeta a cada cliente que todavía no tiene. Muestra sus nombres.
Solución
SELECT c.name
FROM customers c
LEFT JOIN loyalty_cards l ON l.customer_id = c.id
WHERE l.id IS NULL;LEFT JOIN conserva a todos los clientes, y para los que no tienen tarjeta las columnas de loyalty_cards son NULL. WHERE l.id IS NULL deja solo a esos: 15 clientes.
La idea
Muchos a muchos (M:N)
Un pedido tiene muchos productos. Un producto está en muchos pedidos. Es muchos a muchos, M:N.
Aquí la clave no puede ir en ninguna de las dos tablas. La solución es una tercera tabla, una tabla de enlace, con una fila por cada pareja. En la tienda es order_items: cada fila es un producto en un pedido, con la cantidad y el precio.
products
- PK
idinteger nametext *- FK
category_idinteger→ categories pricereal *stockinteger *added_attext *
order_items
- PK
order_idinteger→ orders - PK
product_idinteger→ products quantityinteger *unit_pricereal *
¿Qué pedidos llevan el café (producto 17)?
Con las etiquetas pasa lo mismo. Un producto tiene muchas etiquetas, y una etiqueta está en muchos productos:
tags
- PK
idinteger nametext *
product_tags
- PK
product_idinteger→ products - PK
tag_idinteger→ tags
Míralo
La pareja es la clave
La clave primaria de order_items es la pareja (order_id, product_id). Prueba a añadir el pan por segunda vez al pedido 60, que ya lo tiene:
La base de datos no permite la misma pareja dos veces. Un producto está en el pedido una vez, y las piezas de más van en quantity. Si no, el informe «cuántas veces se pidió el pan» contaría dos veces el mismo producto de un pedido.
Pruébalo
Etiquetas en la web
Junto a cada etiqueta, la web muestra cuántos productos la tienen. Muestra el nombre de la etiqueta y la cantidad.
Solución
SELECT t.name, COUNT(*) AS products
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
GROUP BY t.id, t.name;Agrupas product_tags por etiqueta: «vegano» 10, «sin gluten» 11, «ecológico» 6. Suman 27 parejas, aunque solo 13 productos tienen etiqueta, porque la mayoría tiene más de una.
Ejercicio
Dos tareas
1. La página de las manzanas muestra sus etiquetas. ¿Cuáles son?
Las etiquetas del producto «Manzanas»:
Solución
SELECT t.name
FROM tags t
JOIN product_tags pt ON pt.tag_id = t.id
JOIN products p ON p.id = pt.product_id
WHERE p.name = 'Manzanas';La misma tabla de enlace, leída desde el otro lado: del producto a las etiquetas. «vegano», «sin gluten» y «ecológico».
2. Un cliente busca productos que sean a la vez veganos y sin gluten. Muestra sus nombres.
Los productos con las dos etiquetas, «vegano» y «sin gluten»:
Solución
SELECT p.name
FROM products p
JOIN product_tags pt ON pt.product_id = p.id
JOIN tags t ON t.id = pt.tag_id
WHERE t.name IN ('vegano', 'sin gluten')
GROUP BY p.id, p.name
HAVING COUNT(*) = 2;WHERE t.name = 'vegano' AND t.name = 'sin gluten' no encuentra nada, porque cada fila tiene una etiqueta. Tomas las filas con cualquiera de las dos etiquetas, agrupas por producto y dejas las que tienen COUNT(*) = 2. Salen 8 productos: el pan blanco es solo vegano, y el queso blanco es solo sin gluten.
Test