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