I want to layout a design concept I have for synching a database between two devices without the use of Dropbox, iCloud or any other remote type service.
To do so each record (excluding static reference tables if any) would have three(3) fields in addition to the actual “data” fields
UPDATE_DATE : a timestamp indicating WHEN the operation took place
UUID : a unique identifier for the record (for synch purposes only)
CRUD : a single character "C"reate "R"ead "U"pdate "D"elete [DEFAULT=“R”]
Assumptions :
The database will NOT be in use on both devices during the same time period. Therefore any minor differences in timestamp will not be an issue.
The database will exist ONLY on two devices, if more than one will be designated “Database of Record” and all others must synch with it
The database will be SYNCH’d between devices before alterations on the opposite device
A “delete” operation in the “app” will only set CRUD=“D”, synch process will physically remove record.
Sync Process :
Records (by UUID) that exist on BOTH DEVICES, which ever device has the GREATEST TimeStamp AND CRUD<>“R”… overwrite record on the “other” device
Records (by UUID) that exist on DEVICE-A but NOT on DEVICE-B and have a CRUD value of “C” or “U” are inserted into DEVICE-B
Records (by UUID) that exist on DEVICE-B but NOT on DEVICE-A and have a CRUD value of “C” or “U” are inserted into DEVICE-B
physically delete from BOTH devices where CRUD=“D”
Timestamp for both needs to be saved as GMT 0 in case they’re across the world from each other and using different local timezones.
Otherwise the only thing you’d have to take into account is a record modified at the exact same time in both databases so both have a U with the same timestamp. It’s unlikely but possible and you do have to take that into account.
And this particular app will be in a situation where most likely it will be the same user (or family member) with one database on a mobile device, and the “database of record” being on a desktop
I think it will be challenging for general consumers to use an app that requires one device to be the master or “database of record.”
These days people are switching between devices often. The inconvenience of having to fetch the other device that is marked as master and demote it or sign out of data editing capabilities will be tedious. If you can’t force users to sign out of data editing mode then you very will could be in a situation where two records are written at the same point. What if they lose the device that is the “database of record” can accessory devices “promote” themselves? Do you need consensus from all known devices to vote on a new master?
Another disadvantage is even accounting for time zones the clocks can still be wrong. It is often better to have the master in the cloud. Of course if you are going to have the master sync server in the cloud and all devices have connectivity then why not just use web services and send necessary data back/forth. Using the clock on the server makes sync simpler because it is strictly first come first serve. Of course you may have situation where a user made changes for a record after the record was already deleted but they do not know. Do you save the record and allow their changes or do you say “oh I am sorry, yeah that record you were working on - it’s been deleted”
It is a real can of worms to be honest and web services to a centralized data store is significantly easier in almost every respect. Any simplistic approach is prone for major caveats in data reliability or user experience.
Of course if this is just for you then I suspect it will be fine as you are intimately aware of those limitations. I cannot help myself but ask if it only for you then perhaps you should just consider using one device.
[quote=348369:@Dave S]Assumptions :
The database will NOT be in use on both devices during the same time period.
The database will be SYNCH’d between devices before alterations on the opposite device
[/quote]
I think the two assumptions I quoted will ultimately turn out to not be as feasible as you’d like to hope. But either way, I’ll add my voice to the choir that suggests using UTC/GMT timestamps. Even when you think it is only synced by the same family, people travel these days and one can easily be in another timezone. Or each device will have timestamps on the opposite side of a DST transition.
Which reminds me of something posted in the humor thread here .
In general, two-way sync operations are non-trivial to put it politely. And like many things in programming, it is the edge cases that cause so much of the extra work.
What if one user updates say a name field and another user updates an address field. If you overwrite, you’ll lose something. Shouldn’t you proceed on a field by field basis?
this is not an enterprise database situation… it is a app where the user might have a mobile device where they enter data on the go, come home, synch to their desktop… maybe do stuff on the desktop, synch back to mobile… 99.99999% of the time it will be a single user, and even then I’d expect 1/2 of those would use one device or antoher, and never need synch at all
Each device corrected one field and (in a perfect world) would update that field to the other device. Even if both changes were made between syncs to any device.
But like I said, two-way syncs are non-trivial and collision detection is part of that. In theory, the “better way” is to automatically handle cases where different fields are changed. The real collisions are when both change the same field between syncs. One approach is to take the latest timestamp – but then again timezones or DST changes can come into play too.
That said, if your customer base views a simple sync method as adequate, I won’t try to get you to license a third part solution.
Bill was suggesting tracking changes on a field level, not on the record level. You might have another table that stores the table, column, updated value, and the UTC timestamp in it. Then update the records from that table where UTC timestamp > the last sync UTC timestamp.
