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

Tim Marshall wrote:

So .........

What requirement do you have that's not met by recording just the
mailing address?


Marshall

Marshall Spight wrote:

Analysis by country, etc, presentation for choices by users to indicate
what/where their research is taking place, IOW where in the world we
have research taking place, assets are located, etc. 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Have you looked at the options in TREES & HIERARCHIES IN SQL?

Tim Marshall <TIMMY!@PurplePandaChasers.Moertherium> wrote:

Perhaps you mean that simply reorganizing a tree, interactively, is
simpler than redesigning a fixed scheme of tables and links?

Connect By implies the use of one-way pointers/links, in an adjacency
list. This violates the very basic concept of the relation of vaguely
defined 'related things', which are unsorted/unordered.

Yet information is often sorted, and typically hierarchical. And
information is often sparse in some areas, and the hierarchy flexible
or not so well understood.

If one were to bet on the horses, a database could be used to store
such information, quantitative, even vague and cryptic observations of
use only to the diarist, if you will.

Perhaps one could turn to the RM. One might list the horses by stable.
One might list the horse, per race, and gate. The gate becomes merely
an attribute for that relation.

But how far does one carry that? Horses are money-winners. Some win
more, some less. And they can be ranked. Their ranking is yet another
attribute, in another relation. At what point can one say that the
relation is sorted, which is a violation of the RM, as I understand
it?

On Mon, 20 Feb 2006 17:37:04 -0800, Mark Johnson
<102334.12 (AT) compuserve (DOT) com> wrote:

What do you mean by "how far does one carry that"? All your examples
are typical of cursors, not relations. Every time you say "list",
think "cursor". Cursors are always sorted, but relations are not.

So the question is, what do you want to model? Do you want to model
horses, a schedule of races, or collect statistics on races and
horses? Design your tables according to the model, not the
presentation or view of the model.

--
Bob Hairgrove
NoSpamPlease (AT) Home (DOT) com

Bob Hairgrove <invalid (AT) bigfoot (DOT) com> wrote:

No that's a relation. In describing a race, the attributes might
include, gate. The gates are ordered.

But as I understand it, relations are not supposed to be sorted. So I
wondered that if a relation includes a horse's ranking, as a 'thing'
intrinsic, that one is trying to say that relations can be sorted?


How about the example, above?

Mark Johnson wrote:
In a database that is not set oriented, you could simply add horses to a
race, the gate number would be implicit through the insertion order.

In a set oriented model, any sort order must be explicitly defined
through additional attributes, in this case the gate number. The
database itself does not know that this attribute has to do anything
with ordering (1). To get the values in the order defined through that
attribute, you have to add an ORDER BY clause, which is a feature of the
cursor, not of an relation.

Daniel

(1) The optimizer of course could use an index on that attribute to
retrieve the tuples in a certain order.

"Mark Johnson" <102334.12 (AT) compuserve (DOT) com> wrote

The gates aren't really ordered. The locations of the gates are ordered,
and the numbers painted on the gates are ordered, but the gates themselves
are not ordered.