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?