Database Synch - again (still?)

I have been wrestling with this puzzle for a long time and just can’t seem to get my head around it.

I have an SQLite database (my design, so I can change the schema if necessary)…
if consists of 4 actual data tables, each table has a UNIQUE ID (UUID), as well as create, modifiy and delete dates (a deleted record at this time is just flagged and not really deleted, as it would be part of this exact puzzle :slight_smile: )

My plan is to deploy this app to various members of my family (its Genealogy data) and allow each of them to enter and/or update records based on what they know. Then I need to be able to collect copies of each individual database and synch them back into a single “Master”

Given two databases at a time (A=Master to be updated B=Incoming Database)

  • If KEY_ID is in B and not in A, then Add record from B to A
  • IF KEY_ID is in A and not in B, don’t worry about it as it IS in the “master”
  • IF KEY_ID is in BOTH, and Modify Date is NOT the same, check each field, allow user to decide which value (A or B) will be used
  • IF record in B has a DELETE date and A does not, mark A as deleted also

Repeat with any other incoming database versions…

  • once all copies have been synched to “A”
  • physically delete all “deleted” records
  • send copies of “A” back to family members

Am I missing anything? or should it be done differently? Seems bullet #3 requires manual intervention

I assume you are setting the modify date in A (master) to B’s modify date if you update A from B, and not to the actual date that you’re doing the merge. It may be relevant when considering modifications to the same record from other B databases. And are you tracking where each change came from? You may need to confirm changes with the other parties to make a decision on which one to keep. Or alternatively, if the number of other dbs is not too great, setup a comparison of all changes to a record together at the same time instead of serially. So for example, let’s say a name was “Jim” in the master, then two Bs change it to “Jimmy” but four other Bs change it to “James”. If you do each db separately, this name could keep changing between the three versions and finally remain at whatever the last DB you process has.

Also, seems you need to consider the scenario where A (master) has been marked delete from one B db yet another B db has it as modified. You’ll need to decide whether to undelete it and keep the changes from the second B db. Or like above, only one marked it delete whereas the others all modified it (or just left it as is - so didn’t delete it).

I don’t foresee this being an easy task. It might be better, if possible, to create a web app (or a local app connected to a web db, though that would be slower) so everyone sees the current data all the time, maybe with history information on all changes so everyone can see what others thought a particular data item should be.

that would be correct, for NEW records, for records manually merged it would be the date of the merge as the resulting record does not directly match either A or B before the synch began

[quote]
And are you tracking where each change came from?[/quote]
I could, be not really necessary… as it is a small group of family members… and most conflicts should be an “Oh Yeah…” not a point of contention, as it is facts as best they can be recalled or researched. (and FYI. JIMMY can be a legal name, My Father-in-Law was born “Jimmy” and never “James”)

A web app is not an option (not going to pay for Xojo Web for this, not worth it), and no intention of using any of the current free/paid online Genealogy programs either (most either do/require too much, or not enough)

This question boils down to “How is this done in the real world?”

One other idea I had was to install triggers to track each field change in each table (adding “who” did it)
and applying these as “diff” transactions of some kind