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
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 |