El muchos a muchos y la tabla del medio

1. El problema

Tu empresa tiene 9 vendedores y 53 zonas de venta.

SELECT
    count(*)
FROM
    employees;   --  9
SELECT
    count(*)
FROM
    territories;   -- 53

Con 9 personas y 53 zonas, alguien tiene que cubrir varias. Y así es: Nancy Davolio cubre dos —Neward y Wilton—; Andrew Fuller cubre siete.

Pregunta: ¿dónde apuntas quién cubre qué?

Parece trivial. No lo es, y el rato que le dediques aquí te ahorra la mitad de los errores del resto del curso.

2. El intento que no funciona nunca

Le añades a employees una columna zonas:

Se lee bien y no sirve para nada.

• ¿Quién cubre Boston? Tienes que leer las 9 casillas buscando la palabra dentro del texto. No estás consultando datos, estás buscando en un párrafo.
• Andrew deja Boston. Hay que editar la frase a mano, quitando además la coma correcta.
• ¿Cuántas zonas cubre cada uno? No se pueden contar. Se pueden contar comas, que no es lo mismo.

Esto está mal siempre, pase lo que pase con las zonas. Es el error que ya viste en que-es-una-base-de-datos-relacional: varios valores metidos en una celda.

3. El intento que sí funciona

Pruébalo al revés. En vez de guardar las zonas dentro del vendedor, guarda el vendedor dentro de la zona: una columna employee_id en territories.

employees                territories
+----+-----------+       +--------+-----------+-------------+
| id | apellido  |       | id     | zona      | employee_id |
+----+-----------+       +--------+-----------+-------------+
|  1 | Davolio   |◄──────| 19713  | Neward    |      1      |
|  2 | Fuller    |       | 06897  | Wilton    |      1      |
+----+-----------+       | 01730  | Bedford   |      2      |
                         | 02116  | Boston    |      2      |
                         | 02184  | Braintree |      2      |
                         | 75234  | Dallas    |    NULL     |
                         +--------+-----------+-------------+

Esto funciona. No hay ninguna lista dentro de ninguna celda. Nancy Davolio sigue teniendo sus dos zonas y Andrew Andrew sus siete: salen buscando sus filas. El employee_id se repite, y está bien — es el lado "muchos". Y Dallas, que hoy no tiene vendedor, se queda en NULL sin estorbar a nadie.

Es más: este diseño trae un regalo. Como territory_id es la clave primaria de territories, cada zona existe una sola vez, así que la regla "una zona tiene un solo vendedor" queda garantizada sin escribir ninguna restricción extra. Sale gratis.

Si la relación es de este tipo, el trabajo está hecho. No hace falta ninguna tabla del medio.

Fíjate en que las dos frases —"un vendedor cubre varias zonas" y "una zona tiene un solo vendedor"— no se contradicen. Miran en direcciones distintas: una de vendedor hacia zonas, la otra de zona hacia vendedor. Son las dos mitades de un uno a muchos, como "una madre tiene varios hijos" y "un hijo tiene una sola madre".

4. La pregunta que lo cambia todo

Llega el gerente y te dice que Boston es grande, que la van a cubrir dos vendedores.

Vas a la tabla y no tienes dónde escribirlo:

territories
+--------+-----------+-------------+
| id     | zona      | employee_id |
+--------+-----------+-------------+
| 01730  | Bedford   |      2      |
| 02116  | Boston    |      2      |
| 02184  | Braintree |      2      |
| 02116  | Boston    |      5      |  ← RECHAZADO: Boston ya está en la tabla
+--------+-----------+-------------+

territory_id es la clave primaria de territories. No pueden existir dos filas de Boston. Y no es que quede feo o incómodo: el motor rechaza la fila.

Ese regalo de la sección anterior era en realidad una condición. La clave primaria de territories no permitía que cada zona tuviera un solo vendedor: lo obligaba. Mientras la regla del negocio dijera lo mismo, iban de la mano. En cuanto la regla cambia, el diseño se cae entero.

Y la salida no es rehacer la columna. Es que el dato ya no cabe en ninguna de las dos tablas, porque ha dejado de pertenecer al vendedor o a la zona: pertenece a la pareja de los dos.

5. El cuaderno

Coges un cuaderno nuevo y escribes una línea por pareja:

Nancy  — Neward
Nancy  — Wilton
Andrew — Bedford
Andrew — Boston
...

Nancy ocupa 2 líneas. Andrew, 7. En total el cuaderno tiene 49 líneas. Y si mañana Boston la cubren dos, es una línea más — nada que rehacer.

Ese cuaderno es una tabla, y en Northwind se llama employee_territories:

employees            employee_territories          territories
(9 filas)                (49 filas)                 (53 filas)
    │                  ┌─────────────┐                   │
    └─────────────────►│ employee_id │                   │
                       │ territory_id│◄──────────────────┘
                       └─────────────┘

Tiene dos columnas y nada más, porque no hay nada más que decir sobre la pareja "Nancy — Wilton":

SELECT
    employee_id,
    territory_id
FROM
    employee_territories
ORDER BY
    employee_id, territory_id
LIMIT 5;

 employee_id | territory_id
-------------+--------------
           1 | 06897
           1 | 19713
           2 | 01581
           2 | 01730
           2 | 01833

Eso es una tabla puente: una tabla cuyo único trabajo es unir otras dos.

6. Las dos reglas del cuaderno

6.1 No inventar nombres. En la primera columna solo puedes escribir un empleado que exista en employees. En la segunda, una zona que exista en territories. Las dos columnas son claves foráneas, y el motor lo hace cumplir: un empleado que no existe se rechaza al insertar.

6.2 No repetir líneas. No puedes escribir "Nancy — Wilton" (employee - territory) dos veces. Ya está dicho.

Y aquí está la pieza que cuesta: para cumplir esa regla, ¿qué distingue a una línea de otra? Nada más que la pareja. En el cuaderno no hay ninguna otra cosa escrita, así que no hace falta numerar las líneas: la pareja es el nombre de la línea.

Por eso las dos columnas forman juntas la clave primaria. Ninguna sirve sola: employee_id 1 aparece dos veces en las cinco filas de arriba, y territory_id se repetiría en cuanto Boston tuviera sus dos vendedores.

6.3 Las dos cosas a la vez. Esto es lo que suele atascar: clave primaria y clave foránea no son excluyentes. Son dos papeles distintos y una columna puede tener los dos:

En employee_territories cada columna cumple las dos:

No se estorban. Es la misma casilla cumpliendo dos reglas: "este nombre tiene que existir" y "esta pareja no se puede repetir".

Y esa es la firma de una tabla puente: su clave primaria está hecha de sus claves foráneas. Cuando la veas en un esquema desconocido, ya sabes qué tienes delante sin que nadie te lo explique.

7. Cuándo hace falta y cuándo no

Junta lo de las secciones 3 y 4 y te queda una regla de tres casos:

Lo que obliga a la tabla del medio no es que un vendedor cubra varias zonas. Es que además una zona pueda tener varios vendedores. Hacen falta los dos "varios", y por eso se llama muchos a muchos.

Ahora el dato incómodo: en Northwind, hoy, ninguna zona la cubren dos vendedores. Las 49 asignaciones tienen un solo empleado, y hay 4 zonas sin nadie. Con los datos que hay, la columna en territories bastaría.

Entonces, ¿por qué Northwind trae la tabla puente? Porque la estructura la decide la regla del negocio, no los datos que ves. Que hoy nadie comparta zona puede significar "no está permitido" o puede significar "todavía no ha pasado", y las dos cosas se ven exactamente igual en los datos. Eso no se deduce: se pregunta.

8. La misma forma, con datos encima

employee_territories es el caso limpio. Hay otra tabla puente en Northwind, y es la que vas a usar el resto del curso: order_details.

Mismo esqueleto —order_id y product_id, las dos claves foráneas y las dos clave primaria juntas— pero cinco columnas en vez de dos:

Esas tres últimas no son del pedido ni del producto: son de la pareja. Cuántas unidades de ese producto en ese pedido, y a qué precio se vendió ahí.

Que pertenecen a la pareja no es una opinión, se ve en los datos. El producto 42 hoy cuesta 14,00:

SELECT
    order_id,
    unit_price,
    quantity,
    discount
FROM
    order_details
WHERE
    product_id = 42
ORDER BY
    order_id
LIMIT 4;

 order_id | unit_price | quantity | discount
----------+------------+----------+----------
    10248 |       9.80 |       10 |    0.000
    10309 |      11.20 |        2 |    0.000
    10311 |      11.20 |        6 |    0.000
    10332 |      11.20 |       10 |    0.200

El mismo producto, tres precios distintos. No es un error: es lo que costaba el día de cada pedido. Si ese precio viviera en products, subir la tarifa reescribiría el pasado y las ventas del año anterior cambiarían solas.

De aquí sale la consecuencia que más despista al llegar a Northwind: orders no guarda el total del pedido. No puede — el importe se calcula línea a línea, y las líneas están aquí.

Y aquí el muchos a muchos sí está en los datos, y lo tienes delante: en esas cuatro filas el producto 42 aparece en cuatro pedidos distintos. Un pedido lleva varios productos y un producto está en varios pedidos — los dos "varios" de la sección 7.

9. Errores comunes

1. Llegar a la tabla puente sin haberla necesitado. Si cada zona tuviera un solo vendedor, la columna en territories sería el diseño correcto y la tabla del medio, un rodeo. Antes de crear una, comprueba que los dos lados admiten varios.
2. Buscar la relación en la tabla equivocada. employees no apunta a employee_territories; es el cuaderno el que apunta a las dos listas. Si listas las claves foráneas, la relación aparece bajo employee_territories, no bajo employees.
3. Creer que PK y FK son opciones excluyentes. En una tabla puente, cada columna es las dos cosas.
4. Ponerle un id propio a la tabla puente. Un employee_territory_id autonumérico no identifica mejor: solo permite guardar la misma pareja dos veces, que es justo lo que había que impedir.
5. Meter datos de la pareja en las tablas de los lados. El descuento de una línea no es del producto ni del pedido. Si lo subes a products, deja de ser cierto para los pedidos viejos.

10. Ejercicios

1. Andrew deja de cubrir Boston. ¿Cuántas filas cambian con la tabla puente? ¿Y con la columna en territories? ¿Y con la lista dentro de employees?
2. Si los 9 vendedores cubrieran las 53 zonas, ¿cuántas líneas tendría el cuaderno? ¿Cuántas tiene de verdad?
3. order_details tiene 5 columnas y employee_territories 2. ¿Qué habría que apuntar de la pareja "Nancy — Wilton" para que a esa tabla le hiciera falta una tercera columna?

11. Pregunta de negocio

El gerente quiere pagar una comisión por zona cubierta. Te pide "la lista de vendedores con sus zonas" en una sola hoja de Excel, y te insiste en que quiere una fila por vendedor.

No hace falta SQL. Piensa qué le estás entregando exactamente y qué tienes que advertirle.