Re: SQL over tree-type database?

Hello

Thanks for all the replies and sorry for disappearing after asking a question. I noticed that earlier thread on essentially the same matter and will look through it later - I'm just a bit busy with other things at the moment. I think, a solution that would satisfy me is (I actually already hinted at it in my question): reduce redundancy by splitting the tree into 2-level tables (my advantage is that the number of levels is fixed and known, which, perhaps makes it trivial) thus reducing storage; create a view on all those tables, that would emulate the BIG redundant table, and then use it for queries, thus keeping them simple. Yes, I thought about the method suggested below, ut then queries (at least some of them) would become HUGE and thus error-prone... Makes sense?

Thanks
Guennadi

On Sun, 6 May 2001, Carlos Bromanski wrote:

> Here is a very simple example, but perhaps this is what you're talking
> about?
>
> set of tables that hold trees:
> CREATE TABLE LevelA (NodeID int, ItemName char(30) )
> CREATE TABLE LevelB (ParentNodeID int, NodeID int, ItemName char(30) )
> CREATE TABLE LevelC (ParentNodeID int, NodeID int, ItemName char(30) )
>
> data for a couple of trees; also you could insert data from existing
> unnormalized data like yours:
> INSERT INTO LevelA (NodeID, ItemName) VALUES (1, "Blue Widget")
> INSERT INTO LevelA (NodeID, ItemName) VALUES (2, "Red Widget")
> INSERT INTO LevelB (NodeID, ParentNodeID, ItemName) VALUES (1, 1, "Round
> Framitz")
> INSERT INTO LevelB (NodeID, ParentNodeID, ItemName) VALUES (2, 1, "Square
> Framitz")
> INSERT INTO LevelB (NodeID, ParentNodeID, ItemName) VALUES (3, 2, "Plasma
> Framitz")
> INSERT INTO LevelC (NodeID, ParentNodeID, ItemName) VALUES (1, 2, "Framitz
> Paint")
> INSERT INTO LevelC (NodeID, ParentNodeID, ItemName) VALUES (2, 2, "Framitz
> Decals")
> INSERT INTO LevelC (NodeID, ParentNodeID, ItemName) VALUES (3, 3, "Framitz
> Coolant")
>
> a SELECT that spits out the entire tree:
> SELECT LevelA.NodeID, LevelA.ItemName, LevelB.NodeID, LevelB.ItemName,
> LevelC.NodeID, LevelC.ItemName
> FROM LevelA, LevelB, LevelC
> WHERE
> LevelC.ParentID = LevelB.NodeID AND
> LevelB.ParentID = LevelA.NodeID AND
> LevelA.ItemName = "Blue Widget"
>
> - cb
>
> Guennadi V. Liakhovetski <G.Liakhovetski_at_sheffield.ac.uk> wrote in message
> news:Pine.GSO.4.21.0105052140420.4335-100000_at_acms23...
> > Hello
> >
> > Sorry, I am not a specialist in databases, so, perhaps, my question is
> > simple or meaningless... We've got a relational database (PostgreSQL),
> > which is currently VERY redundant. Ok, I know how to reduce the redundancy
> > by splitting the table into several smaller (2-column in our
> > case) ones. But, anyway the redundancy would only be decreased, and not
> > removed completely, and building SQL-queries would become more difficult
> > (nested selects, etc.), unless you can create something like a view on the
> > top of several tables?... Anyway, the data naturally have tree-structure,
> > so, ideally it should be kept that way (not sure if RDBMSs can perform
> > such optimisation / redundancy removal automatically?), but I still want
> > to be able to use SQL-queries. So, ideally, the data should be stored in a
> > tree, but logically be representable as a table with rows spanning all
> > tree-levels... Is this possible? and - if yes - how?
> >
> > Thanks
> > Guennadi
> > ___
> >
> > Dr. Guennadi V. Liakhovetski
> > Department of Applied Mathematics
> > University of Sheffield, U.K.
> > email: G.Liakhovetski_at_sheffield.ac.uk
> >
> >
>
>
>
>
>
>
>

___

Dr. Guennadi V. Liakhovetski
Department of Applied Mathematics
University of Sheffield, U.K.
email: G.Liakhovetski_at_sheffield.ac.uk Received on Thu May 10 2001 - 17:49:33 CEST