0

I am developing a mobile app that will be able to work in offline mode.

I have an online application that consumes a table called "Books" in a database located on a server, and my mobile app has a sqlite database with the exactly table "Books".

When the app gets internet avaible, it syncs the data from "Books" table - located in sqlite - to "Books" table - located in SQL Server.

I am using the MERGE (Transact-SQL) statement to perform it. But I have a problem.

Imagine my mobile app is without internet connection for 7 days and the tables are:

Books(SQL Server)
+----+---------------------------------+--------------+
| Id | Name                            | LastModified |
+----+---------------------------------+--------------+
| 1  | Modern Operating Systems        | 2019-09-20   |
+----+---------------------------------+--------------+
| 2  | The Art of Computer Programming | 2019-09-20   |
+----+---------------------------------+--------------+

Books(sqlite in mobile App)
+----+---------------------------------+--------------+
| Id | Name                            | LastModified |
+----+---------------------------------+--------------+
| 1  | Modern Operating Systems        | 2019-09-20   |
+----+---------------------------------+--------------+
| 2  | The Art of Computer Programming | 2019-09-20   |
+----+---------------------------------+--------------+
| 9  | Computer Networks               | 2019-09-26   |
+----+---------------------------------+--------------+

Consider that both tables in sqlite and SQL Server contains Auto Increment Primary Key, if use the "Id" as the criteria of merge, it would get a dirty merge.

How should I do the merge in this case ?

Alan Nunes
  • 163
  • 4
  • 15
  • 6
    You can't use an `int` id as you single PK when running a distributed system. Easy solution is use uniqueidentifier. Another solution is allocating different ranges of numbers to each database. Another solution is having a composite OK with a database id + sequence number. – Dale K Sep 26 '19 at 20:32
  • 4
    ^ Composite PK. – Dale K Sep 26 '19 at 20:40

1 Answers1

0

Using a composite primary key solves my problem.

Alan Nunes
  • 163
  • 4
  • 15