Re: Can Oracle save a tree?

"Ramon F Herrera" <ramon_at_conexus.net> wrote in message news:c9bc36ff.0404271813.1f66aa34_at_posting.google.com...
> The company I work for makes reports for other companies, based
> on geography, states, districts, etc. and the companies can vary
> from a one location shop, to a multinational with thousands of
> branch offices.
>
> Needless to say, the most natural way to store this is in
> a tree data structure. I have already done that at the Java-GUI
> front end, but I was wondering how to do it in the database.

That may be the most natural way to *display* the data - I'm unconvinced that it is the most natural way to *store* the data. It sounds like you actually have at least two if not more entities which you are interested in stores and their location. The *natural* way for me to store this data would be in 2 tables a STORE and a LOCATION table. eg

STORE

STORE_ID NUMBER pk
STORE_NAME CHARACTER NOT NULL
STORE_LOCATION NUMBER fk to location
STORE_MANAGER etc etc

and
LOCATION

LOCATION_ID NUMBER pk
LOCATION_DESCRIPTION CHARACTER
LOCATION_STATE
LOCATION_ZIP

Obviously you might actually have tables for STATE and DISTRICT if necessary as well.

It sounds like you have started coding based on the interface, and not on a data model, I apologize if this is an incorrect assumption and/or if I'm telling you something you already know, but nearly always the correct structure for storing the data will fall out (almost straightforwardly) from the data model drawn up at the design phase.

> I realize that anything can be flattened to a relational table,
> but I understand that now Oracle has other non-traditional
> ways of storing data.
>
> So, is it possible to save a tree in Oracle?

see above, but the answer is yes. You can use the "connect by" sql syntax for heirarchical queries, you could probably use object-relational types or nested tables for this. These are all in the docs at http://docs.oracle.com

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/