How do people handle foreign keys on clients when synchronizing to master db

Question

Hi,

I'm writing an application with offline support. i.e. browser/mobile clients sync commands to the master db every so often.

I'm using uuid's on both client and server-side. When synching up to the server, the servre will return a map of local uuids (luid) to server uuids (suid). Upon receiving this map, clients updated their records suid attributes with the appropriate values.

However, say a client record, e.g. a todo, has an attribute 'list_id' which holds the foreign key to the todos' list record. I use luids in foreign_keys on clients. However, when that attribute is sent over to the server, it would dirty the server db with luids rather than the suid the server is using.

My current solution, is for the master server to keep a record of the mappings of luids to suids (per client id) and for each foreign key in a command, look up the suid for that particular client and use the suid instead.

I'm wondering wether others have come across thus problem and if so how they have solved it? Is there a more efficient, simpler way?

I took a look at this question "Synchronizing one or more databases with a master database - Foreign keys (5)" and someone seemed to suggest my current solution as one option, composite keys using suids and autoincrementing sequences and another option using -ve ids for client ids and then updating all negative ids with the suids. Both of these other options seem like a lot more work.

Thanks,

Saimon

Answer 1

From my experience it's easiest taking the composition approach, particularly when it comes to debugging issues and potential needs for rolling back, i.e. it's really helpful to know what requests came from what machine and resulted in what changes. Whenever you're effectively dealing with many to one you have to have a way to effectively isolate all of the many, it also allows you to do more intelligent conflict management when you have two of the 'many' sending updates that are non-complementary (if you want to do that sort of thing).

Answer 2

I've just thought of another possibility:

When assigning luids on the client-side, keep a map of all assignments of that luid e.g.

something like (json):

  {
   'luid123': [{model: list, attribute: 'id'}, 
             {model: todo, attribute: 'list_id'}]
  }

When we get the global luid2suid map from the server (after sync up), for each luid we look up the luid in the luid map and for each entry update the appropriate attribute in the appropriate model with the guid accordingly and then remove the entry from the luid mapping.

What do you think?

This way I avoid having to do expensive look ups in the global luid2suid map for all foreign keys of command synched. Another benefit is that foreign keys are all suids on the client too and I'd only have to look up suids from luids on the server side for cases of offline record creation and modification before synching back to server.

It's just an idea that just popped into my head. I'm still hoping for more feedback on the subject