Primary IDs in MirrorSync Can or Cannot Be Used in Relationships?

[ronsmithmd]

I'm new to MirrorSync but not new to FileMajer (I have my verion 1 disks still from 1985). The documentation seems to indicate that primary serial keys and timestamps are all you need, but in the advanced topics it seems to indicate that those primary keys should not be used in relationships? I'm confused here about the way the sync works.

I'm a Pediatrician (now in my 35th year) and will be spending next summer doing health assessments up in Onaville, Haiti. I've developed a slimmed down version of the FileMaker EMR that runs my medical practice (since 2000) to help me do this. MirrorSync seems to indicate that simple primary keys and timestamps are all I need in each table to work offline and sync back to my server when I get where there is internet service.

Individual patients in my database have self-join relationships to other individuals in that same table so that I can see who a person's parents as well as their children are. If those records are created in my iPhone while I'm offline, how does MirrorSync make sure that these self-joins fields are updated with the "new" serial primary keys? The advanced documentation seems to indicate that MirrorSync will NOT handle this at all?

If that is the case then what is it that MirrorSync CAN do for anything but simply tables with no primary key relationships? Self-joins would in particular fail, but other relationships would, it seems to me, fail also?

Can you describe how syncing exactly affects the primary keys which are used in relationships?

Edited August 4, 2017 by ronsmithmd
minor correction

[Zach360Works]

Hey Ron, 

From the advanced documentation on MirrorSync managed keys:

---

A very different approach is to allow conflicting primary keys to exist on each separate database, without ever writing those primary keys to other databases. When record #11 is written from the first database to the second (in our original example), instead of being written with primary key 11, it is written with primary 51 (the next number in the sequence on the second database). This has the advantage of the shortest possible primary keys, which are pure numeric values, with no possibility of conflicts. It is also the way that the majority of existing databases are designed. MirrorSync supports this method (and is the only sync framework that does, to our knowledge). It creates an internal table to translate between the primary keys on all database that are syncing, so that when record #11 is later updated, MirrorSync knows to change record #51 in the second database. MirrorSync also re-writes foreign keys when they are written from one database to another, so that foreign keys that contained '11' on the first database will be re-written with '51' in the second database.

---

This is a brief overview on how MirrorSync managed keys will work. If MirrorSync is configured correctly, the corresponding foreign key fields that match with the primary keys will be also changed when the sync is performed. MirrorSync stores data from each sync allowing it to remember exactly which record it matches with on the hub database.

If you are only syncing with one device and not using any filtering then there is no need for this feature. If you are syncing with more than one device and, for example, you are offline adding your own patients while others get added at your home office, this feature becomes useful. Lets say in the time that you are offline, your home office adds 10 new patients and syncs with your hub database. The hub database now has records with serial numbers 1 - 10. Now in Hati you add 2 new patients, a father and son. The son has a serial number 1 and father 2. The son's foreign key field that references his father's serial number is set to his (the father's) primary key: 2. If that relationship is set up and configured correctly this is what will happen:

When you are back online and begin your sync, MirrorSync will recognize that none of the new records that you created while offline existed during the last time you synced with the home database. Since the home database now has records that are numbered 1 - 10, MirrorSync will begin adding the new records from your phone to the hub as 11 and 12. Also, since your foreign key fields are configured to be related to primary keys using a self join relationship, these values will also change. So in your home database you will now have 12 records, the sons serial number will be 11 and the fathers will be 12. Also since the father's serial number has changed to 12, mirror sync will ensure that the sons foreign key field referencing his father's primary key is also changed to match. 

I hope I gave you some answers that you were looking for, if I'm not understanding your question or you would like to know more please let me know!

Thanks, 

Zach

[ronsmithmd]

It does, but what if I have other fields in a record that point to primary keys of other records in the SAME table? Those fields will not be changed and will point to the wrong records, correct? Or does MirrorSync change those also?

(I'm assuming by 'foreign key' you mean relationships between tables and not within the same table you see.)

Ron

Edited August 4, 2017 by ronsmithmd
minor clarification

[ronsmithmd]

I was up during the night so I went through the installation and I see that it does recognize keys in self-joins. That was very concerning for my application.

The advanced topic discussion in the online documentation led me to think that this possibly was not the case.

Thanks for your time.