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@removethispart.home.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.

Ok, here's what they are

* area - arbitrary name for a collection of sites (optional)

* site name for a collection of buildings (may only be one building)
optional

* building - a single building not optional name for a collection of rooms

* zone - sub area of a building not optional name for a collection of rooms.

* rooms (not listed)

The main record here is Building. Areas and sites are optional ways of
grouping buildings. Zones and rooms are required.

Since the area and site are optional and arbitrary (you may change your
organizational chart) I need a way of updating the children of that record
to reflect the changes. I can do it easy enough in php, just don't know how
with postgres.

Here's an example of the php script. Probably won't indent properly...

$building_id is the current building
$sel_BuildingSite is the new site for this building

if ($but_UpdateBuilding) {
$txt_buildingname=trim($txt_buildingname);
if ($txt_buildingname) {
$check=pg_Exec($connection,"select building_id from buildings where
building_id='$building_id';");
if ($check) {
if(pg_NumRows($check)==1) {
$check=pg_Exec($connection,"
update zones
set zone_site='$sel_BuildingSite'
where zone_building='$building_id';");
$check=pg_Exec($connection,"
update rooms
set room_site='$sel_BuildingSite'
where room_building='$building_id';");
$check=pg_Exec($connection,"
update buildings
set building_name='$txt_buildingname',
building_site='$sel_BuildingSite'
where building_id='$building_id';");
} else {
$error="Building ID Not found";
}
} else {
$error="Error - check is null for this building_id!";
}
}
}

What I'd like to do is have the php script change the building_site field
value then have postgres update the zone_site and room_site fields by
itself. The more I read the docs (thus the brain pain) the more I'm
convinced I need a trigger and function. I just can't seem to figure out how
they work.

Yes, I will be moving to transactions and more error checking, just trying
to figure out how I'm going to do this before I get too involved 8)

"Lee Harr" <missive@frontiernet.net> wrote in message
news:9okp4i$2q58$1@news.tht.net...

On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <pmeloy@removethispart.home.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,

* area - arbitrary name for a collection of sites (optional)

* site name for a collection of buildings (may only be one building)
optional

* building - a single building not optional name for a collection of rooms

* zone - sub area of a building not optional name for a collection of rooms.

* rooms (not listed)

CREATE TABLE room (room_id int, room_name text);
CREATE TABLE zone (zone_id int, zone_name text);
CREATE TABLE zone_room (zone_id int REFERENCES zone,
room_id int REFERENCES room);
CREATE TABLE building (building_id int, building_name text);
CREATE TABLE building_zone (building_id int REFERENCES building,
zone_id int REFERENCES zone);
CREATE TABLE site (site_id int, site_name text);
CREATE TABLE site_building (site_id int REFERENCES site,
building_id int REFERENCES building);
CREATE TABLE area (area_id int, area_name text);
CREATE TABLE area_site (area_id int REFERENCES area,
site_id int REFERENCES site);

I think I would do something like this. Try not to duplicate information
anywhere in your schema. This way, if you decide to move a site to a
different area or a room to a different building ;) you only need to
change it in one place.

Yeah, that part I know. The problem is that I'm going to have relations
tables up the wazoo. Been there before and I'd love to avoid it if at all
possible. I've managed (barely) to do joins on 11 tables at once and it was
freaking tough. I know duplication is supposed to be bad, but if I can get
the database to enforce references in the way I want, at least I won't
accidently get fields out of sync.

Mind you, my past experience with with 6.x... maybe now with selects for
"from" sources, it won't be so bad... hmmm

"Lee Harr" <missive@frontiernet.net> wrote in message
news:9olnc8$8sv$1@news.tht.net...

* area - arbitrary name for a collection of sites (optional)

* site name for a collection of buildings (may only be one building)
optional

* building - a single building not optional name for a collection of

rooms

* zone - sub area of a building not optional name for a collection of

rooms.

* rooms (not listed)

CREATE TABLE room (room_id int, room_name text);
CREATE TABLE zone (zone_id int, zone_name text);
CREATE TABLE zone_room (zone_id int REFERENCES zone,
room_id int REFERENCES room);
CREATE TABLE building (building_id int, building_name text);
CREATE TABLE building_zone (building_id int REFERENCES building,
zone_id int REFERENCES zone);
CREATE TABLE site (site_id int, site_name text);
CREATE TABLE site_building (site_id int REFERENCES site,
building_id int REFERENCES building);
CREATE TABLE area (area_id int, area_name text);
CREATE TABLE area_site (area_id int REFERENCES area,
site_id int REFERENCES site);

I think I would do something like this. Try not to duplicate information
anywhere in your schema. This way, if you decide to move a site to a
different area or a room to a different building ;) you only need to
change it in one place.

The main record here is Building. Areas and sites are optional ways of
grouping buildings. Zones and rooms are required.

Since the area and site are optional and arbitrary (you may change your
organizational chart) I need a way of updating the children of that

record

to reflect the changes. I can do it easy enough in php, just don't know

how

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.

On Mon, 24 Sep 2001 11:22:40 -0700, Pat M <pmeloy@removethispart.home.com>:

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.

Sounds good. I use views like this also (to reduce complexity).

I used to always want to "do it all in one query."

That gets out of hand pretty quickly, but if you can encapsulate
different sections of the query in views...

"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/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!