Hi,
I (think I) want to denormalize an aggregate for performance reasons.
I think that is the right terminology, please correct me if not.
Consider these two tables:
items:id serial (pk),group_id int (fk),...
groups:id serial (pk),item_count int -- this is derived from (select count(*) from items
where group_id = id)...
item_count would be updated by insert/update/delete triggers on the
items table, hopefully that would ensure it is always correct?
I'm wondering is what I'm trying to do here pretty standard and are
there any gotchas I should be aware of?
Lastly, (assuming this is acceptable practice), are there any
shorthand ways of setting these kind of derived columns up? It seems
like there's a lot of trigger/function writing that could be
automatically generated for a lot of cases.
Thanks!
Jamie