Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I want to achieve a database synchronisation between my server database and a client application. The server would run MySQL and the applications may run different database technologies, their implementation isn't important.

I have a MySQL database online and web accessible via an API I wrote in PHP (just a detail).

My client application ships with a copy of the online data. As time passes my goal is to check for any changes in the online database and make these updates available to the client app via an API call, by sending a date to an API endpoint corresponding to the last date the app was updated, the response would be a JSON filled with all new objects and updated objects, and delete IDs, this makes possible to update the local store appropriately.

Essentially I want to do this: http://dbconvert.com/synchronization.php

My question is about the implementation details.

  1. Would I need to add a column to my database tables with a "last modified" date?

  2. Since the client app could be very out of date if it's been offline for a long time, does that also mean I shouldn't delete data from the online database but instead have another column called "delete" set to 1 and a modified date updated appropriately?

  3. Would my SQL query simply check for all data with a modified date superior then the date passed into the API request by the client?

I feel like there's a lot more to it then having a ton of dates everywhere. And also, worry that I will need to persist a lot of old data in order to ensure that old versions of the client app always have the opportunity to delete parts of their data when they are able to sync.

share|improve this question

1 Answer 1

There are different ways to do it but in general you have 2 choices:

Rough imports: overwrite what you have with the new data coming in. So a full new import of the data.

or the alternative:

Track changes: Don't just use a modified date field but really track all changes. Then let the changes be downloadable with a from date. So, the client asks for all changes since the last time it asked. Then it will apply all changes and you can handle the arising conflicts.

share|improve this answer
    
Could you elaborate a little on "Don't just use a modified date field but really track all changes" please ? –  Daniel Jul 2 '13 at 16:58
    
I believe he means that you should keep a journal of transactions that marks the change and the time at which that change was made. Then, when you want to sync your database, you just ask for all changes made after the last sync date. If you wanted to go even further, you could have the journal keep track of only the data which is committed (so, for instance, you could ignore every transaction that involved a primary key that was inserted, updated, and deleted before the synchronization process). –  mgw854 Jul 3 '13 at 3:13
    
Correct @mgw854 that is a quite well known way to solve this issue. It is also less data to sync since it's only the changes not the whole database. –  Luc Franken Jul 3 '13 at 8:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.