X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 596159DD56C
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Dec 2005 07:56:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 18629-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun,  4 Dec 2005 07:56:53 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.183])
	by postgresql.org (Postfix) with ESMTP id A70C39DCAD0
	for <pgsql-performance@postgresql.org>;
	Sun,  4 Dec 2005 07:56:54 -0400 (AST)
Received: from [84.143.20.193] (helo=pse.dyndns.org)
	by mrelayeu.kundenserver.de (node=mrelayeu3) with ESMTP (Nemesis),
	id 0MKxQS-1EisUC0Upp-00039O; Sun, 04 Dec 2005 12:56:56 +0100
Received: from pse1 ([192.168.0.3]) by pse.dyndns.org with esmtp (Exim 4.44)
	id 1EisUA-0004We-4L; Sun, 04 Dec 2005 12:56:54 +0100
Message-ID: <4392D985.7060902@pse-consulting.de>
Date: Sun, 04 Dec 2005 11:56:53 +0000
From: Andreas Pflug <pgadmin@pse-consulting.de>
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: =?ISO-8859-1?Q?=22H=E9lder_M=2E_Vieira=22?= <hmv@mail.telepac.pt>
CC: pgsql-performance@postgresql.org,  rodrigo.madera@gmail.com
Subject: Re: Faster db architecture for a twisted table.
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
	<439230D3.9070006@pse-consulting.de>
	<005501c5f872$b1ce1380$580bfea9@hmv02>
In-Reply-To: <005501c5f872$b1ce1380$580bfea9@hmv02>
X-Enigmail-Version: 0.89.5.0
X-Enigmail-Supports: pgp-inline, pgp-mime
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Provags-ID: kundenserver.de abuse@kundenserver.de
	login:0ce7ee5c3478b8d72edd8e05ccd40b70
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/114
X-Sequence-Number: 15935

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