Expanding parent-child tree with additional tables

database-designoracle

I have readonly access to a table (Oracle database) which contains a hierarchical structure, represented via parent-child.

I need to add another table, which will expand the existing hierarchical tree.
The new table needs to add levels to the tree, including entire sub-trees.

The problem is this:
Any node in the expansion table needs to have a single parent, yet that parent can be in either the original table, or in the expansion table.

How can I accomplish this safely?

Things I thought of so far:

Giving each node in the expanded table two parent (one from each table)
Problem: data integrity!
Each node will have one parent, without knowing which table contains it
Problem: querying will be difficult.
Problem 2: same ID for two different nodes (in different tables)
Each node will have one parent, with another column specifying which table contains it
Problem: querying still difficult.

Best Answer

Here's an idea: build your "expansion table" with something like this:

expanded_table (id number, parent_src char(1), parent_id number, ...)

The parent_src column should indicate where the parent comes from, e.g. you could use 'O' of original, 'E' for expansion. (Add a primary key on id, of course.)

To handle the querying, use a view defined like this (pseudo-sql):

select 'O' source, o.id, 'O' parent_src, o.parent_id, [...] from original_table
union all
select 'E' source, e.id,   e.parent_src, e.parent_id, [...] from expanded_table

You can do hierarchical queries based on (source,id)/(parent_src,parend_id) pairs.

No problem with duplicate/overlapping ids, the source info disambiguates them.

Now data integrity is going to be very problematic.

You can't have check constraints that reference other tables, and I don't know of a way of building a "conditional foreign key" that would work here.
You could use triggers to make sure DML on the expanded table is coherent, but even with those and triggers on the original table (if you could do that, which doesn't seem to be the case), that would be very tricky to get right.

Having all DML to this "structure" go through PL/SQL that you control could do it, but that doesn't appear to be possible given the information in your post.

Compromise Solution...

You will still need a Stored Procedure, but not using recursion. Using a simple loop, you can construct a self-join query and executed it dynamically. Given the depth of the tree you are looking for, here is such a Stored Procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `dianuj`.`GetTopParentGivenDepth` $$
CREATE PROCEDURE `dianuj`.`GetTopParentGivenDepth` (GivenDepth INT)
BEGIN

    DECLARE x1,x2 INT;

    SET x = 0;
    SET y = 1;
    SET @SQ = 'SELECT DISTINCT A0.id FROM prarent A0';
    WHILE y < GivenDepth DO
        SET @SQ = CONCAT(@SQ,' INNER JOIN prarent A',y,' ON A',x,'.id = A',y,'.parent_id');
        SET x = y;
        SET y = x + 1;
    END WHILE;
    SET @SQ = CONCAT(@SQ,' WHERE A0.parent_id = 0');
    SELECT @SQ;
    PREPARE stmt FROM @SQ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

END $$

DELIMITER ;

After loading your data into MySQL on my desktop, I ran the Stored Procedure with...

Depth 1

mysql> call GetTopParentGivenDepth(1);
+--------------------------------------------------------------+
| @SQLSTMT                                                     |
+--------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 WHERE A0.parent_id = 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

Depth 2

mysql> call GetTopParentGivenDepth(2);
+------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
|  2 |
+----+
2 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Depth 3 .. 6

mysql> call GetTopParentGivenDepth(3);
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(4);
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id WHERE A0.parent_id = 0 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call GetTopParentGivenDepth(5);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id WHERE A0.parent_id = 0 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+----+
| id |
+----+
|  3 |
+----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call GetTopParentGivenDepth(6);
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @SQLSTMT                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT DISTINCT A0.id FROM prarent A0 INNER JOIN prarent A1 ON A0.id = A1.parent_id INNER JOIN prarent A2 ON A1.id = A2.parent_id INNER JOIN prarent A3 ON A2.id = A3.parent_id INNER JOIN prarent A4 ON A3.id = A4.parent_id INNER JOIN prarent A5 ON A4.id = A5.parent_id WHERE A0.parent_id = 0 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>

Notice that Depth 6 has no return value. To find the maximum depth, you would have to iterate on it until no rows comes back.

CAVEAT

The worst case scenario would be to use the number of rows in the table as the depth. Such a tree would be nothing more than a degenerate linked list.
I cannot give you any guarantees on query performance, such on SQL construction and subsequent execution. Any kind of recursion would be the sole responsibility of the Query Parser.

Give it a Try !!!

How to get value from parent row

May be this will help.

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic, NVL(a.configuration,CONNECT_BY_ROOT configuration) as conf from topicdet a
connect by nocycle prior a.topic=a.parenttopic;

if you dont want to use hierarchical query then

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic,case when a.configuration is null then (select b.configuration from topicdet b where b.parenttopic=a.topic) else a.configuration end as config from topicdet a