¿Enlazar una tabla con si misma?

[javierflti]

Hola, tengo un pequeño problema en el diseño de una base de datos y quiero saber que opinais los que tenéis más dominio del tema.

Estoy creando una aplicación de gestión de perros y necesito almacenar en una base de datos información de cada perra (solo las hembras) con su madre y con su padre.

En principio tenía pensado hacer lo siguiente

Tabla perras
id
nombre
xxxx
xxxx
id_madre (enlazado a la misma tabla)
id_padre (enlazado a la tabla perros)


Tabla perros
id
nombre
xxxx
xxxx
xxxx

El problema es que me gustaría dejar el id de la madre como opcional (porque puede que no se conozca quien es la madre) y no se como hacer eso, porque a la hora de sacar los datos, no puedo hacer una consulta de manera correcta.

Mi pregunta es la siguiente
¿Está bien el diseño y se podría sacar los datos de una perra y los de su madre con una sola consulta?
¿Tendría que hacer una tabla intermedia para enlazar los datos?

Muchas gracias

[leonardo_josue]

Hola javierflti:

Lo que tienes se llama es una referencia circular y si hay forma de obtener lo que quieres, sin embargo hay algunas consideraciones a tomar en cuenta.

En primer lugar, si hablas de atributos OPCIONALES, estos no deben formar parte de una tabla, sobre todo si deseas tener integridad referencial. Una llave (primaria o Foránea) NO PUEDE SER NULA, por lo tanto no podrías colocarla en una tabla...

Hay varias formas de modelar lo que quieres, sin embargo debes de considerar que es posible que en algunos casos no tengas información ni del padre ni de la madre... creo que el modelo que te pudiera funcionar sería un modelo de tres tabla: una donde almacenes el nombre de todos los perros, una donde relaciones aquellos perros de los que conoces el padre y otra aquellos de los que conoces la madre...

Sería más o menos así:

Código MySQL:

mysql> SELECT * FROM cat_perros;
+----------+--------------+--------+
| id_perro | nombre       | genero |
+----------+--------------+--------+
|        1 | perro uno    | M      |
|        2 | perra dos    | H      |
|        3 | perra tres   | H      |
|        4 | perra cuatro | H      |
|        5 | perro cinco  | M      |
+----------+--------------+--------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM padres;
+----------+----------+
| id_perro | id_padre |
+----------+----------+
|        1 |        1 |
|        2 |        5 |
|        5 |        5 |
+----------+----------+
3 rows in set (0.00 sec)
 
mysql> SELECT * FROM madres;
+----------+----------+
| id_perro | id_madre |
+----------+----------+
|        2 |        4 |
+----------+----------+
1 row in set (0.00 sec)
 

Observa que en la tabla PADRES y MADRES, ambos campos apuntan hacia la misma tabla (CAT_PERROS) por lo tanto, si quisieras obtener AMBOS NOMBRES, lo que tienes que hacer es poner dos veces la tabla pero con distintos ALIAS:

Código MySQL:

mysql> SELECT P.id_perro id_hijo, CP1.nombre nombre_hijo, 
    -> P.id_padre, CP2.nombre nombre_padre
    -> FROM padres P
    -> INNER JOIN cat_perros CP1 ON CP1.id_perro = P.id_perro
    -> INNER JOIN cat_perros CP2 ON CP2.id_perro = P.id_padre;
+---------+-------------+----------+--------------+
| id_hijo | nombre_hijo | id_padre | nombre_padre |
+---------+-------------+----------+--------------+
|       1 | perro uno   |        1 | perro uno    |
|       2 | perra dos   |        5 | perro cinco  |
|       5 | perro cinco |        5 | perro cinco  |
+---------+-------------+----------+--------------+
3 rows in set (0.00 sec)
 

Observa que utilizo dos veces la tabla cat_perros, pero con dos alias distintos (CP1, CP2), de tal manera que puedo relacionar de manera independiente cada tabla...

de esta manera, no importa si no tienes información de alguno de los padres, podrías obtener la información de cada perro más o menos así:

Código MySQL:

mysql> SELECT CP1.id_perro, CP1.nombre,
    -> CP2.nombre padre,
    -> CP3.nombre madre FROM cat_perros CP1
    -> LEFT JOIN padres P ON CP1.id_perro = P.id_perro
    -> LEFT JOIN cat_perros CP2 ON P.id_padre = CP2.id_perro
    -> LEFT JOIN madres M ON CP1.id_perro = M.id_perro
    -> LEFT JOIN cat_perros CP3 ON M.id_madre = CP3.id_perro
    -> ORDER BY CP1.id_perro;
+----------+--------------+-------------+--------------+
| id_perro | nombre       | padre       | madre        |
+----------+--------------+-------------+--------------+
|        1 | perro uno    | perro uno   | NULL         |
|        2 | perra dos    | perro cinco | perra cuatro |
|        3 | perra tres   | NULL        | NULL         |
|        4 | perra cuatro | NULL        | NULL         |
|        5 | perro cinco  | perro cinco | NULL         |
+----------+--------------+-------------+--------------+
5 rows in set (0.00 sec)
 

Se entiende el punto???

Saludos y espero que te sea de ayuda
Leo.

[javierflti]

Gracias, a ver si lo puedo solucionar así
muchas gracias