triggers vs b-tree - Mailing list pgsql-general

From gustavo halperin
Subject triggers vs b-tree
Date
Msg-id 45B91433.7040309@gmail.com
Whole thread Raw
Responses Re: triggers vs b-tree
List 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;



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Duplicate key violation
Next
From: "carter ck"
Date:
Subject: Linking a Postgres table on Linux to Ms Access