DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=JL6g6cLT6xsN16T3XN/qeQaDAIaJEaCrN/J0x4BCXkccNIjanaGIpz7SX2/uLREKnEIXBtF2L3lWOcoz8TgVLt+hAkpgJgO70tCEBSGyjyaQv8yIx15pRjdMtUfD4vNIcgLaa10w3apWxM1pEAW/SemUF6V0KT2DqOShMXcrH68=
Message-ID: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
Date: Sat, 3 Dec 2005 23:00:21 +0000
From: Rodrigo Madera <rodrigo.madera@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Faster db architecture for a twisted table.
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 p=
oint)
);

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).

Thanks for any help,
Rodrigo