Обсуждение: 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@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,
> 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?
>
>

> * 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
> with postgres.
>

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

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

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
> > with postgres.
> >

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
> > with postgres.
> >

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.

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.