Message-ID: <439230D3.9070006@pse-consulting.de>
Date: Sun, 04 Dec 2005 00:57:07 +0100
From: Andreas Pflug <pgadmin@pse-consulting.de>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Rodrigo Madera <rodrigo.madera@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Faster db architecture for a twisted table.
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
In-Reply-To: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rodrigo Madera 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?
>
>What would be a better design to have these kind of relationships?
>(where you need several references to rows inside the table we are).
>  
>

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.

Regards,
Andreas