Re: SQL over tree-type database?

From: Carlos Bromanski <cbroman_at_shpamcore.com>
Date: Sun, 6 May 2001 20:48:23 -0500
Message-ID: <3af5fe66$0$42879$1dc6e903@news.corecomm.net>

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"

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
>
>
Received on Sun May 06 2001 - 20:48:23 CDT