We have a data API that has to publish data to an external system and keep the data up to date.
The external system needs aggregate/summary data from us. We have ROWVERSION columns on all tables involved, but we need to create a view to publish the summary data.
Here is the core of the problem:
The parent table contains a ROWVERSION, lets call it Order. The child table contains a ROWVERSION lets call it OrderItem. The VIEW needs to publish Summary data for the Parent (Order) which includes columns such as TotalAmount (Sum of OrderItem.Amounts for the Order) and TotalItems (Count of OrderItems for the Order).
Now if we publish the ROWVERSION of the parent table then changes on the child table(s) will not be reflected in the ROWVERSION.
So if we add or update OrderItems, the Order ROWVERSION will not change but the OrderItem ROWVERSION will.
The External System needs to know that something changed on the Order, but it cannot see the OrderItem table, and therefore can only look at a ROWVERSION on the VIEW.
This cannot be a new problem and I'm sure there is a best practice design/solution to this problem.
I considered constructing a byte[] as ROWVERSION on the VIEW consisting of the sum of all of the other ROWVERSIONS of the Parent and Child table records. I'm unsure if this will work or what the possible issues could be. (Running out of number space as the rows increase for one...)
Any suggestions are welcome.
