From: Hélder M. Vieira <hmv@mail.telepac.pt>
To: pgsql-performance@postgresql.org
Subject: Re: Faster db architecture for a twisted table.
Date: Sun, 4 Dec 2005 01:33:13 -0000
Message-ID: <005501c5f872$b1ce1380$580bfea9@hmv02> (raw)
References: <3cf983d0512031500r2a9da709r8eb362806e91f61@mail.gmail.com>
<439230D3.9070006@pse-consulting.de>
----- 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)
...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hmv@mail.telepac.pt
Subject: Re: Faster db architecture for a twisted table.
In-Reply-To: <005501c5f872$b1ce1380$580bfea9@hmv02>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox