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?
Thanks,
Dan Griffin
