Syncing related tables with sync framework

Question

Current situation:

I have a database with following structure:

• Product
  • Category (contains column product_id)
    • Item (contains column category_id)
      • Rating (contains column item_id)
      • Comments (contains column item_id)
      • Creator (contains column item_id)

The database runs on a ms sql 2008 server. I have several clients (with sync framework) at different locations which sync with the server to their local compact sql file.

My question/problem:
I have set-up a filterParameter (with template) on the server so that only products with a certain id are synced. All goes well for the Product table, but for the other tables not.
For example all categories are loaded, all items are loaded, etc. I would only like categories to be synced that have that certain product_id, only items that have that category_id and so on...

Normally I would just join them together, but I don't know how to do that in sync framework, since there isn't a global sql procedure to select all changes. But they're all sperate stored procedures. How would I best implement this? I will provide code or a db sample if needed.

Greets Daan & thanks in advance!

Btw, I've read this post ( Syncing related tables ), but it can't apply the method described since I have more than 3 levels in my database. Or am I wrong?

Answer 1

The best case might be to change your schema, by adding the product_id field to all the tables in the database. That would greatly simplify the filter statements, and improve performance for sync operations.

If you don't want to change your database schema, you can use sub-queries in your filter criteria as shown below. Also, take a look at this discussion which addresses this topic: http://social.microsoft.com/Forums/en-US/syncdevdiscussions/thread/622b37ac-4229-4d34-b093-fbae0731508b.

Filter for Product and Category tables (both tables need to have "product_id" set as a filter column):

@"[side].product_id = @ProductId"

Filter for Item table:

@"[side].item_id IN (
    SELECT i.item_id 
    FROM Item i 
    INNER JOIN Category c ON i.category_id = c.category_id
    WHERE c.product_id = @ProductId)"

Filter for Rating table (other tables at this level will be similar):

@"[side].rating_id IN (
    SELECT r.rating_id
    FROM Rating r
    INNER JOIN Item i ON r.item_id = i.item_id
    INNER JOIN Category c ON i.category_id = c.category_id
    WHERE c.product_id = @ProductId)"