Deleting records when using filtered scope based on parent/child relationship RRS feed

  • Question

  • Hi, I'm looking for some guidance.

    We have 2 tables, Order and OrderDetail that look like this:

    Order: {OrderId, CustomerId, ..}
    OrderDetail: {OrderDetailId, OrderId, ...}

    If we define a filtered scope based on the CustomerId, then filtering Order is straighforward (using a filter template):

    [side].[CustomerId] = @customerId

    as is filtering the OrderDetail table:

    [side].[OrderId] IN (SELECT OrderId FROM Order WHERE CustomerId = @customerId)

    The problem comes in when we delete an Order and then someone tries to sync.
    While both deletions will be tracked as changes, the OrderDetail deletion is no longer in scope because the Order row that it references has been deleted.

    I can think of 1 obvious ways to deal with this:

    put the filter parameter in every table that I want constrained by a filter. EG, add CustomerId to the OrderDetails table. I do not like this for several reasons.
     - It denormalizes data
     - I don't think that our schema should have to change to make the sync framework work

    Another option is to disallow deletions and to use instead something like soft-deletes with an active flag instead of actually deleting from the db. I don't like this limitation either.
    Is there a best practice here?  


    Dan Griffin


    Wednesday, November 10, 2010 8:04 PM

All replies

  • I think you have laid out the two of the suggestions I would have made. Adding the filter parameter to every table is the one I see implemented most often.




    Wednesday, November 10, 2010 9:12 PM
  • try this:

    On the Order table, add the OrderId as a filter column. but dont put it in the FilterClause, this would at least put the OrderId in the Order table's tracking table.


    On the OrderDetail, try this FilterClause: [side].[OrderId] IN (SELECT OrderId FROM Order_Tracking WHERE CustomerId = @customerId)


    havent tested it myself, but it might work.



    Thursday, November 11, 2010 2:46 AM
  • Ah - interesting idea. I will definitely give this a try. OrderId shouldn't need to be added as a filter column though should it, since it would be the primary key of the table...? I already include OrderId as a filter column in the OrderDetail table.
    Thursday, November 11, 2010 6:57 PM