Jim Hubbard wrote:
> They also want a local store of (at least) that store's data - or (if
> possible) all store data until the connection to the central database was
> lost.
>
> And, let's not forget a way to post back to a central database (if one is
> used) any local transactions done at a disconnected store once the
> connection to the group or central database is restored.
>
> Simple, huh?

Here's how I would design this system:

1. They perform write operations to a database connection to the master
database, which is at the home office.

2. The local sites keep a copy of the master database, which is updated
periodically with standard replication.

3. Read operations are performed on this local database.

4. If the connection to the master database goes down, the apps need to
detect this error and instead write their changes to a local database.
I'd recommend this _not_ be their local copy of the master database, but
a separate instance. I'll call this the failover write database.

5. Thus when they do queries against their local read-only copy of the
master, they should combine the query results with any changes stored in
their local failover write database. Hopefully this will be minimal,
but the application code has to account for it.

7. When the connection is restored, the contents of the failover write
database are sent to the master database. Then the local system deletes
the contents of the failover write database. Wait a few moments for the
master to process the new data, update keys, etc. Then re-synchronize
the read-only copy of the master. Thus the failover changes have moved
to the master database, and back again via replication. But now they
have had their auto-generated primary keys assigned, and other data
cleanup tasks. You might want to make the local app deny the user from
making any further changes during these moments. Also note that other
sites may have queued up changes, so the initial replication sync after
an outage may contain more volume of data than usual.

This still leaves the problem of what happens if two separate sites
create records for the same real-world entity (like a customer who calls
two separate sites). Resolving these cases is something which is not
100% automatable, in any RDBMS system.

You must plan for some living person to review new data and performs
cleanup at the home office. Some data cleanup is automatable, but it is
simply not possible to handle all cases without a human.

Regards,
Bill K.