Обсуждение: My brain hurts - update field based on value of another table's field

I'm just a hobbyist so this is probably atroceous, but I'm trying to do
something like the following. Sorry its not in real SQL format. I know how
to auto-update by referencing to a primary key of another table, but what if
you want a field to change along with a non-unique field from a different
table? Here's an example of what I mean

table areas
-------------
area_id serial primary key
area_name text

table sites
----------
site_id serial primary key
site_name text
site_area int references areas on delete cascade

table buildings
------------
building_id serial primary key
building_name text
building_area int <--- needs to change when site_area changes
building_site int references sites on delete cascade

table zones
------------
zone_id serial primary key
zone_name text
zone_area int  <--- needs to change when building_area changes
zone_site int  <--- needs to change when building_site changes
zone_building int references buildings on delete cascade

None of the primary keys will be changing of course. But the area a building
is in may change (area being an arbitrary designation, not municiple
boundaries), as may other fields as I work my way through building the data.

I know I can join things together in queries, avoiding all this, but it gets
real confusing trying to join 12 tables, and slow... I want to be able to
get the area from the buildings table and not have to join three tables just
to find out what area it belongs to. Unless someone knows an easier way than
select area_name from areas,sites,buildings where area_id=site_area and
site_id=building_id and building_id=1; Speed and easy queries are my focus,
not disk space or ram savings.

I looked at foreign keys, but they get uptight when the referenced field
isn't unique. On update cascade would have been wonderful 8(

I looked at inheritance, but I don't think its what I had in mind.

I'm thinking I have to delve into the horrors that are triggers and
functions...

Any cool ideas floating around out there?

>>>>> "Pat" == Pat M <pmeloy@removethispart.home.com> writes:

Pat> I know I can join things together in queries, avoiding all this,
Pat> but it gets real confusing trying to join 12 tables, and
Pat> slow... I want to be able to get the area from the buildings
Pat> table and not have to join three tables just to find out what
Pat> area it belongs to. Unless someone knows an easier way than
Pat> select area_name from areas,sites,buildings where
Pat> area_id=site_area and site_id=building_id and building_id=1;
Pat> Speed and easy queries are my focus, not disk space or ram
Pat> savings.

Just build a view that wraps that part of the join, and map updates on
that view back to the consituent tables (or just forbid writes to
those tables).  Then you can construct queries as if those one or two
extra columns were part of that table, and they'll never get out of sync.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!