triggers vs b-tree
От | gustavo halperin |
---|---|
Тема | triggers vs b-tree |
Дата | |
Msg-id | 45B91433.7040309@gmail.com обсуждение исходный текст |
Ответы |
Re: triggers vs b-tree
|
Список | 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 по дате отправления: