locked
Unique Identifiers RRS feed

  • Question

  • My current table has a integer auto incrementing value as the primary key which I need to rethink for synchronization. I thought about changing this to a GUID, but it would be complete overkill. My second option would be to use my USERID along with some DATE+TIME algorithm which still would generate a long key.
    I've also been playing around with changing the increment value to -1 in my local XSD file and generating negative based integers while offline, it seems to correctly assigning them positive based numbers after the sync.
     
    I am wondering what your opinion is for creating Unique Id's for synchronized tables?
     
    Thanks,
    Mike
    • Moved by Max Wang_1983 Friday, April 22, 2011 10:44 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, May 24, 2007 2:58 PM

All replies

  • Hi Mike,

     

    Auto increment columns are not sync-friendly types and there is no best solution for it. But before going into details I'd like to know why you need them. There seems to be two broad usage scenarios for auto increment columns:

     

    1- Data Component

    For example, purchase order number or account number. This number you would give to customers and they will use it from that point on. This puts the requirement that all sync clients must have the same value for this item.

     

    2- Normalization

    As a good database designer you would normalize your tables and link them through PK-FK relationships and one way to identify rows is to use Identity columns. But the column is internal and does not have significant meaning outside of the database structure.

     

    So, which type better describe your scenario.

     

    Thanks

     

     

    Thursday, May 24, 2007 5:44 PM
  • I guess 1 - Data Component is closer, but their are some relations as well. 

     

    The Solution is a Sales Lead system in which sales people remotely create leads and then collaborate with other sales people globally working on obtaining the business. A "Lead number" would be Created with other relevant data in one table and then all the correspondence is in another table with the text from the sales force. The Sales people are use to using the Lead number to track the lead which is why the GUID is a bit(s) much.

     

    Regards,

    Mike

     

    Thursday, May 24, 2007 8:54 PM
  •  

    Well, I acknowledge that auto-increment column would be convenient in this case (and many other cases). However, auto increment columns are hard to synchronize. The way SQL Server Merge replication solved this problem was by assigning ranges to every client. For example, client A, gets range from 1-1000; client B, from 1001-2000 ...etc. When a client consumes its range, it goes back to the server to request anther range and so on. Sync services allow you to specify a range when the schema is first created. But there is no support for requesting new range. It is rather management intense model for an API.
     
    What I’d recommend is to generate the lean number in such away that makes it unique. For example, each sales person has a unique alias (i.e. email) that could be appended with a number that is auto-incremented for this sales person only. So if I created a sales lead, it would look like 1-rafik, for the 10th lead i create, the key would be 10-rafik. Similar formulas for uniqueness can be developed such that it is unique, easy to remember and synchronize-able.
     
    Hope that helps
     
    Thanks

    Monday, May 28, 2007 1:01 AM
  • Hi Rafik,

     

    Users can login from anywhere so my software could be installed at the office and home, Laptop... I would need ranges per install not only per user which is why I started looking at a YYMMDDHHMMSS+USERID, but that is a large number even if I pack all the bits together and convert to base36.

     

    Testing with -1 as the autoinc value worked great, I wish there was a way you could support it. Is there anyway you could look for -1 Identity column and then update the local value during the sync process with the server assigned value?

     

    My other option is to hold the newer offline records in a second database or XML store while offline and then post them outside of the sync framework directly to the Webservice. I could then use a download sync to update the main database so in theory I would post -1, -2, -3 and then I would sync and would pull down 1001,1002,1003. This way I don’t have to deal with the PK\Identity change that occurs.

     

     

    Mike

     

     

     

    Monday, May 28, 2007 12:49 PM
  • Hi,
    I am looking at sync framework to sync my data. I have few tables that using auti-incr number as well as PK. I choose it is for normalization reason. These tables will only be updated at one site at this moment. Can sync framework support it?

    Thank you
    Tuesday, November 13, 2007 2:06 AM
  • Hi there,

     

    Well, if you make changes to the data at a one node only then you can use sync services to sync the data across. On the node that make changes you could use identity columns (auto-increment columns). On all other nodes, you will need to use int or bigint column.

     

    Thanks

     

    Tuesday, November 13, 2007 4:18 AM