I've got a fundamental db schema composed of 2 tables One is a straightforward ID -> Text listing of terms, and also the other has 2 posts, parent and child. The ids within the first table are produced on place with a db sequence as the second table consists of a mapping between secrets to keep the 'structure' from the hierarchy.

My issue is which i might want to sometimes move a tree in one db to a different. Basically have 2 DBs, each with 10 terms in (Database A's terms != Database B's terms, and there is no overlap), and that i just copy the information from One place to another then I'll have an apparent problem the terms is going to be renumbered however the associations wont. Clearly within this example just adding 10 to any or all the connection secrets works, but does anybody are conscious of an over-all formula to get this done?

The DB is oracle 11g, as well as an oracle specific option would be fine...

Quick answer

Import right into a staging table, but populate planned ID values in the same sequence accustomed to produce ID values in the destination table. This really is certain to avoid conflicts between ID values as DBMS engine supports concurrent use of sequences.

Using the ID values around the node planned (see below) re-mapping the ID values for that edges is trivial.

Longer answer

You may need a mechanism that maps the values between your old secrets in the source and new secrets within the destination. The best way to do that would be to create intermediate staging tables that contain the mappings between your old and new kays.

In Oracle, autoincrementing secrets are often completed with sequences in much the way in which you've referred to. You have to construct staging tables having a placeholder for that 'old' key to help you perform the re-mapping. Make use of the same sequence as utilized by the applying to populate the ID values on actual destination database tables. The DBMS enables concurrent accesses to sequences and utilizing the same sequence guarantees that you won't get collisions within the planned ID values.

For those who have a schema like:

create table STAGE_NODE (
       ID int
      ,STAGED_ID int
)
/

create table STAGE_EDGE (
       FROM_ID   int
      ,TO_ID     int
      ,OLD_FROM_ID int
      ,OLD_TO_ID int
)
/

This will help you to import in to the STAGE_NODE table, protecting the imported key values. The place process puts the initial ID in the imported table into STAGED_ID and populates ID in the sequence.

Make certain you apply the same sequence that's employed for inhabiting the ID column in the destination table. This guarantees that you simply will not get key collisions when you attend place towards the final destination table. You should re-make use of the same sequence.

Like a helpful side-effect this can also permit the import to operate while other procedures are happening up for grabs concurrent reads on one sequence are fine. If required you are able to run this kind of import process without lowering the applciation.

After you have this mapping within the staging table, ID values within the EDGE table are trivial to compute having a query like:

select node1.ID         as FROM_ID
      ,node2.ID         as TO_ID
  from STAGE_EDGE se
  join STAGE_NODE node1
    on node1.STAGED_ID = se.OLD_FROM_ID
  join STAGE_NODE node2
    on node2.STAGED_ID = se.OLD_TO_ID 

The planned EDGE values could be populated into the staging tables utilizing an UPDATE query having a similar join or placed into the destination table from the query like the one above.