Database design and sync framework RRS feed

  • Question

  • I hope this is the proper forum for this question but this is more of a question of design than implementation. I currently have an application that uses an database to track work orders there are two tables Pending and Completed.  The Pending table contains a Status column that records whether the work order has been completed, there are several statuses for completed, or if its still pending. Pending work orders are sent out to several technicians, sometimes multiple technicians receive the same work order, at the end of the day all completed work orders are stored in the Completed table and the statuses of all pending work orders are updated in the Pending table. Which means all the pending work orders that have been completed must have their statuses in the Pending table reflect there current complete or pending status. To add one more problem there is a hierarchy to the various completed statuses so lower ranked statuses cannot overwrite higher ranked ones.

    I have in place a system that currently takes in each completed work order one at a time and makes all the appropriate changes. It works extremely well but the code is bulky, slow, and extremely accurate. I've been working with the sync framework but I'm having trouble with what so far to me seems an all or nothing sync process.

    At this point the only thing I can think of is to create a third table in which to dump all work into before going through each record, making my changes and then clearing the temp table.

    Any ideas or advice on this would be appreciated. Thank you

    Monday, July 19, 2010 9:10 AM

All replies

  • Hi BrownRR,

    If I understand your question correctly, you want to make changes to the status column in the Pending table based on some data in the Completed table. It seems like you want to do business logic and your problem is less related to sync. Also, is the schema of the Pending and Completed tables the same?

    Monday, July 19, 2010 11:56 PM
  • To answer your question first the schema is different but they share one data point that being the status. To make it clearer there are 4 possible states a work order can have (that state represented by its status), Pending (the first and lowest state), Attempted (the work order was attempted but not completed for some reason), Returned (the work order can never be completed for some reason), Completed (The work order is done this is the highest state). What can happen is that a work order can be Attempted one day so the Pending table status must show it was attempted and then if its completed the next day the Pending table status must show its now completed. A big issue arrives when technicians have the same work orders assigned to them (its part of the work strategy and can't be changed) and one completes the work and  one marks it as Attempted or Returned. If the the Completed work order is downloaded first I have to make sure that the Pending table status can not be over written by an Attempted or Returned status. Hope that explains things clearly.


    As far as the business logic do you any have any examples or links you could share to illustrate the point. Thank you

    Tuesday, July 20, 2010 12:17 AM
  • if a work order is updated by more than one client, Sync Fx will raise a conflict for it. You can subscribe to the ApplyChangeFailed event to examine the conflicting rows' status and resolve the conflict manually by applying the row with the higher status based on your status hierarchy.
    • Proposed as answer by Ganeshan Tuesday, July 20, 2010 10:20 PM
    Tuesday, July 20, 2010 1:33 AM
  • JuneT is correct in mentioning how you can plug in your custom business logic. If both the tables have the same key column, you should be able to use sync framework to provision just the status column and the primary key and then hook to the ApplyChangeFailed event to plug in your business logic.
    Tuesday, July 20, 2010 10:18 PM