From: | Andreas Pflug <pgadmin(at)pse-consulting(dot)de> |
---|---|
To: | "Hélder M(dot) Vieira" <hmv(at)mail(dot)telepac(dot)pt> |
Cc: | pgsql-performance(at)postgresql(dot)org, rodrigo(dot)madera(at)gmail(dot)com |
Subject: | Re: Faster db architecture for a twisted table. |
Date: | 2005-12-04 11:56:53 |
Message-ID: | 4392D985.7060902@pse-consulting.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hélder M. Vieira wrote:
>
> ----- Original Message ----- From: "Andreas Pflug"
> <pgadmin(at)pse-consulting(dot)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)
>
This is an extended version, that could describe general person
relations, not only family relations. Still, your your
relationship_types are not precise. Since a two way relation is
described, only the two Child_of and Brother/Sister are needed; the
gender should be taken from the person themselves (to avoid data
inconsistencies as "Mary is a brother of Lucy").
But this isn't pgsql-performances stuff any more.
Regards,
Andreas
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Wollny | 2005-12-04 13:24:37 | Re: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 |
Previous Message | Michael Riess | 2005-12-04 09:33:47 | Re: 15,000 tables - next step |