Re: synchronization with heterogeneous tables and referential integrity

Cris Carampa wrote:
> I am developing an Oracle-based application (10.1 or 10.2, SE1, Linux)
> for a company which has an accounting system based on a SQLServer2000
> database.
>
> In my application's database design there are entities which have
> foreign keys referenced to some tables that should be maintained by the
> accounting application in SQLServer.
>
> My idea is to build "shadow" tables in Oracle with the same structure of
> the SQLServer tables and implement a scheduled batch process that keep
> them synchronized with SQLServer using a database link and Heterogeneus
> Services.
>
> My question is about which method to use to make the synchronization. I
> am looking at the following solutions:
>
> 1. implement the SQLServer "shadow" tables with Materialized Views.
> Unfortunately, I noticed that you can't have a foreign key on a "real"
> table referenced to a MV, so with this solution I would lose Referential
> Integrity or at least I should try to keep it with triggers. This seem a
> Bad Thing (TM) to me.
>
> 2. implement the SQLServer "shadow" tables with real Oracle tables and
> tell the batch process to disable all the foreign keys, drop the
> "shadow" tables, recreate them with CTAS and enable all foreign keys
> again. The problem is that a deletion of a SQLServer row that has Oracle
> children would stop the CTAS statement and the table would not be rebuilt.
>
> 3. using a MERGE statement in the batch process for inserting new rows
> and update changed rows in the "shadow" tables based upon the fact that
> primary keys in SQLServer and in Oracle are the same. A second statement
> could delete the rows that were deleted in SQLServer and that have no
> childs in Oracle.
>
> Every kind of suggestion is welcome. Kind regards,

Given that SQL Server is incapable of providing a point-in-time valid view of the data how are you planning to manage an accounting application between two very different database concepts? Surely you can't play the "lets create a temp table" game.

It will help to answer your question.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)