> 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.
>
The trigger/array seems to me as a premature optimization - if you are
not sure the index is 'too slow' (and there's no other way to speed it
up) don't do it.
You should always have a foreign key in Person(family_id) referencing
the Family(family_id) as you need to reinforce data integrity between
these two tables, and the 'rule of a thumb' is to have indexes on
foreign keys in large tables. The reason is pretty simple - the
PostgreSQL does a simple query when checking the foreign key.
So if the Person table is 'small' (less than for example 1000 rows) and
it will not grow too much, there's no need to use an index (as it won't
be used for small tables) and the queries to get all the family members
will be very fast too.
On the other side, if the Person table is 'large' (say more than 10.000
rows), then there should be an index on Person(family_id). Then it
depends on your requirements - the most important things to consider are
these:
1) Will the application be mostly used to select or update?
The trigger adds (small) overhead to modifications, but if you do
mostly selects this may not be a problem.
2) Do you need only IDs of the family members, or do you need all the
data from Person table?
The trigger/array solution gives you only IDs and you'll have to
fetch the data in a separate query (most probably). The array simply
complicates the queries.
Anyway, I would try to stick with the foreign key / index solution as
long as possible. If you are not happy with the speed do some benchmarks
with the trigger / array solution and compare them to the foreign key /
index. Try to do some other optimizations too (for example cluster the
Person table along the family_id column - that usually means a serious
performance boost).
Tomas