Derived columns / denormalization

From: "Jamie Tufnell" <diesql(at)googlemail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Derived columns / denormalization
Date: 2009-01-16 03:52:25
Message-ID: b0a4f3350901151952n569a45b5mfd4e9b56ca558c78@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2009-01-16 04:06:35 Re: Derived columns / denormalization
Previous Message Steve Midgley 2009-01-14 18:51:49 Re: some howto/theory book/tutorial on practical problem solving in SQL