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.