Synchronizing database records

Synchronizing database records

Post by Christakis » Tue, 18 Jul 2006 14:30:26


Heyas,

I am looking at creating a client/server database app that will be used by
laptops that go out in the field and come 'home' to synchronize. While I
google for a few hours, I thought I'd just post my thoughts here to see what
other people do/have done/think. I was thinking of adding a DateTime field
called LastChanged to each table and anytime the laptop user changes a
database record it updates that field. Then when he gets back to the office
it compares all the records in that table with the master to see what needs
to be updated on the server.

The prob with this will be when 2 people edit the same record and come back
and sync, one of them will overwrite the others change etc etc. While I
suspect there is no 'best way' to do this, does anyone have any advice on
what works and what doesn't?

thanks in advance,
cj
 
 
 

Synchronizing database records

Post by Allen Eger » Tue, 18 Jul 2006 20:17:18


If your field people know what records they'll be working on, one
approach is to have them 'sign out' the records from the database that
they'll be working on remotely. The signing out would include the
date/time and userid, and the rest of the application would have to
"honor" the signed out status, showing people that the records are
"signed out", and letting people read the records, but not update them.

If you take this approach, you need to "sweep" the database regularly
looking for records that were "signed out", but not "returned".

--
Allen Egerton aegerton at pobox dot com

 
 
 

Synchronizing database records

Post by Maarten Wi » Tue, 18 Jul 2006 22:51:14


Standard functionality in all the usual databases. Even Access can do it.

Groetjes,
Maarten Wiltink
 
 
 

Synchronizing database records

Post by Bruce Robe » Thu, 20 Jul 2006 00:56:54


Its not necessarily that simple. There may be situations in which a
dependancy is altered in one location and not the other invalidating an
edit legitimately made at the other location. A simple record copy can
subsequently introduce corruption.


As I suggested above, its not only a problem of two edits of the same
record. Edits of different records can also be problematic. One approach is
to log all transactions on the db. Synchronization then consists of merging
logs.

If its viable, consider equiping the laptops with wireless/portable
internet connections and providing them with a real-time interface to the
db.