Create process for synchronizing changes with external databases

[fancyham]

Overview

Some Food Oasis regions will have multiple partners who will provide data sources for listings.

One question then becomes how to import changes from our partners, and how our partners can update their databases with the latest Food Oasis listing info (for example if a correction has been made)

We need a way to process and synchronize changes when listing data lives both on Food Oasis’s database as well as an external database.

For example:
In Hawaii, we are initially starting with one data source (Aloha Harvest) and Food Oasis will be showing data imported from there and AH’s database will be the ’source of truth’.

In the future, however, as we add additional listings and data sources, AH’s database will no longer be the source of truth.

In fact, crowdsourced corrections made to FO might need to be replicated to AH's and other databases.

In this case, is FO the source of truth?

Let’s define some processes to find changes and to sync data sources with each other.

Action Items

• Discussion about replication needs, architecture, and tradeoffs
• Process for replicating changes from an external database to FO
• Process for replicating changes from FO to external databases
  • One possibility: Automatically send a summary email to our partners with listing updates
• Consistency check to validate that data is replicated correctly between FO and other databases

Resources/Instructions

https://en.wikipedia.org/wiki/Single_source_of_truth

[fancyham]

One way is to find changes in a source database and manually update the destination database with any changes.

For our initial situation in Hawaii, this makes a lot of sense since Aloha Harvest is our ’source of truth’ until we start adding other listing data providers to the mix.

The process would be:

1. Enter all accurate data in source-of-truth (SOT) database first
2. Export SOT database and timestamp it. This is a dated snapshot.
3. Compare spreadsheets to find changes since the last snapshot
4. Replicate those changes on the Food Oasis website via the org editing screens
5. Save the most recent timestamped SOT database snapshot for future use.

This is great if done perfectly, but there may be minor mistakes over time. We will want to find a way to compare FO to the SOT database.

##Finding differences between database snapshots:

There are some tools ($$$) that do what programmers call ‘diffing’ (named after a program called ‘diff’ that finds differences between two documents).

But fortunately, I think that the free Google Sheets can help do the task of comparison.

“Show differences between Sheet1 and Sheet2"

Import two spreadsheets into this Google Sheets document and it will indicates where the two sheets are different. Perfect for comparing old and new CSV files.

Save a copy and give it a try:
https://docs.google.com/spreadsheets/d/1adhHjgh9IGpvcPTj4LWZNduCFFA8aHwxFOvaR8IAJ18/edit?usp=sharing

The formula for the cells is =IF(Sheet1!C2 <> Sheet2!C2, Sheet1!C2 & " | " & Sheet2!C2, "=")

More info: https://www.wikihow.com/Compare-Two-Sheets-in-Google-Sheets

Useful for this project but probably lots of other ones as well.

There may be web versions of this tool that may be useful or even easier, but be sure to read the privacy notices: diff compare csv
https://www.google.com/search?q=diff+compare+csv