referential integrity across databases

We have a 'core' database which centralizes shared information (e.g. addresses, contacts) used by several programs - each with their own respective database.

The issues we are dealing with is how to avoid orphans (in program db's) when the core data is dedupped.

An example is as follows:

    Program A references core record 'Smith Inc.' and program B references 'Smith H. Inc.' Our

    monthly cleanup of the core db discovers 'Smith Inc.' is the same company as 'Smith H Inc.' and

    removes Smith H. Inc. Now all records which referenced 'Smith H' are orphaned in program B's db.

Options entertained so far include:

-the use of triggers on the Core db to check program

          references


          -additional tables in the core db registering program


          references

I suspect this issue is quite common - what have you done? Does Oracle have specific options for this?

Thanks. Received on Wed Sep 16 1998 - 09:23:20 CDT