Re: Storing hierarchical information?

From: Mikito Harakiri <nospam_at_newsranger.com>
Date: Mon, 07 May 2001 18:46:37 GMT
Message-ID: <h4CJ6.4896$vg1.381323_at_www.newsranger.com>

In article <slrn9fdnor.iqd.criscokid_at_deepcore.gsm>, Dave Kimmel says...
>
>So, what I want to ask is this: Is there any "proper" way to store
>hierarchical data in a database, much like operating systems store
>files? Is what I'm doing fine, or is there a way that could be
>faster?

Dave,

Question how to store tree in the database surface now and then, and many would pointer to J.Celko's method. IMHO, more superior method is to materialize a path in some way. Below is extract from some old thread.

If you just store path as a string

('1.0')

/     |    \
/       |      \
/         |        \
/           |          \
('1.1')        ('1.2')       ('1.3')
/      \          /   \           |
/       |        /      \          |

('1.1.1') ('1.1.2') ('1.2.1')('1.2.2')('1.3.1') / | \
/ | \
('1.2.2.1') ('1.2.2.2') ('1.2.2.3')

then you would be able to query like this:

select node from tree where SUBSTR('1.2.2.3', 1, LENGTH(node))=node

returns all the nodes above 1.2.2.3.

select node from tree where SUBSTR(node, 1, LENGTH('1.2.2'))='1.2.2'

returns everything below 1.2.2.

• Useful to avoid lenthly 'where' clauses CREATE Function NODELEVEL ( arg IN VARCHAR2 ) RETURN number IS ret number; BEGIN
• Implemented declaratively, just to prove that could stay relational.
• Will get better performance by simply counting dots in the node string procedurally. select count(node) into ret from tree where SUBSTR(arg, 1, LENGTH(node))=node; RETURN ret ; END; /

select node from tree where SUBSTR('1.2.2.3', 1, LENGTH(node))=node and NODELEVEL('1.2.2.3')=NODELEVEL(NODE)+1

returns node's parent. Received on Mon May 07 2001 - 20:46:37 CEST