Re: My brain hurts - update field based on value of another table's field

On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <pmeloy(at)removethispart(dot)home(dot)com> wrote:
> 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
>

I think you would do this by _not_ having building_area at all.
You have a building_site, which has a site_area. Is it possible
to have a building_area that is _different_ from the site_area
of the building_site? That does not make sense to me.

> 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
>

It is really difficult to help you not knowing what these "site" and
"area" and "zone" things are.

> 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?
>
>