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;