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.
