We have a data synchronization scenario wherein we want to send the data from master DB to client. The clients will have only a subset of master data depending on some rules which are calculated at the server level.
Here we have cases where the tables have relation among them and there can be "n" levels of such relation. ie I can have Table-1 whose data will determine what data from Table-2 should be sent which in turn will determine data of Table-3 be sent and so on. So if there is any change in any of these tables then all tables down the hierarchy should be recalculated and synchronized.
Some limitations we have are:
a) We are currently using Pervasync(www.pervasync.com) for synchronization and modeled the queries for such cascading cases. And facing issues there like DB lock wait time out.
b) This is also limiting the usage of DB triggers as pervasync internally uses the triggers.
c) Since we have already zeroed in on pervasync, it is difficult to take a code based calculation approach wherein we identify at the code level what changes to be propagated and do data to client mapping.
Has anyone handled such cases? Any suggestions on how to approach this case will be very helpful.