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.
  • 2
    (BTW, your question was fine on DBA.SE. It's very frustrating to have a question deleted under you when you're trying to answer :-) )
    – Mat
    Mar 24, 2013 at 9:15
  • Sorry, @Mat! I just saw that DBA.SE has less users, while StackOverflow has these kind of questions as well, and decided to move the Q to a place with a better chance of getting an answer... Mar 24, 2013 at 9:35
  • 1
    Indeed, but it's sort of a chicken and egg problem - you need good questions over there to get experts hooked up to answer. Anyway, no big deal. DBA.SE is quite responsive, but the middle of the week-end is a slow spot all over.
    – Mat
    Mar 24, 2013 at 9:38
  • 1
    And the question is about to be closed as off topic on SO - so I suspect you will need to reopen on DBA (I think though this is on topic for SO)
    – mmmmmm
    Mar 24, 2013 at 12:17

2 Answers 2


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.

  • Would you suggest 'O' and 'E' as source indicators, or perhaps 1 and 0, or an enum? Apr 2, 2013 at 7:41

Well, someone at work provided another interesting answer.

Use single table:

  • Original nodes remain as they are.
  • Expanded nodes have negative IDs.

This makes it possible to maintain data-integrity (each node has single parent), while being able to tell the Originals and Extended apart.

  • 1
    A few years down the road, this approach may not be easily extendable to the next requirement and may cause confusion because of the "hidden" concept of what a negative id really means. Using a separate bit column (IsFromExpanded) is probably more maintainable/readable in the long run. And as mentioned, the bit column should be part of the key.
    – crokusek
    Apr 28, 2014 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.