Re: How best to implement a multi-table constraint?

Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:
> Hello all,
>
> I'm a bit of a newb designing a database to hold landcover information for
> properties in a city. Here's some simple sample data:
>
> property:
> property_name*, property_area
> -----------------------------
> sample house, 2500
>
>
> property_landcover:
> property_name*, landcover_name*, landcover_area
> -----------------------------------------------
> sample house, building, 1000
> sample house, grass, 1000
> sample house, concrete, 500
>
>
> Now, I need to check that the sum of landcover_area for a property matches
> the property_area.
>
> It seems like I have three obvious options:
>
> 1. A constraint trigger that sums up landcover area and compares it to the
> property area.
>
> Downside: The trigger will run for every row that's updated in these two
> tables, although it only needs to run once for each property.
>
>
> 2. A statement-level trigger that does the same thing as #1.
>
> Downside: Since I don't have access to the updated rows, I'll have to
> check the entire property table against the entire property_landcover
> table. It seems like this could get expensive if either of these tables
> gets very large.
>
>
> 3. Use a 3rd table to hold the total landcover area for each property. Use
> row-level triggers to keep this 3rd table updated. Use a statement-level
> trigger (or table constraint) to ensure the total landcover area matches
> the property area.
>
> Downside: Although I avoid redundant checks, my understanding is that
> UPDATE is a fairly expensive operation, so it might not actually perform
> any better.
>
>
> Although my tables are small right now, they may potentially have to hold
> an entire city's worth of properties, so I'm interested in finding a
> solution that scales.
>
> Can anyone offer some feedback or suggestions on which of these options to
> use? Or perhaps even another solution that hasn't occurred to me?
>
> Thanks!
>
> -Karl
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>