Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: ID management on Offline and online database

    Hi everybody

    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.

    Please advise

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Are you using Merge Replication?
    How Merge Replication Works

    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.

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Hi

    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.

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  5. #5
    Join Date
    Mar 2009
    Posts
    3
    Thanks, i have used a different column for the id, and its working.

    best reagrds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •