CODEWITHSUNDEEP
androidmysqlsqlitespring-bootdata-synchronization
ads
ads

Reputation: 1723

How to properly implement a data sync between mobile client database and server mysql database

I've developed an android app that allows users to log certain activities. I now want to add a feature where users can back-up the data to a server so that if they have multiple devices, they can just download from it.

I started working on a Spring-Boot based web application. This will be purely json and will not have a web-ui.

I came upon a question regarding primary keys and foreign keys. For example I have this table on the server.

CREATE TABLE user_activities (
  id INT AUTO_INCREMENT
, user_id INT
, activity_date DATETIME
, notes TEXT
, PRIMARY KEY (id)
);

This table on the SQLite on the client.

CREATE TABLE user_activities (
  id INT AUTO_INCREMENT
, user_id INT
, activity_date LONG
, notes TEXT
);

When the user creates the activity record offline, it will generate a primary key. When the user goes online, I will have to sync the data by doing an httpPost. However, I can't use the id generated as it might cause duplicate issues.

What kind of approach can I use on this?

Appreciate any help.

UPDATE 1

Ok. So based on comments from Jorge Moreno and nuuneoi, I've come up with this design.

UPDATE 2

After looking at the design, I might have missed a column in the client side. Added device_id column there as well.

I figured that local_id could be similar across user devices so adding device_id would make it impossible to have similar values using both device_id and local_id.

Server

CREATE TABLE user_activities (
  id INT AUTO_INCREMENT
, device_id VARCHAR(100) -- Actual Devices Id (e.g. Phone, Tablet). To handle if a user has multiple devices
, local_id INT
, user_id INT
, activity_date DATETIME
, notes TEXT
, PRIMARY KEY (id)
);

Client

CREATE TABLE user_activities (
  id INT AUTO_INCREMENT
, user_id INT
, device_id TEXT -- Added on UPDATE 2
, activity_date LONG
, notes TEXT
);

Appreciate any feedback.

Upvotes: 0

Views: 1609

Answers (3)

ads
ads

Reputation: 1723

After reading a few threads listed below, I've decided to use UUID as my primary key columns and just generate some random UUID from each client.

how good is java's UUID.randomUUID? Android (distributed application) primary key strategy

Upvotes: 0

nuuneoi
nuuneoi

Reputation: 1798

How's about this table on the server.

CREATE TABLE user_activities (
  id INT AUTO_INCREMENT
, local_id INT
, user_id INT
, activity_date DATETIME
, notes TEXT
, PRIMARY KEY (id)
);

and this table on SQLite?

CREATE TABLE user_activities (
  local_id INT AUTO_INCREMENT
, user_id INT
, activity_date LONG
, notes TEXT
);

Upvotes: 1

Jorge Moreno
Jorge Moreno

Reputation: 378

Don´t sent the id value from sqlite, sql server had the id autoincrement

Upvotes: 2

Related Questions