Re: Newbie DBase architecture questions

jraustin1_at_hotmail.com (James) wrote in message news:<a6e74506.0206021524.53ed1564_at_posting.google.com>...
> > > > Products in a store are often belong to more than one category
> > > > -- it means that the node can have more than one parent.
> > >
> > > I still don't understand, below is a tree where the same product is in
> > > 3 different departments.
> > >
> > Now you change the cost for MyStoreFldr->Houseware1->Lighting1->LavaLamp1.
> > What happens with the others?
> > What if the whole Lighting section is in 2 different departments?
>
> Couldn't it be handled as shown below where:
> -> is a reference to the original
> () is the qty
>
> MyStoreFldr
> Houseware1
> ->Lighting1
> ->LavaLamp1(X)
> Hardware1
> DeptX1
> DeptX2
> Lighting2
> ->LavaLamp1(Y)
> Entertainment1
> DeptX1
> DeptX2
> DeptX3
> ->Lighting1
> ->LavaLamp1(Z)
>
> LampsFolder
> LavaLamp1
> LavaLamp2
> LavaLamp3
>
> DeptsFolder
> Lighting1
> Lighting2
> Lighting3
>
> I have implemented similar concepts in rdb, but it required generic
> modelling (everything in 4 or 5 tables) which limited full use of SQL,
> required recursive type programming, impacted performance
> significantly and won't necessary recommend it as a viable solution.

That's easy in network database, but in RDBMS you don't have pointers/references. It doesn't necessarily mean that RDBMS is in any way inferior -- even for this problem. Transitive closure can be handled much the same way as Materialized Views do. A user queries against original adjacency list:

select productName from Sections s, Products p where s.product# = p.product#
connect by section = prior parent_section start with section = 'Lightning'

The optimizer would find a Materialized Transtive Closure table 'SectionsTC' and rewrite the query as

select productName from SectionsTC s, Products p where s.product# = p.product#
where section = 'Lightning'

In this particular example the benefit of using Materialized Transitive Closure may be not that evident, because RDBMS chases pointers [or, rather, surrogate keys] quite efficiently on the original Sections table (with the 'section' column indexed, of course). Still, Materialized Transitive Closure is handy for more complicated queries - aggregate totals - for example.

As you see, efficient network queries is certainly possible with Oracle 'connect by' construct. I'm not sure if it's also possible with generic ANSI-standard recursive SQL. Received on Mon Jun 03 2002 - 19:44:18 CEST