If I get too academic or dry, please accept my apologies in advance.

Where possible a centralized database is preferred over a distributed
system. My pet project is designed for the heavy construction
environment, specifically targeted at the oil industry. Since the last
thing to be installed, one to two weeks after my target clientele have
left the site, is the cell tower, communications is sporadic to non-
existent at best.

First, I minimize the chances of the same data being edited by careful
design of the tables themselves. Mostly static data is separated from
relatively dynamic data. Data that is coincidentally related is also
separated. It is rare that the same row would ever be changed in two
places.

On rare occasions when conflicts do occur, precedence is established by
displaying unmergable data and having an authorized user choose between
the conflicting rows. This results in an entry in a resolution log that
includes the reasoning for selecting one row over the other. Since I
have not actually needed to use this mechanism yet, I can't vouch for
how well it works. My immediate response would be to look at my data
model and see if I had inappropriately tied two pieces of information to
the same record - name and address for example.

What sort of auditable information would be likely to change in two
places at the same time, with different results? In my experience, if
two or more different results are equally and concurrently valid in real
life, then the correct data structure supports a many-to-many
relationship.

The most likely scenario I can see is something like a current balance
in an inventory system. In this scenario, what is really needed for
auditing purposes is a record of the inventory transactions.

The current balance can only be considered valid when all transactions
are merged, so it cannot be auditable. Since it is not auditable, it
cannot reside on the same table as the inventory itself or the
transactions that are applied to it. Current balance is the sum of all
unvoided transactions for a given inventory item starting from the most
recent physical count to the most recent transaction. Storing balance
in a table is a convenience/performance thing rather than a
transactional entity.

Hope this helps.

On Mon, 2005-06-27 at 11:55 +1000, Lauchlan Mackinnon wrote:
> > 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.
>
> But how do you deal with cases where
>
> (i) the user is up to date with the current databases
> (ii) the user goes offline (say a local copy of the database is made)
> (iii) the user edits some rows offline
> (iv) meanwhile those rows are edited online in the main database
> (v) the user goes back online and wants to replicate his or her changes
>
> I would have presumed you'd need a logic of deciding which changes take
> precedence. For example, if the online changes take precedence, perhaps you
> have to come back to the user and say you can't commit changes to row 123456
> because someone has changed that row online. the new data is . . . what do
> you want to do?
>
> I think an n-tier framework would make supporting this kind of replication
> logic much easier. What is your strategy for handling these cases?
>
> Regards,
>
> Lauchlan Mackinnon