A way to increase read performance on tree like structure is to create a
table where the values in a single column are
statename<delim>cityname<delim>suburbname<delim>streetname<delim>address
This table is derived from the actual table through the use of triggers
however indexes should be able to be used when you are matching from left to
right to a point. Updates to the upper part of the tree do take a long time
to complete.
A table with the same structure can be also be used.
-----Original Message-----
From: Pat M [mailto:pmeloy@removethispart.home.com]
Sent: Monday, 24 September 2001 10:07 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] My brain hurts - update field based on value of
another table's field
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.
> >
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
