Re: SQL over tree-type database?

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) )

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

  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
>
>
Received on Mon May 07 2001 - 03:48:23 CEST