Location Structures Approaches, Pros & Cons

From: Tim Marshall <TIMMY!_at_PurplePandaChasers.Moertherium>
Date: Mon, 20 Feb 2006 01:10:01 -0330
Message-ID: <dtbh72$nns$1_at_coranto.ucs.mun.ca>



Hi, I haven't googled this subject yet, but I'm hoping for some kicks or gentle prodding in the right direction as it's an area with which I am unfamiliar. Thanks very much in advance for reading.

I'm doing some thinking on a system that involves tracking items in numerous locations around the world (my organization has a lot of academics traveling about for research). These locations will likely be input at first by the admins of the system, but users will likely need to have either some mechanism to enter locations not covered or to at least submit requests that locations be added.

The two approaches for listing such locations I'm contemplating are what I'm calling (and perhaps there are better terms for them) the "multiple table hierarchical approach" and the "single table parent/child approach".

Multiple table hierarchical approach is what I'm used in facilities management related applications. Instead of facility->building->room, though, I'd be looking at tables that are set up Country->province/state->county->city/location. The advantage of this is I can use proper PK/fk constraints. However, I think there's a disadvantage in that I am imposing a specific "geo-political organizational structure" of sorts on all parts of the world... what if a user wishes to provide location detail at a lower level than city level, such as a borough or something?

The other approach is one with which I am familiar for listing parent child relationships between various components of building equipment (a fan might be a child of an air handling unit, for example) rather than location. It's more difficult (impossible?) to impose a table level constraints on relationships within such a table, and depending on the platform, it can be difficult to really organize the data into major locations (country, say) and the component children. Oracle allows me to do this using the Connect by prior statements, for example. The real advantage of such a system as this is that I'm not locked into specific "geo-political" organizational structure.

Thanks again, in advance, for any advice, gentle or rough...

-- 
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me
Received on Mon Feb 20 2006 - 05:40:01 CET

Original text of this message