Re: Duplicating tables in a distributed database

Suggestion: use a "last change time stamp" in both places. Keep only in the "legal copy" data base what has been locally requested from the
"system of record" data base. Thus, the "legal copy" data base acts as a
"persistent local buffer" and contains only the needed data subset of
the "system of record data base.

It works like this:

1. the "legal copy" data base (btw, same structure) is queried for data in a table
2. the "legal copy" data base has a trigger defined that reacts if the data is there: it sends the table key and the associated "last change time stamp" to the "system of record" data base
3. if the "last change time stamp" for that key is the same, then the "system of record" data base sends back a short message to that effect to the "legal copy" database that will release the part of the query that corresponds to that table.
4. if the "last change time stamp" for that key is not the same, then the "system of record" data base sends back the updated row to the
   "legal copy" database that will update itself with it before it releases
   the part of the query that corresponds to that table.
5. if the row corresponding to the key no longer exists on the
   "system of record", then the "system of record" data base sends back a
   "not found" message to the "legal copy" database that will delete the
   row before it releases the message to the part of the query that corresponds to that table.
6. the "legal copy" data base has also a trigger defined that reacts if the data is not there: it sends the table key to the "system of record" data base which sends the table row (or a "not found" message") to the "legal copy" database that will insert it before it releases it to the part of the query that corresponds to that table.

I hope this helps.

François Received on Wed Jan 24 2001 - 20:14:14 CET