> 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
