Hélder M. Vieira wrote:
----- Original Message ----- From: "Andreas Pflug"
<pgadmin@xxxxxxxxxxxxxxxxx>
Create a table "sibling" with parent_id, sibling_id and appropriate
FKs, allowing the model to reflect the relation. At the same time, you
can drop "mother" and "father", because this relation is covered too
Something like a table describing relationships and a table reflecting
relationships from both sides, I guess:
create table relationship_type
(
relationship_type_id serial,
relationship_type_description varchar(20)
)
populated with values such as:
1 Child_of
2 Father_of
3 Brother_of
4 Sister_of
...
And then
create table person_relationships
(
source_person_id int4,
relationship_type_id int4,
target_person_id int4
)
populated with values such as:
1 1 2 (person 1 is child of person 2)
2 2 1 (person 2 is father of person 1)
This is an extended version, that could describe general person
relations, not only family relations. Still, your your
relationship_types are not precise. Since a two way relation is
described, only the two Child_of and Brother/Sister are needed; the
gender should be taken from the person themselves (to avoid data
inconsistencies as "Mary is a brother of Lucy").
But this isn't pgsql-performances stuff any more.
Regards,
Andreas
