On Wed, 2005-03-23 at 16:30 +0000, acegracely wrote:

>
>
> Hi,
>
> I need to make my database replication friendly. (don't worry, it is
> not in use yet. I have just converted over from Advantage Database
> Server).
>
> Am I right in thinking that the easiest way is to make all primary
> keys an integer with a generator?

This is very easy but ...

> If so, will this fall over if replication is bi directional i.e.
> depending on the time interval between replications, both ends could
> add a number of records thus the generators could be out of sync.
>

This fails with bi-directional replication.

My preferred system is independent of any particular DBMS.

I use a UUID generated by the application (guaranteed unique in the
world across all systems until 3025 or something like that, provided you
have a network adapter in place), plus an integer sequence ID as part of
the support for an an insert-only paradigm (business data is never
updated or deleted - old record is invalidated through its audit
segments and new record is generated with new sequence number).

It is inherent in any DRDBMS that a formal "primary key" in the RDBMS
sense of the word is not necessarily a useful entity. RI must occur
across n physical instances of the database, so rows cannot be
physically deleted until you can guarantee that every instance of the
database reflects the invalidation of the rows in question.

Updates have their own issues. The data model should be atomic enough
that logical updates are rare, even when physically implemented as
invalidate and insert. Even then you need to be prepared to resolve
inconsistencies during merge.