Search Postgresql Archives

triggers vs b-tree

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux