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: | Whole Thread | Raw Message | 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;
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 |