misma llave FK en otra tabla, ¿algún consejo?

[Perr0]

Hola Buenas Tardes, nuevamente expongo esto pero un foro mas arriba, ya que no recibi respuesta:

Voy a colocar un ejemplo más básico pero me servirá para aclarar la consulta.

Tengo dos tablas:
personas
relacion

la tabla personas contiene 3 campos:
- id por persona
- nombre de la persona
- edad persona (no tiene importancia este campo)

la tabla relacion consta de 4 campos:
- idrelacion que es un id autoincremental
- persona1 (es la llave foránea donde me marca el id de algun registro personas)
- persona2 (es la llave foránea donde me marca el id de algun registro personas que no sea persona1)
- relacion no tiene mayor importancia pero es para definir por ejemplo: NINGUNA, PADRE, HIJO, ETC.

la tabla relacion la tengo con UPDATE ON CASCADE y DELETE ON CASCADE


mi consulta es:
¿esta bien hecha la estructura?

si estubiera bien entonce y como ejemplo
suponiendo que tengo los siguientes datos en tabla personas

idxxNombrexxxxEdad
1xxJuan xxxxxxx40
2xxMariaxxxxxxx35
3xxLuisxxxxxxxx31
4xxJosexxxxxxxx9


y estos datos en la tabla relacion

idrelacionxxpersona1xxpersona2xxrelacion
1xxxxxxxxxx1xxxxxxxxxx2xxxxxxxxxHERMANA
2xxxxxxxxxx1xxxxxxxxxx3xxxxxxxxxNINGUNA
3xxxxxxxxxx1xxxxxxxxxx4xxxxxxxxxHIJO
4xxxxxxxxxx2xxxxxxxxxx4xxxxxxxxxSOBRINO
5xxxxxxxxxx2xxxxxxxxxx3xxxxxxxxxNINGUNA

como deberia hacer un SELECT? (lo manejare con VIEWS luego) para poder consultar por una persona, y que me muestre todas las personas relacionadas con ella, por decir:

busco por Juan:

y que me arroje:

1) Maria (HERMANA)
2) Jose (HIJO)


hay alguna forma de mejorar esa estructura, agregando una tabla? o algo mas simple?

gracias por cualquier ayuda recibida...todo me sirve

salu2

[rqd]

la estructura no me parece mala, excepto que no acostumbro usar el mismo nombre para un campo en una tabla, (en tu caso relacion.relacion).

Para las consultas podrás usar alias para la tabla personas ejemplo:

SELECT relacion.idrelacion, relacion.idpersona1, p1.nombre, relacion.idpersona2, p2.nombre, relacion.relacion.
FROM relacion,personas p1, personas p2
WHERE relacion.idpersona1 = p1.idpersona
AND relacion.idpersona2 = p2.idpersona

[gnzsoloyo]

1) La tabla RELACION no necesita un "idrelacion". La relacion ya está dada por los dos ID de las personas vinculadas, a menos que pueda haber más de una relación entre personas, caso en el cual lo que correspondería no sería un ID relación sino un tipo de relación.
2) Si quieres conservar tu modelo, quitale la condición de PK a esos dos campos en la tabla Relación, porque con un ID numerico como parte de la clave pueden duplicarse las relaciones entre las mismas personas, ya que el ID cambiaría siempre, lo que mantendría la unicidad de la clave.
3) Además (siempre que quieras conservar tu modelo), las dos FK puestas deberían ser declaradas como clave de un índice UNIQUE, para evitar valores dos repetidos de relaciones.
4) Como te dice rqd, no se usan los mismo nombres para las tablas que para los campos. Los DBMS hacen una evaluación jerárquica de los nombres, y pueden confundir el a la tabla con el campo si no lo indicas completo, lo que dará resultados erráticos o incorrectos, y no será por errores de sintaxis.

[Perr0]

agradesco las aclaraciones.

en realidad la tabla relaciones tiene una sola PK (suelo agregar siempre una PK en toda tabla aunque no la utilice para preveer posibles cambios a futuro) y dos FK, la del tiporelacion (a.k.a. relacion.relacion) bien podria ser otra FK de una tabla en que tenga definido varios tipos de relaciones como: esposo, hermano, nieto, abuelo, etc.

la otra opcion era crear dos tablas personas:
- tpersona1
- tpersona2

pero se repetirian algunas como decir tengo 4 personas Juan (papá), María (mamá), José (hijo), Tomás (hermano de Juan)

en una tabla tendria que colocar:
tpersona1: Juan

en la otra
tpersona2: María, José, Tomás

podria establecer las siguientes relaciones:
Juan - María (esposa)
Juan - José (hijo)
Juan - Tomás (hermano)

pero para establecer la relación Tomás con María (Cuñada) y José (Sobrino) tendria que agregarlo a la tabla tpersona1 y empezaria con la duplicidad de datos...por ello opte por una sola tabla y otra para relacionarlos en vez de 3.

salu2 y espero siga por buen camino

[gnzsoloyo]

Una relación de parentescos como describes requiere sólo tres tablas, de las cuales únicamente dos tienen datos y la otra es relacional
En esencia tienes dos tablas: Personas y Relación. En la primera registras a las personas y sus datos. Esa tabla lo que tiene es una relación N:N consigo misma, y como toda relación N:N genera una tabla relacional, esta sería Persona_relación.
Según el modelo E-R esa tabla no requiere ID propio, ya que es una tabla dependiente, su ID está dado por las dos FK, definidas al mismo tiempo como PK de la tabla, desde el momento en que no puede aparecer un par relacionado en el mismo orden dos veces. Por dar un ejemplo, Juan no puede ser esposo y hermano de María, por lo cual la relación Juan+María sólo aparece una vez.
Por eso la PK de esa tabla debería ser sólo el ID de la primera persona del par más el ID de la segunda persona del mismo par.
El problema está dado en la identidad de la relación, es decir, en qué es lo que caracteriza a esa relación sobre las otras. Por eso necesita un atributo adicional que bien puede ser un relacion_id, que en realidad sería una FK adicional apuntando a la tabla Relación, la cual debería contener la descripción de las relaciones (hermano, hermana, esposo, esposa, cuñada, tio, abuela, amigo, etc.).

¿Se entiende la idea?

A mi entender estás tratando de inventar el agujero del mate. Este tipo de casos ya se resolvió hace mucho en los modelos de base de las redes sociales, que puedes consultar en Database Answers, por ejemplo:



En este modelo, el rol es la relación, la cual va como FK en Contacts; contact_id es FK de My (la tabla de usuarios), y FK_customer es e definitiva una My_Id que corresponde al contacto, que también es usuario.
En esencia es el mismo esquema, aunque más elaborado.

¿Alcanzas a verlo?

[Perr0]

como diria Ned Flanders perfectirijillo gracias por tomarte el tiempo y la explicación. Lo que mas agradesco es la página con varias modelos, no tenia idea de su existencia y es muy útil.

saludos y que estes bien.