0

My system needs to get only the updates from the server. How do I design my database? Have an audit table or is there any other design mechanism? What I'm planning is to send an update id from my device, and retrieve the new updates. How to really implement this?

Sivaneasharajah Lushanthan
  • 3
  • 1
  • What kind of updates are you referring to? What that has to do with database? You need to provide more details – Suhas Nov 03 '11 at 04:59

3 Answers3

1

It sounds like you're looking for SQL Server Replication. You might want to look at the technet article Exchanging Data with Mobile Users which describes two common models, and how you can use SQL server to help you.

You could choose to roll your own with audit tables but you'll still need to manage the problems of update collision that the replication services are intended to help you solve.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • I think he said he needs to get (read) what has changed. Not sure that implies replicating two databases. Could be a client pulling what has changed since last access. – bryanmac Nov 03 '11 at 05:08
0

Writing to an audit or change table what has changed is one approach as you have mentioned.

If you're using SQLServer, there's also a timestamp column. It's not a datatime column - the name is mis-leading. Instead, it is an incrementing number and when a row is touched, it gets the next number. If you get max timestamp and then later, query all rows with timestamp greater than that long, you'll get the rows that were changed/added.

http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

One approach I would specifically be wary of is using datetime as the watermark. It shifts, can change and is not good for use in an algorithm - especially to detect what has changed. I've seen systems fall down trying to rely on datetime as a reliable watermarking approach.

bryanmac
  • 38,941
  • 11
  • 91
  • 99
  • // Yes, I went across time stamps and I found it really interesting. But how to implement it properly? Am I going to insert a time-stamp column in all the tables I have, or use one single audit table where data gets inserted using triggers called by other tables. If so, what would be my table structure? //Table Action_History (Action_Id,Action,TableName, Primary_Key_OfTable,TimeStamp) // OR // Table Action_History (Action_Id,Action,TableName,Column Name,Old_Value, New_Value ,TimeStamp)? Then, what about joining tables with composite PKs? How do I track their changes? – Sivaneasharajah Lushanthan Nov 03 '11 at 10:17
  • For the timestamps - let's say you had a customers table. And, that customers table is related to others. As customers are added or edit, their timestamp column changes. Then the consumer that is trying to get "all the customers that changed" would call a method GetCustomersSince(long watermark) method. that make would query for all customers with timestamp > watermark and return it. – bryanmac Nov 03 '11 at 12:23
  • For the history table would have to have something like object_type_id, object_id, action_id, who_changed. In that solution, it's an append only table and the autoseed pk id becomes the watermark. If I did that, I would avoid triggers and have sprocs or batches write that in a transaction. – bryanmac Nov 03 '11 at 12:27
0

Years ago, I implemented this kind of design. It was very trivial but it worked. As far as I understand, you want to push update to your client like Adobe does. Keep a table in database(it even need not be a table, you can keep entry in XML). When your client application loads, check the version against server, if mismatch then download the latest update and then update the client version else normally load your client application

Anand
  • 14,545
  • 8
  • 32
  • 44