Re: Parts explosion with repeated subtrees

Costin Cozianu wrote:
: It looks to me that it's a badly designed schema to begin with.
:
: (Food_Item -> Item_Type) and food_item is not candidate key.
:
: Technically what you have is a DAG (directed acyclic graph) and not a
: forest. What it boils down to is the transitive closure. What's about
: this problem that is different from any other parts explosion problem ?
:
: Transitive closure should have been part of SQL and SQL based products
: ages ago, and probaby it's not yet, but can be solved relatively painful
: using Db2 recursive queries, Oracle CONNECT BY operator, or using hand
: made "materialized view" tables updated via triggers. I don't know
: what's the problem with "trees". Vendors don't pay attention to the
: users (they're busy implementing other UFOs like XML, "objects" and
: object pointers), the SQL committee doesn't pay attention to anyone. I
: mean recursive queries might be a cool trick in theory but it can't
: address the basic needs of the user: a safe and simple way to resolve
: the transitive closure.
:
: Joe, you have to confess that you orchestrated this whole thing, so that
: you can publish a whole book full of twisted and mind boggling Joe Celko
: style SQL, solving a problem that in a normal world would be absolutely
: trivial :)
:
: How about SQL committee specify a TRANSITIVE CLOSURE JOIN, and vendors
: implement it correctly, would you still publish a book on trees in SQL ?

How about performance issues? Optimization? Even if something like TRANSITIVE CLOSURE JOIN (TCL from now on) becomes a standard, the alternate hierarchy representations would still make sense (of course, depending on what do you wanna do with your data). Can you imagine the horror of (ab)using? SQL (as is) is "adapted" (sorry for my English) to squeeze the best performance from relational model. The goal is to design our tables in such a manner that we need simple SELECT on one table, and not monsters like TCJ to make the things done.

:
: best regards,
: Costin Cozianu
:
: Is it not proven that transitive closure is impossible in SQL ?

Yes, but just because of that, you change the way you are representing your "relation" (the one you want to find it's transitive closure) and all of a sudden simple old SELECT is quite good enough.

Don't misunderstand me, I'm not against something like TCL (but I'll stay away from it if I can), but IMO even after introducing TCL into SQL, alternate representation would still make sense.

regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/

P.S. I haven't give it a second thought, but I think Oracle's CONNECT BY operator won't save us here because one node can have more than one "parent"... Received on Sat Dec 14 2002 - 19:49:46 CET