Tim Dietrich

Custom Software Developer

Home Services Portfolio Blog About Contact Newsletter

EasySync: Handling Orphaned "Child" Records

In the following scenario, it is possible to end up with "orphaned" child records in the host database:

A mobile user has pulled a parent record and one or more child records. The mobile user adds a child record, or updates an existing child record. At some point prior to the mobile user performing a sync, the parent (and its children) are deleted from the hosted database. When the mobile user syncs, the new (or updated) child record will be pushed to the host, and the child record will be added. At that point, the child will be present, without its parent record. (In other words, it will have been "orphaned.")

Working with Bill Heizer at FileMaker Inc, we've identified four possible ways to handle this scenario:

1. Simply don't worry about the orphans. Save them to the host and let them be. (You could also write a scheduled script that looks for orphaned records and either deletes them or generates a report of them for later analysis.)

2. When updating a child record (or adding a new child record) on the mobile device, "tickle" the parent records and all of its other children so that they, too, will be included in the next sync. Admittedly, this is potentially a really bad approach, especially if the child records include binary data (containers). But in some cases, this might be acceptable.

3. Develop a pre-sync "diff" process, which would identify all of the UUIDs of parent records that the child records (which are about to be pushed) are referencing as foreign keys, which are no longer (or never were) present in the hosted database - and then, on the mobile side, "tickle" those parent records (and all of their children) so that they are included in the pushed payload. This is similar to #2 above, except that it would prevent you from having to constantly push records unnecessarily. (Note that we are considering adding functionality in a future release of EasySync that would assist with this approach.)

4. Adopt a check-in / check-out approach to parent / child record combinations, to ensure that parent records are never deleted from the host if they are "in play" on mobile devices. (This is easier said than done, but if your business case supports this approach, it is an option worth considering.)

Ah, the complexity of sync!