From: Andreas Pflug <pgadmin@pse-consulting.de>
To: "Hélder M. Vieira" <hmv@mail.telepac.pt>
Cc: pgsql-performance@postgresql.org
Cc: rodrigo.madera@gmail.com
Subject: Re: Faster db architecture for a twisted table.
Date: Sun, 04 Dec 2005 11:56:53 +0000
Message-ID: <4392D985.7060902@pse-consulting.de> (raw)
In-Reply-To: <005501c5f872$b1ce1380$580bfea9@hmv02>
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
<439230D3.9070006@pse-consulting.de>
<005501c5f872$b1ce1380$580bfea9@hmv02>
Hélder M. Vieira wrote:
>
> ----- Original Message ----- From: "Andreas Pflug"
> <pgadmin@pse-consulting.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgadmin@pse-consulting.de, hmv@mail.telepac.pt, rodrigo.madera@gmail.com
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <4392D985.7060902@pse-consulting.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox