triggers vs b-tree

From: gustavo halperin <ggh(dot)develop(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: triggers vs b-tree
Date: 2007-01-25 20:33:55
Message-ID: 45B91433.7040309@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello I have a design question:

I have a table representing Families, and a table representing Persons.
The table Family have a row family_id as primary key.
The table Person have a row person_id as primary key and contain also a
row family_id.
As you can understand, the row family_id in a table ficha_person is not
unique, I mean is the same for all the family person's.

So my question is: If most of the time I need to find all the persons
for one asked family what is the best way to do that?
I think about two methods:
1- Making a b-tree index in ficha_person with the rows
family_id and person_id.
2 - Adding an array in the table ficha_family containing the
persons of this family. And creating a Trigger that update this array
for each person insert/delete in the table ficha_family.

So ..., what do you think? There are a better solution or what of the
above solutions is better ??

Thank you in advance,
Gustavo.

Tables:

CREATE SEQUENCE ficha_person_id_seq;
CREATE TABLE ficha_person (
person_id integer DEFAULT nextval('ficha_person_id_seq')
CONSTRAINT the_pers
on_id PRIMARY KEY,
family_id integer ,--CONSTRAINT the_family_id
.....
) WITH OIDS;

CREATE SEQUENCE ficha_family_id_seq;
CREATE TABLE ficha_family (
family_id integer DEFAULT nextval('ficha_family_id_seq')
CONSTRAINT the_fami
ly_id PRIMARY KEY,
person_id integer[], --- Optionally, instead of using b-tree index.
.....
) WITH OIDS;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Boone 2007-01-25 20:37:11 Postgresql best practices
Previous Message Merlin Moncure 2007-01-25 20:28:03 Re: loop plpgsql recordset variable