I agree, but it seems to me that property_area isn't a strictly derived value. It's possible to change the makeup of a property's landcover -- for example, remove some concrete and plant more grass, or add an extension to the building -- but the overall property area should remain constant. I feel like I should probably include some kind of constraint to enforce this. Am I needlessly over-complicating this? -Karl ----- Original Message ----- From: matthias@xxxxxxx Sent: Tue, October 21, 2008 7:31 Subject: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@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general ----- End of original message ----- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
