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 1E6AA9DCAAD for ; Sat, 3 Dec 2005 21:33:18 -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 87191-09 for ; Sat, 3 Dec 2005 21:33:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sapo.pt (relay6.ptmail.sapo.pt [212.55.154.26]) by postgresql.org (Postfix) with SMTP id 496C79DCA94 for ; Sat, 3 Dec 2005 21:33:13 -0400 (AST) Received: (qmail 19915 invoked by uid 0); 4 Dec 2005 01:33:13 -0000 Received: from unknown (HELO sapo.pt) (10.134.35.155) by relay6 with SMTP; 4 Dec 2005 01:33:13 -0000 Received: (qmail 9907 invoked from network); 4 Dec 2005 01:33:13 -0000 X-AntiVirus: PTMail-AV 0.3.87.1 X-Virus-Status: Clean (0.00427 seconds) Received: from unknown (HELO hmv02) (hmv@[82.155.147.30]) (envelope-sender ) by mta5 (qmail-ldap-1.03) with SMTP for ; 4 Dec 2005 01:33:13 -0000 Message-ID: <005501c5f872$b1ce1380$580bfea9@hmv02> From: =?iso-8859-1?Q?H=E9lder_M._Vieira?= To: References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com> <439230D3.9070006@pse-consulting.de> Subject: Re: Faster db architecture for a twisted table. Date: Sun, 4 Dec 2005 01:33:13 -0000 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2670 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.919 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.919 X-Spam-Level: * X-Archive-Number: 200512/110 X-Sequence-Number: 15931 ----- Original Message ----- From: "Andreas Pflug" > 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) ... It requires a careful maintenance, as almost all (I'd stick with ALL) relationships will require a person to appear twice (as source and as target), but flexible and easy to query. Helder M. Vieira