Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can Oracle save a tree?

Re: Can Oracle save a tree?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 28 Apr 2004 11:04:08 +0100
Message-ID: <408f8198$0$20514$ed9e5944@reading.news.pipex.net>


"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/
Received on Wed Apr 28 2004 - 05:04:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US