Can Data Partitions Be Used With Associative Tables?

First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database
schema which contains associative tables for many-to-many relationships.

We have two tables, a User table
and an Audit table. A user can be
assigned more than one Audit. An Audit
can be assigned to more than one User.
So an AuditUser associative table exists. If data partitions are used based on User,
then any Audits that are assigned to one or more users should be copied to the
proper partition for each User (the msmerge_current_partition_mappings table
with the proper partition_id values).



In order to insert records with such a schema, the following
steps occur in order:

Insert
new row into Audit table with new rowguidInsert
entry into AuditUser table associating the auditguid with every userguid that
is assigned this audit.



Merge replication triggers are fired on insert of the Audit
row and another one for the insert of the AuditUser row.



When the Audit row is inserted, the replication trigger follows
the following logic:

Inserts
a copy of that row into the msmerge_contents table. Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). To do this, it checks to see if the
AuditGuid is referenced in one or more AuditUser rows. Since we haven€™t inserted the AuditUser
row at this point, the trigger€™s logic doesn€™t find a partition to copy
this row to.



When the AuditUser row is inserted, the replication trigger performs
the same logic as with the Audit row, it:

Inserts
a copy of that row into the msmerge_contents table.Evaluates
the row to determine which partition(s) this row should be copied to as
well (msmerge_current_partition_mappings table). Since the row meets the criteria for one
or more partitions, it is copied to the msmerge_current_partition_mappings
table for each partition that exists.



When replication occurs, we see only the AuditUser rows
copied down to our device, and not the corresponding Audit rows. Now that we understand the triggers, it is
plain to see why. If the AuditUser row
could be inserted first, then the trigger on the Audit row would copy that row
into the proper partitions and all would work well. However, the Audit row must be inserted
first, so that foreign key relationship constraints are preserved.



It seems that the Update trigger on the AuditUser row
actually walks the relationships and copies any related child rows to the
msmerge_current_partition_mappings table.