So I have, what would seem like a common question that I can't seem to find an answer to. I'm trying to find what is the "best practice" for how to architect a database that maintains data locally, then syncs that data to a remote database that is shared between many clients. To make things more clear, this remote database would have many clients that use it.
For example, if I had a desktop application that stored to-do lists (in SQL) that had individual items. Then I want to be able to send that data to a web-service that had a "master" copy of all the different clients information. I'm not worried about syncing problems as much as I am just trying to think through actual architecture of the client's tables and the web-services tables
Here's an example of how I was thinking about it:
list --list_client_id (primary key, auto-increment) --list_name list_item --list_item_client_id (primary key, auto-increment) --list_id --list_item_text
Web Based Master Database (Shared between many clients)
list --list_master_id --list_client_id (primary key, auto-increment) --list_name --user_id list_item --list_item_master_id (primary key, auto-increment) --list_item_remote_id --list_id --list_item_text --user_id
The idea would be that the client can create todo lists with items, and sync this with the web service at any given time (i.e. if they lose data connectivity, and aren't able to send the information until later, nothing will get out of order). The web service would record the records with the clients id's as just extra fields. That way, the client can say "update list number 4 with a new name" and the server takes this to mean "update user 12's list number 4 with a new name".