Duda con Foreign Key e integridad referencial

[alvaro_pasto]

Hola.

Estoy diseñando una BDD después de tener el tema aparcado durante algún tiempo, con lo que algunos conceptos se me han enfriado. Expongo la situación:

Tengo dos tablas (entre otras). En la tabla resultados guardo los resultados de diferentes carreras en una estructura como la siguiente:

- posicion 1
- posicion 2
- posicion 3
...

En la tabla pilotos guardo los datos de los pilotos de la siguiente forma:

- idpiloto
- nombre
...

Tanto los campos posición como el campo idpiloto son tinyint unsigned, y la relación sería 1:N, osea, un piloto puede estar en varios registros de la tabla resultados (hay varios resultados de carreras diferentes).

La duda es la siguiente ¿Debería crear solo una Foreign Key de esta manera...

Código:

CONSTRAINT `fk_t_resultados__t_pilotos`
    FOREIGN KEY (`pos_1` , `pos_2` , `pos_3` , `pos_4` , `pos_5` , `pos_6` , `pos_7` , `pos_8` , `pole` , `rapida` )
    REFERENCES `topracin_pruebas`.`t_pilotos` (`num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` , `num_piloto` )

...o debería crear una Foreign Key diferente por cada campo?

Muchas gracias por la ayuda.

[gnzsoloyo]

Ni una ni otra. Estás planteando mal el modelo referencial. Lo que tienes es en realidad son las tablas (ejemplificando):

• Corredor(Corredor_id, nombre)
• Pista(pista_id, nombre, ubicacion, datos...)
• Carrera(carrera_id, nombre, fecha)
• Corredor_carrera(carrera_id, corredor_id, posicion_salida, posicion_final)

Este esquema te puede resolver la consulta de quienes participaron y en que posición llegaron en cada carrera y donde se hizo, cuando se hicieron , cuántas ganaron, y demás datos.

Tienes que recordar que:
1. Una FK es una clave referencial que relaciona un campo dado de una tabla con el campo clave (PK) de otra tabla. En ese sentido, no existe tu modelo una tabla que contenga al mismo tiempo en la misma tabla todos los campos: `pos_1` , `pos_2` , `pos_3` , `pos_4` , `pos_5` , `pos_6` , `pos_7` , `pos_8` , `pole` , `rapida`. ¿O sí?
2. Un campo posicion1, posicion2, etc., en una tabla no tiene ningún sentido porque eso requeriría poner tantos campos como posiciones, o bien un conjunto de posiciones acotado, con lo que perderías la información de aquellos corredores que hayan salido fuera de esas posiciones (supongamos que guardas 16 y tienes 20 corredores, ¿qué pasó con los otros?). En cambio, una tabla que conserve las posiciones relacionadas con la carrera y con el conductor, requiere solamente esos campos para obtener todos los datos.
3. Además, poner tantos campos UNIQUE (ya que no puedes usar todos los campos como PK) lo único que haría es reducir la performance por exceso de índices.
4. Un efecto adicional es que aún así, en el esquema que propones, la misma carrera podría figurar en más de un registro, con sólo ingresar dos corredores con posiciones cambiadas. El sistema no lo notaria, porque poner 1,4,2,5 ó 1,2,4,5 como ID de conductores sería igualmente válido, porque el ordenamiento sería diferente. ¿Se comprende?, en cambio generar una PK con carrera_id, corredor_id, posicion_salida, posicion_final no permitiría jamás una duplicación.

Un afinamiento del modelo podría incluir, por ejemplo, eventos ocurridos durante la carrera, lo que implicaría poner dos tablas adicionales:

• Corredor(Corredor_id, nombre)
• Pista(pista_id, nombre, ubicacion, datos...)
• Carrera(carrera_id, nombre, fecha)
• Corredor_carrera(carrera_id, corredor_id, posicion_salida, posicion_final)
• Corredor_carrera_evento(carrera_id, corredor_id, evento_id, evento_hora, otrosdatos...)
• Eventos(evento_id, evento_descripcion)

Este modelo podría codificar las ocurrencias y situaciones de la carrera, y solamente deberías almacenar lo que sucede según tiempo. Permitiría saber qué le ocurrió a un piloto durante cada carrera.
En definitiva, lo que tienes que profundizar es el análisis del sistema y crear un buen DER, antes de ponerte a implementar las tablas. Estos errores aparecen en el nivel de DER lógico con mucha facilidad.

[alvaro_pasto]

Muchas gracias por la información tan útil, todavía no la asimilé toda pero me estoy haciendo una idea.

Algo a tener en cuenta es lo siguiente. Solo me interesa guarar los datos de los 8 primeros, ademas de pole y vuelta rápida, es lo único relevante para mi. Dicho de otra manera, lo que haya pasado con los demás y en que posición quedaron, no me interesa para nada. No se si esto afecta en algo al diseño.

Voy a echar un vistazo y dedicarle algunas horas mas a intentar asimilar tu información, y cuando haya modificado el modelo me vuelvo a pasar por aquí.

Muchas gracias por la ayuda

[gnzsoloyo]

En realidad no afecta el modelo por una cuestión de principios de diseño:
La estructura de datos debe ser eficiente en las consultas y simple de analizar; la que propones es ineficiente en la consulta, por cuanto para obtener las posiciones de una carrera determinada debes analizar los ocho campos, mientras que en el otro caso solamente hay que analizar uno. Eso hará a la larga que las consultas sean más fáciles de escribir y con menos depuración, ya que el modelo está más normalizado.
Hay que diseñar siempre para el peor escenario posible. Casi siempre uno se queda corto.
Se debe diseñar de modo que el modelo sea flexible y funcional para el uso actual, pero también para las aplicaciones futuras, porque todos los sistemas evolucionan. Es más sencillo evolucionar con un modelo normalizado que con un modelo sin normalizar.

Sintetizando: Cuanto mejor diseñes ahora, menos problemas tendrás en el futuro para mejorar el modelo o agregarle capacidades.

[alvaro_pasto]

Muchisimas gracias por tu ayuda gnzsoloyo, me esta sirviendo de mucho. En unos días me vuelvo a pasar por aquí para contaros que tal evoluciona el diseño.

[alvaro_pasto]

Bueno, después de intentar aplicar los consejos de gnzsoloyo aquí dejo el modelo referencial, teoricamente pulido. He añadido la función de apuestas, donde cada usuario puede apostar por la posición de cada piloto en cada carrera, y a partir de ahí se calculará una puntuación en la aplicación que utilizará la BDD.

Este es el enlace al diseño: http://www.alvarovazquez.es/publico/Modelo.pdf

¿Mejor la cosa?

[alvaro_pasto]

He añadido un par de tablas para tener constancia de diferentes eventos que ocurran durante la carrera.

El modelo sigue aquí: http://www.alvarovazquez.es/publico/Modelo.pdf

Algúna recomendación?