Woohoo! I got it. VIEWS! I can pre-do a pile of the work in a view and cut
down on my scripting complexity a LOT. Instead of duplicating data all the
time, create a view that includes all the parent record fields that I'd
usually have to join manually in a script.
Areas
------------
area_id pkey
area_name
Sites
--------
site_id pkey
site_name
site_area references area_id
Buildings
------------
building_id pkey
building_name
building_site references site_id
create view building_view as select buildings.*,(select site_name from sites
where site_id=building_site) as building_site_name,(select area_id from
areas,sites where area_id=site_area and site_id=building_area) as
building_area_id,(select area_name from area,sites where area_id=site_area
and site_id=building_site) as building_area_name from buildings
Which gives me
buildings_view
---------------
building_id
building_name
building_site
building_site_name
building_area_id
building_area_name
If I change the area a site is associated with( say site_area is changed
from 32 to 122, the value is reflected in building_area_id without any
intervention on my part. Have I got this right? btw - the real schema has a
LOT more than just this, which is why my big effort to reduce the complexity
(wrapping my brain around) of queries in the web page scripts.