Seeking Hierarchical Relational Data Model

  • Thread starter Thread starter clintonG
  • Start date Start date
C

clintonG

I'm looking for documentation and would not turn my nose up to
any code from anybody who thinks they are good at the design
of an algorythm that can be used to generated a hierarchical relational
data model.

What?

A Yahoo-like drill-down menu that is a series of categories and nested
categories is a hierarchical relational data model. An example can
be seen at [1] but the review of the query string values strongly indicates
a flawed model as the query string values are not logically ordered,
are not grouped, are not sequenced and in short are indicative of a
flawed model.

I'm formally trained in architecture. Where's a computer scientiest
when you need one? :-)

--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@ REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

[1] [1] http://demos.deloittes.net/directorydotnet/frmDefault.aspx
 
Joe Celko wrote a couple of excellent books on hierarchical database models.
How many levels deep is your hierarchical structure?

Thanks,
Michael C.
 
Hello!
Joe Celko wrote a couple of excellent books on hierarchical database models.
How many levels deep is your hierarchical structure?
Click to expand...

I have been following Joe Celkos for some time and have succesfully
implemented the graph model (although in a slighty modified version that
also allows for frequent insertions) all the way from the DB layer to C#
models (that allows for tree traversions including caching etc.).

The model has many advantages to the normal parent/child adjacency list -
even though the adjancency model is quite strong, when it comes to
lazyloading children etc. because you only need to fetch a set of children
for any given parent. With some caching in the middletier, you're looking at
service cached objects (or clones), instead of fresh data from the database.

But while most graph models allows for lazyloading children, some are
stronger in terms of fetching large presorted trees, and frequent
insertions. Usually, most trees have frequent selections and in-frequent
insertions. Unfortunately, with the basic graph (nested sets) model from Mr.
Celko, you end up locking the entire table when inserting new nodes.

Explaining everything here is not going to happen, but I'd suggest you get
his latest book on "Trees and Hierarchies in SQL" and take it from there.
It's quite interesting actually.
 
Well thank you Michael and Anders alike. Your references to Joe Celko
and his work has me eager to get going..

Answering Michael's question regarding how deeply the model needs to
nest is why I think I needed to be referred to a 'Joe Celko' as the model
I need must enable indeterminate levels.

I want to make it possible for those with deep subject level expertise
to generate and edit their own tree(s) as they and they alone are those that
know the taxonomy and the hierarchical relationships that are pertinent to
their body of knowledge.

Maybe a tough task for me compared to what can be done using an application
such as the "Verity Collaboration Classifier" [1] but that's what I'm interested
in
learning more about and appreciate your comments pointing the way...

--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@ REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

[1] http://www.verity.com/products/vcc/index.html
 
I ran into a similar problem with hierarchical datasets, way back before I
heard about Joe Celko. I beat my head against the wall for about a month
trying to figure it out until I bought his book. He's definitely got the
most in-depth coverage on the topic I've seen. Most authors that touch on
it will dedicate maybe 3 or 4 pages, and barely scratch the surface. He
dedicates whole books to it. I think he also posts to microsoft.public.sql
or another sql newsgroup on occasion.

Also, even though there may not be a logical limit to the depth of your
model, you will run up against physical limitations - performance, storage,
administration cost, etc., at some point. Whatever model you choose, make
sure you take all that good stuff into account.

Good luck,
Michael C.

clintonG said:
Well thank you Michael and Anders alike. Your references to Joe Celko
and his work has me eager to get going..

Answering Michael's question regarding how deeply the model needs to
nest is why I think I needed to be referred to a 'Joe Celko' as the model
I need must enable indeterminate levels.

I want to make it possible for those with deep subject level expertise
to generate and edit their own tree(s) as they and they alone are those that
know the taxonomy and the hierarchical relationships that are pertinent to
their body of knowledge.

Maybe a tough task for me compared to what can be done using an application
such as the "Verity Collaboration Classifier" [1] but that's what I'm interested
in
learning more about and appreciate your comments pointing the way...

--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@ REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

[1] http://www.verity.com/products/vcc/index.html




Anders Borum said:
Hello!


I have been following Joe Celkos for some time and have succesfully
implemented the graph model (although in a slighty modified version that
also allows for frequent insertions) all the way from the DB layer to C#
models (that allows for tree traversions including caching etc.).

The model has many advantages to the normal parent/child adjacency list -
even though the adjancency model is quite strong, when it comes to
lazyloading children etc. because you only need to fetch a set of children
for any given parent. With some caching in the middletier, you're looking at
service cached objects (or clones), instead of fresh data from the database.

But while most graph models allows for lazyloading children, some are
stronger in terms of fetching large presorted trees, and frequent
insertions. Usually, most trees have frequent selections and in-frequent
insertions. Unfortunately, with the basic graph (nested sets) model from Mr.
Celko, you end up locking the entire table when inserting new nodes.

Explaining everything here is not going to happen, but I'd suggest you get
his latest book on "Trees and Hierarchies in SQL" and take it from there.
It's quite interesting actually.
Click to expand...
Click to expand...
 
Thanks again Michael...

<%= Clinton

Michael C said:
I ran into a similar problem with hierarchical datasets, way back before I
heard about Joe Celko. I beat my head against the wall for about a month
trying to figure it out until I bought his book. He's definitely got the
most in-depth coverage on the topic I've seen. Most authors that touch on
it will dedicate maybe 3 or 4 pages, and barely scratch the surface. He
dedicates whole books to it. I think he also posts to microsoft.public.sql
or another sql newsgroup on occasion.

Also, even though there may not be a logical limit to the depth of your
model, you will run up against physical limitations - performance, storage,
administration cost, etc., at some point. Whatever model you choose, make
sure you take all that good stuff into account.

Good luck,
Michael C.

clintonG said:
Well thank you Michael and Anders alike. Your references to Joe Celko
and his work has me eager to get going..

Answering Michael's question regarding how deeply the model needs to
nest is why I think I needed to be referred to a 'Joe Celko' as the model
I need must enable indeterminate levels.

I want to make it possible for those with deep subject level expertise
to generate and edit their own tree(s) as they and they alone are those that
know the taxonomy and the hierarchical relationships that are pertinent to
their body of knowledge.

Maybe a tough task for me compared to what can be done using an application
such as the "Verity Collaboration Classifier" [1] but that's what I'm interested
in
learning more about and appreciate your comments pointing the way...

--
<%= Clinton Gallagher
A/E/C Consulting, Web Design, e-Commerce Software Development
Wauwatosa, Milwaukee County, Wisconsin USA
NET csgallagher@ REMOVETHISTEXT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

[1] http://www.verity.com/products/vcc/index.html




Anders Borum said:
Hello!

Joe Celko wrote a couple of excellent books on hierarchical database
models.
How many levels deep is your hierarchical structure?

I have been following Joe Celkos for some time and have succesfully
implemented the graph model (although in a slighty modified version that
also allows for frequent insertions) all the way from the DB layer to C#
models (that allows for tree traversions including caching etc.).

The model has many advantages to the normal parent/child adjacency list -
even though the adjancency model is quite strong, when it comes to
lazyloading children etc. because you only need to fetch a set of children
for any given parent. With some caching in the middletier, you're looking at
service cached objects (or clones), instead of fresh data from the database.

But while most graph models allows for lazyloading children, some are
stronger in terms of fetching large presorted trees, and frequent
insertions. Usually, most trees have frequent selections and in-frequent
insertions. Unfortunately, with the basic graph (nested sets) model from Mr.
Celko, you end up locking the entire table when inserting new nodes.

Explaining everything here is not going to happen, but I'd suggest you get
his latest book on "Trees and Hierarchies in SQL" and take it from there.
It's quite interesting actually.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top