My own approach to this is to use MAC address/time based UUID's (GUID's
for Micro$oft platforms) for primary keys. My own requirements include
auditing, so I add an integer version number to the primary key. I
store the UUID's as 36 ASCII characters, although there are some
firebird specific tricks to improve performance and compression.

UUID's have a "reasonable degree of uniqueness" based on a number of
assumptions until the year 3020, plus or minus a couple of years. In
practical terms, the use of UUID's allows nearly universal merging of
instances of the "same" tables without fear of key collisions, or
altering keys during migration.

For any purpose related to audit trails in the US, this latter issue is
critical. By law, you can never break an audit trail (Sarbanes-Oxley,
or SOX for short). Changing keys (sign flipping, temporary tokens, etc)
without an explicit trail of where, when, and why the key was changed is
implicitly verboten in business applications that are related in any way
to the flow of money.

I also use an "insert only" paradigm, in which rows are updated only for
the purpose of timestamping the row as inactive, and rows are never
deleted. The effect of altering a row is to timestamp the old row as
inactive, and insert a new row with the next version number.

The technical implementation of this is trivial. A one-way merge
consists of selecting a record from one database, updating inactivated
records, and inserting new records into the second database. A two-way
merge is simply two one-way merges operating concurrently in opposite
directions.

By keeping a last merge timestamp, the amount of data moved can be
limited to only rows that were inactivated or added after the last
merge.

My baseline structure for all tables is:

ID CHAR (36) ASCII not null
VERSION INTEGER not null
CREATE_STAMP TIMESTMAP not null
CREATE_USER CHAR(36) ASCII not null
CREATE_METHOD VARCHAR(256) not null
VOID_STAMP TIMESTAMP
VOID_USER CHAR(36) ASCII
VOID_METHOD VARCHAR(256)

Primary key:
ID, VERSION

Additional indexes:
CREATE_STAMP desc
VOID_STAMP desc


All of my own tables are extended from this basic structure.

On Sun, 2005-06-26 at 16:15 +1000, Grant Brown wrote:
> Hi all,
>
> Using Delphi 5, FIBPlus and FireBird 1.5
>
> Never had anything to do with replication before so please bear with me.
>
> I need to be able to allow user of our product the ability to
>
> 1) - Disconnect from the server DB (FB 1.5 in Superserver mode)
> 2) - Take there laptop out into the field and edit documents and or
> create new ones
> 3) - Connect back into the server and upload new or changed documents.
>
> So in order to do that I assume that they will need a copy of the DB on
> there laptop as the embedded version and also access to the server DB
> (please note that we have the server DB up and running)
>
> So first question - How does one swap between embedded version and
> server version at runtime
>
> Next question - the big one, how does one implement replication.
>
> What are the gotchas, for example how do we control the number
> generators that create the PK numbers. Wont the get all out of wacko
>
> TIA
>