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 tablesHere's an example of how I was thinking about it:Client Databaselist--list_client_id (primary key, auto-increment)--list_namelist_item--list_item_client_id (primary key, auto-increment)--list_id--list_item_textWeb Based Master Database (Shared between many clients)list--list_master_id--list_client_id (primary key, auto-increment)--list_name--user_idlist_item--list_item_master_id (primary key, auto-increment)--list_item_remote_id--list_id--list_item_text--user_idThe 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.
Best way to maintain data between local and remote sql databases
sql;web services;mobile
Take a look at my answer to a similar question about syncing data between a server and a mobile device.There are solutions for this, so there's no need to reinvent the wheel.