Hello,

I'm writing a rather complex project management system (with
Firebird/IBO) for a customer. This customer want's to "outsource"
some of the planning, that is, give the software and a copy of their
database to another company and then, when the company has finished
their work, syncronize the changed database with their own "main"-
database.

The problem is the primary keys. Each table has a primary key of
integer-type and get's its value from a table-specific generator. A
lot of the tables also contain lookup-key values that reference other
tables.

I don't know how to implement the syncronization system, without
risking key violations. What happens if the third-party company adds
records to a table while the "main"-customer makes changes to the
same table? Then there are two records with different data, but with
the same primary key.

Another problem, which is in my opinion more complicated, is if the
third-party company creates a new record in one of the lookup tables,
which is referenced by another record. If the "main"-customer also
creates a new record in the same lookup table, there is not only a
primary key violation, but also a "lookup-key-violation", if you see
what I mean.

Frankly I don't know how to solve this problem, and I hope I've
managed to explain the problem clearly enough. Any help is very much
appreciated.

Should I create some kind of table locking which prevents the main
customer from editing those parts of the database that are
currently "out-sourced"?
I have a changelog system which logs all inserts, updates and deletes
in all the tables. Could I perhaps use this system and just overwrite
any existing records added after the export date?

Thanks in advance,

-Petter-