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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
