Database Synchronization PK, FK, GUID RRS feed

  • Question

  • Hi,

    I work on a new project and it uses C#.

    I have multiple clients and a server.

    I have sql server 2014 Database on remote server and i have multiple clients works with LocalDB mdf and i'm searching for the best solution to implement Microsoft Sync Framework between the clients and the server with avoiding Duplicate and similar issues.

    I intend to use GUIDs cause i found its genius but my main problem is in PK and FK relationships.

    As an example for old database design

    1- Table Customers

    [id] which is PK and identity

    2- Table Orders

    [id] which PK and identity

    [customerID] FK to table Customers

    How can i solve this issue and Re-Design tables to allow sync and not lose the benefits of using PK and FK in searching, indexing etc...

    I really appreciate any help here

    • Edited by Mr.eslam Friday, October 23, 2015 3:31 AM Edit Question
    Friday, October 23, 2015 12:48 AM

All replies

  • Hi,

    A PK column with Identity is not a good candidate for synchronization, it is prone to duplication. Likewise if you want to use a Guid, you will lose the benefits of using PK and FK, as you have said. This is a problem actually, so you have no other option but to redesign your tables. One of the best option is to change the data type of your PK column (Id) from int to varchar of your Customers and Orders table, make some necessary adjustments to another tables that have references (FK Reference) to the Customers and Orders table, and leave the data as is, because I'm sure you will not loosing any data for it. Create a code generator that generates numbers or code from the stored procedure, then create a unique device code per server or client (ex, location code, store code, etc.). So the format of your Id = device code + autogenerated number. For example, at the Main server I can use M as my location code, and the generated code is 1, so the value of Id = M1. Likewise at locationA, A as location code, then the value of Id = A1, same for location B Id = B1, Location C Id = C1, and so on and so forth. So from this method you are sure that the Id is unique for every locations.

    Please see this nice article by June T. about Sync Framework,


    Hope it helps.

    Saturday, October 24, 2015 1:33 AM