Well, I am sure this continues to be requested before but I am yet to locate a single solid response to it. I am along the way of making an answer which involves remote offices uploading data to 1 master db via web service. Essentially, each office may have a home windows service that runs every 1hr approximately, accumulates any new data right into a dataset, connects towards the server via http, and uploads the dataset. The server then imports the information and get up. Theoretically it will work. Right.
Not necessarily, so each office includes a unique OfficeID, provided to it through the server in order to have them unique around the server. In the beginning I decided to cracked it till I realized the issue with auto-increment PKs. The thing is, all of the remote offices curently have existing data and all sorts of their tables all have Auto-Increment PK's and all sorts of connected constraints. Root/Parent Tables which have the OfficeID don't have any problem as it is already unique, the issue lies using the foreign secrets for how long they'll achieve the server, they've got a NewID so the relationship using the child will lost.
Right now I've only 2 solutions.
- Remove all auto-increment and different PK constraints around the server db and employ the OfficeID to remove duplicate foreign 'keys'. or...
- When posting the dataset, keep an eye on each row and it is connected parent using things like Scope_Identity etc to ensure that each child row is connected using the correct parent row.
Option 1 is searching much simpler to implement since its less work with me, however i question about sql's performance as well as data integrity is a problem since constraints can't be enforced. Option 2 could keep things under control but Gosh the quantity of code needed is mind dazzling.
Are there more options that I am not thinking about? and when I have only the two above, the lesser of two evils.
Use GUIDs as the primary secrets. It isn't 100% foolproof (99. and a pretty big quantity of nines percent), and there's a performance penalty, but it's the simplest method of carrying this out while remaining sane.
Are you able to range from the Office id within the table and also have a composite primary key? Otherwise I'd most likely opt for @tdammers suggestion of utilizing a GUID.
Getting rid of the PK constrainst will result in disaster sooner or later. I'd election for option 2 - it's more work, but you will have a better quality and maintainable solution in the finish from it. It will imply that work databases will not match the actual anymore - they'll need up-to-date too...