"Bill Karwin" <bill@karwin.com> wrote in message
news:e23bi30gk7@enews2.newsguy.com...
> 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.

This is where I hit my first "bump in the road". The client wants to use
the FREE version of MSDE or MySQL. Neither version offers 2-way
replication.

Same things I quit working in corporate America 3 years ago over. They want
it FREE and they want it NOW.

I'm about to tell him to get someone else. This is starting the same way
all of the pains in my ass did in corporate America. I won't do it for any
amount if it is going to be one of THOSE applications/experiences.

>
> 3. Read operations are performed on this local database.

Only in case of connection failure, right? They need to do immediate saves
and reads of that data....so writing to a central store that would not
update the local store for say 5 minutes even would not be acceptable.

>
> 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.

Actually, I was thinking of a more simple scenario and I'd be greatful for
your input on it.....

What if the local app always writes and reads from the local db. Every
record change has an associated timestamp. A seperate process handles
replicating the data to a central store (used to restore local databases in
case data is lost due to a catastrophic event at the store) by updating
records changed in either the central db or local db by selecting records
with timestamps greater than the last time/date that the records were
synchronized. This could be real-time, as long as a connection is
available.

As it will be virtually unheard of that any 2 changes will have the exact
same timestamp, the newer change always overrides the older changes, but the
older chnages are recorded anyway (for historical data - in this case they
may be customer notes, etc.).

The central office can attach to any database (including the central db) to
get their data - thus the same app used by the clerks can be used by central
office personnel with the proper security clearances for different
functions.

IF any store is offline (or the central db is not online) the UI should
indicate that the data is valid as of the last synchronization.

Does my simple solution miss anything major?

Thanks!

Jim