On Sun, 23 Sep 2001 03:56:50 GMT, Pat M <[EMAIL PROTECTED]> 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? > > ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly