locked
Handling synchronization where PK is used for normalization and unique constraint columns exist RRS feed

  • Question

  • I'm struggling to understand sync services and how I should deal with primary and foreign keys in a normalized database.  Can someone point me to additional samples, documentation, or explain?  I've tried to explain my issue below and then have several questions at the bottom.

     

    I was planning on using auto incrementing integers as the primary keys on my tables (the server side database already exists and uses autoincrementing integers).  The insert/update/delete commands would have to store data based on the unique constraint columns in each table, not on the primary key.  After an insert, the primary key would have to be populated in SQLce based on the identity value created in the server database (and then in the dataset also).  Conflicts would be defined as more than 1 client updating data with equal unique constraints.  So if two clients created a row with the same unique constraint columns then there would be a conflict even though the two client rows had different PKs in their local SQLce database.  After both clients synced and resolved the conflict, then the two client side databases should end up with the same primary key for that row.

     

    I'm using VS2008 beta 2 and SQLce 3.5 in a windows form application on the client side.  SQL 2005 is the server database.  I need to handle bidirectional sync.  I've run a few samples and built my own sample in VS2008 using the wizards provided, but none of the samples that I have seem to show how to handle a normalized database where the PK is only used as a unique identifier and in PK-FK relationships, and other columns (multiple columns) define unique constraints.

     

    Rafik's article in his blog mentions two ways that a unique identifier is used.  Rafik goes on to explain the first option, but does not really explain the second option which is more of what I'm after.

     

    http://blogs.msdn.com/synchronizer/archive/2007/06/28/stay-away-from-auto-increment-columns.aspx

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1643465&SiteID=1

     

    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.

     

    1. After the VS2008 wizard builds the .sync file, how do you go about building custom insert/update/delete commands that use the unique constraint columns and handle the primary key correctly?  Is creating custom sql commands the correct approach in dealing with the unique constraint columns?

     

    2. The VS2008 wizard sets up the SQLce database tables to use an identity seed of 0, increment of 1.  The dataset is setup to use an identity seed of -1, increment of -1.  The server database uses a seed of 1, increment of 1.  If I create a new row in the dataset, perform an UpdateAll() to save the row to SQLce, then SQLce auto creates an identity value.  When Sync() is ran (using the wizard built syncadapter commands), then the PK that SQLce created is saved in the server database.  I was thinking that the server database would create a new PK for the row, then SQLce would save the new identity value that was generated on the server in both the SQLce client side database and in the dataset, but that does not seem to be the case by default.

     

    3. What about cascading updates and deletes?  Should they/can they exist and where?  On the server, in SQLce, or in the ado.net dataset?  Our existing server database does use cascading deletes to remove child rows whenever the parent row is deleted by an application.

     

    4. Is it best practice to use a timestamp as the sync anchor instead of datetime?  The wizard defaults to datetime, but there are several blogs mentioning the use of @@DBTS as the anchor.  If I choose timestamp as the datatype, then does the autogenerated code use @@DBTS?

     

    5. I'm having a lot of trouble finding where the parameters to the insert/update/delete commands are documented.  The sync services books online doesn't show required parameters for the commands such as SyncAdapter.InsertCommand.

     

    Regards,

    James

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 9:54 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Saturday, November 3, 2007 8:02 PM