Re: Faster db architecture for a twisted table.

Hélder M. Vieira wrote:
>
> ----- Original Message ----- From: "Andreas Pflug"
> <pgadmin(at)pse-consulting(dot)de>
>
>> 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