locked
Syncing reference data and transactional data - handling foreign keys? RRS feed

  • Question

  •  

    Here is my scenario (loosely based on Rafik's conflict handling demo):

     

    order_types is a "reference table" -> only updated on the server. Provides id, code, description. ID is an Identity column.

    orders contains a column called order_type_id, FK relationship on the server to the order_types table. This table would sync bidirectionally.

     

    I would sometimes want to sync the reference data only (download only or snapshot).

     

    1. Must I also sync the order_types table when I am syncing the orders table?

    2. When/how is the best way to create the FK on the client?

     

    I originally created 2 sync groups, one w/orders & order details (same as in demo), and a separate sync group & agent (initiated independently by the user) for order_types. This worked fine until I wanted to add the FK constraint on the client. I attempted to add it in the CreatingSchema event when orders is created, but because the order_types table doesn't exist in the sync group, that was clearly not right.

     

    What is the recommended approach to get at the scenario I am describing?

     

    Thanks!

    • Moved by Max Wang_1983 Friday, April 22, 2011 9:21 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, December 4, 2007 10:35 PM

Answers

  • Hi Stephanie,

     

    Well, it does not hurt to sync order_types table each time. Since the data does not change that often, you won't not get a lot of changes with each sync. Unless you have a strong reason not to synchronize it with the rest of the table, you should do so.

     

    The recommended way to create FK constraint on the client is by using the SchemaCreated event which is fired directly after each table is created.

     

    Thanks

    Wednesday, December 5, 2007 3:48 AM
  • Hi,

     

    1- Depends on the scenario, but events could be used for creating the schema. The SchemaCreated event is fired after the table is created thus giving you the option to add index to it ..etc.

     

    2- You should add the tables to the SyncTables collection in the proper order. More importantly, you should also order the SyncAdapter collections on the server provider properly; the order is very important for sync to function correctly.

     

    Thanks

     

    Wednesday, December 12, 2007 6:20 PM

All replies

  • Hi Stephanie,

     

    Well, it does not hurt to sync order_types table each time. Since the data does not change that often, you won't not get a lot of changes with each sync. Unless you have a strong reason not to synchronize it with the rest of the table, you should do so.

     

    The recommended way to create FK constraint on the client is by using the SchemaCreated event which is fired directly after each table is created.

     

    Thanks

    Wednesday, December 5, 2007 3:48 AM
  •  

    Thanks a lot - that makes sense (my computer went on a brief hiatus, hence the delay in my reply).

     

    Two clarifications:

     

    1. You mention that it is recommended to create foreign key relationships the SchemaCreated event, but the sample I saw used the CreatingSchema event. Is there some guidance as to when to use each event? (Purely an academic question, as you will see below). The documentation I am referring to is the BOL topic "How to: Initialize the Client Database and Work with Table Schema". It shows creating a foreign key in the CreatingSchema event handler.

     

    2. Based on your feedback, I think what I really wanted to do was to create the client's schema independently of the sync (and use the UseExistingOrFail table creation option). This way I could create the schema that has orders and order_types with the FK when the client db is created, and two SyncAgents. One SyncAgent manages only the reference data, and one does the reference + transactional. This seems to work well. What I am not sure about is if the syncAgent.Configuration.SyncTables.Add method guarantees order. Right now, I am not creating ANY relationships in the Sync Schema and it works well if I add the tables to the SyncTables collection in the proper order. Is that the correct way to do this?

     

    Thanks again!!!

    Monday, December 10, 2007 8:43 PM
  • Hi,

     

    1- Depends on the scenario, but events could be used for creating the schema. The SchemaCreated event is fired after the table is created thus giving you the option to add index to it ..etc.

     

    2- You should add the tables to the SyncTables collection in the proper order. More importantly, you should also order the SyncAdapter collections on the server provider properly; the order is very important for sync to function correctly.

     

    Thanks

     

    Wednesday, December 12, 2007 6:20 PM