Unanswered: ID management on Offline and online database
i dont know if this the right forum to post this question, so if not please suggest a forum for me.
i have two database system types one is a SQL Server 2005 database and it is online database. and the other one is SQL Express 2005 offline data base. the sql express is installed on a number of computers and it get sync to the main database every week. i'm having a problem managing the temp ids of records people are getting when creating records on the offline db and the real ids these records gets when it get sync to the online db.
is there any sugesstions on how to manage these ids. i thought about using a refrence table in the offline db to map the temp id to real ids.
Merge will manage IDENTITY values for you (after a fashion). If you roll your own solution then resolve conflicts at the centre using natural key values and generate new IDs in the central DB. Or use Guids as surrogate keys so that you don't have to worry about duplicating them.
i'm not using Merge Replication. my problem with merge replication is when the user create a record he/she will get a temp id. after the data get replicated how the user going to know the real id, my understanding the merge replication will overwrite the ids so the temp id will be lost.
Merge Rep does not overwrite IDENTITY values. It assigns different ranges of values to each subscriber.
In any case, IDENTITY is best used only as a surrogate key and not exposed to users at all. If you don't expose it then end users don't need to worry if it changes. Use another column(s) for the business key. IDENTITY should not be the only key of a table.