Synchronizing one or more databases with the main database - foreign keys

Question

I use Google Gears to be able to use the application offline (I know that Gears is out of date). I ran into a synchronization problem with the database on the server.

A particular problem is primary keys or, more precisely, foreign keys. When sending information to the server, I could easily ignore the primary keys and generate new ones. But then, as I find out what the relationship is.

I had one solution, make a bet that I will need to save all pk for each client. What is the best way to synchronize multiple clients with one db server.

Edit:
I thought about this, and I think that primary primary keys are not the best solution, but what other possibilities exist? Time-based does not seem right due to collisions that can occur.

GUID comes to mind, is that an option? It seems that creating a GUID in javascript is not so simple.

I can do something with natural keys or compound keys. Since I think about it, this looks like the best solution. Can I expect any problems with this?

Answer 1

This is not a complete answer, but may at least provide you with some ideas ...

The question you ask (and the problem you are trying to address) does not apply to Google Gears and remains valid with other solutions, such as HTML 5 or systems or Flash / Air.

Over the past month, ZendCon made a presentation about this subject, and slides are available on the slide show: Planning for synchronization with the browser -Local databases

Going through the thoses slides, you will see notes on several possibilities that may come to mind (some really come to your mind or in other answers):

• Using GUID
• Composite Keys
• Primary key pool (i.e. reserve a range of keys in advance)

Of course, for each of them there are advantages ... and disadvantages - I will not copy them: take a look at the slides; -)

Now, in your situation, which solution will be the best? It’s hard to say, actually - and the sooner you think about synchronization, the better / easier it will be: adding material to the application is much easier when this application is still at the development stage ^^

First, it may be interesting to determine:

• Your application is usually connected, and its disconnection is rare.
• Or, if your application is usually disconnected and only connects from time to time.

Then what are you going to sync?

• Data?
  • Like "This is a list of all the commands made by this user."
  • With this data replicated to each separately connected device, of course, each of which can change it.
  • In this case, if one user deletes a row and another adds a row , how do you know who has the “true” data?
• Or actions taken from this data?
  • Like "I am adding an entry to the list of commands made by this user"
  • In this case, if one user deletes a row and another adds a row, it is easy to synchronize, because you just need to synchronize these two actions with the central database
  • But this is not so easy to implement, especially for a large application / system: every time an action is performed, you need to register it!

There is also a special problem that we usually don’t think about until it happens: especially if your synchronization process may take some time (if you have a lot of data, if you do not synchronize often ...), what to do if synchronization is stopped when it is not finished yet?

For example, what if:

• The user on the train has access to the network, with some 3G card
• Sync begins
• there is a tunnel - and the connection is lost.

Semi-synchronized data may not be as good in most situations ...

So, you should also find a solution to this problem: in most cases, synchronization should be atomic!

Answer 2

I came up with the following solution:

Each client receives a unique identifier from the server. Wherever a primary key is mentioned, I use a composite key with a client ID and an auto-increment field.

Thus, the combination is unique and easy to implement. It remains only to make sure that each client really gets a unique identifier.

I just found one drawback: SQLite does not support auto-increment on compound primary keys, so I have to process the identifier myself.

Answer 3

I would use a similar setting for your last answer. However, to get around the auto-increment problem, I would use one surrogate key with auto-increment in your main database, and then save the primary key of the client and your client identifier. Thus, you do not lose or change any data in the process, but also keep track of which client the data was originally received from.

Be sure to also set up your unique Pk client identifier, client identifier, to include referential integrity from any child tables.

Answer 4

Is there a reasonable limit to the number of objects that a client can create when disconnected? One of the possibilities that I see is to create a kind of "local sequence".

When your client connects to the central server, it receives a numerical identifier, for example, a 7-digit number (the server generates it as a sequence).

Actual PKs are created as strings: 895051 | 000094 or 895051 | 005694, where the first part is a 7-digit number sent from the server, and the second part is a "local" sequence managed by the client.

Once you synchronize with the central one, you can get a new 7-digit number and restart the local sequence. This is not too different from what you suggested, in general. It just makes a real PC completely independent of the client’s identity.

Another bonus is that if you have a scenario where the client has never connected to the server, it can use 000000 | 000094, request a new number from the server and update the keys on your side before sending it back to the server for synchronization (this is difficult if you have many FK restrictions, although it may not be feasible).