Date: Mon, 05 Dec 2005 09:24:51 +1100
From: Klint Gore <kg@kgb.une.edu.au>
To: Rodrigo Madera <rodrigo.madera@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
Message-Id: <43936CB3FB.5E5FKG@129.180.47.120>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Sat, 3 Dec 2005 23:00:21 +0000, Rodrigo Madera <rodrigo.madera@gmail.com> wrote:
> Imagine a table named Person with "first_name" and "age".
> 
> Now let's make it fancy and put a "mother" and "father" field that is
> a reference to the own table (Person). And to get even fuzzier, let's
> drop in some siblings:
> 
> CREATE TABLE person(
>    id bigint PRIMARY KEY,
>    first_name TEXT,
>    age INT,
>    mother bigint REFERENCES person,
>    father biging REFERENCES person,
>    siblings array of bigints  (don't remember the syntax, but you get the point)
> );
> 
> Well, this is ok, but imagine a search for  "brothers of person id
> 34". We would have to search inside the record's 'siblings' array. Is
> this a bad design? is this going to be slow?

Do you need the array at all?

alter table person add column gender;

select id 
>from person
where gender = 'male' 
and (mother = (select mother from person where id = 34)
     OR father = (select father from person where id = 34))

You can change the OR depending if you want half brothers or not

> What would be a better design to have these kind of relationships?
> (where you need several references to rows inside the table we are).

We use that structure (without the sibiling array) for our systems. 
Siblings are calculated from parents (in our case, livestock, there can
be hundreds).  You have to be prepared to use recursive functions and
make sure that a person doesnt appear anywhere higher in their family
tree.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+