How best to implement a multi-table constraint?

From: "Karl Nack" <pglists(at)futurityinc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How best to implement a multi-table constraint?
Date: 2008-10-20 20:56:21
Message-ID: 1224536181.v2.mailanyonewebmail-389065@fuse50
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2008-10-20 21:51:41 Re: [HACKERS] Debian no longer dumps cores?
Previous Message Simon Riggs 2008-10-20 20:56:06 Re: [GENERAL] [HACKERS] Hot Standby utility and administrator functions