1

I'm working on architecting a sqlite database for a gallery application. I would like to know if using a time stamp to check if the db has been updated is good practice, or if there is a better way to do this.

Here's what I have...three tables:

buildings
  buliding_id PK
  code
  name
  description
  timestamp

building_album
 album_id PK
 building_id FK (reference to buildings->building_id)
 album_url
 timestamp

building_images
 image_id PK
 album_id FK (reference to building_album->album_id)
 image
 timestamp

As of now I would have the application check against the server's db to see if any time stamps have changed, and if they have change those fields.

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
에이바바
  • 1,011
  • 11
  • 36
  • 60
  • Against what do you compare the timestamps? –  Jul 30 '12 at 14:14
  • 1
    If you don't actually care about the timestamps, which it sounds like you don't, use version numbers instead. If you also add a version number for the database as a whole, the checking-for-updates process will be very fast. – bzlm Jul 30 '12 at 14:15
  • @Tichodroma, the time stamps from database the application currently has – 에이바바 Jul 30 '12 at 14:15
  • @bzlm, perhaps I'm wrong but if I checked against a version number wouldn't I have to download the entire database each time it's updated? Is this better than just changing some fields? – 에이바바 Jul 30 '12 at 14:17
  • 1
    @에이바, I'm assuming your application would query the database for the last-modified time of each building/album/image. It could simply query against a version number instead. And if the "global version number" hasn't changed, then there's no need for further queries. Also, don't forget to handle additions/removals of buildings/albums/images. – bzlm Jul 30 '12 at 14:22
  • 1
    Convention is often to not only keep track of modify timestamp (and/or some version/transaction id), but also creation and deletion, too. Perhaps you don't need in this case, but it's always useful for detecting insertions, modifications, and deletions. – Rob Jul 30 '12 at 14:29
  • 1
    The notion of a version number and/or transaction id is that you can execute a query that says "retrieve me records that have changed since version number x" or "... since transaction id y". – Rob Jul 30 '12 at 14:51
  • @RobertRyan can you point me in the direction of something to read on setting up transaction/version ids to do that? – 에이바바 Aug 01 '12 at 13:12

1 Answers1

4

I'd start here for a broader discussion on the topic: How to Sync iPhone Core Data with web server, and then push to other devices?.

But if you wanted to do some simple, one-way synchronizing of information from the server to the mobile device, one "simple" approach would be:

  1. modifying that server database to introduce the concept of some unique identifier (whether a transaction id or version id) that is used across all of the tables;

  2. making sure that the server is modified to record any creations, updates, and deletions to the "master" tables with that identifier which is automatically incremented/updated; this transaction/version identifier could either in the tables themselves or using some shared transaction log;

  3. writing a routine on the server so the client can retrieve the current identifier;

  4. have the app keep track of the last identifier for which it successfully received and completed synchronization/replication;

  5. write some server routine that the client can call with its "last successful" identifier, for which the server will return all records that have changed since that identifier; and

  6. write some mobile device code to take the response from the prior step and update its internal database accordingly.

If you want to also synchronize changes on the mobile and send them back to the server, or if you have constraints upon what changes you can make in the server database, then this all becomes significantly more complicated.

Community
  • 1
  • 1
Rob
  • 415,655
  • 72
  • 787
  • 1,044