Chapter 8. SQL Remote Design forAdaptive Ser verEnter prise
Sharing rows among several subscriptions
Thereare cases where a row may need to be included in several
subscriptions. For example, if instead of the many-to-one relationship
betweencustomers and sales representatives that we had above, we may
havea many-to-many relationship.

The Policy example

ThePolicy database illustrates why and how to partition tables when there is
amany-to-many relationship in the database.
Exampledatabase Hereis a simple database that illustrates the problem.
Policy
policy_key
cust_key
rep_key
SalesRep
rep_key
name
Customer
cust_key
name
ThePolicy table has a row for each of a set of policies. Eachpolicy is drawn
upfor a customer by a particular sales representative. Thereis a
many-to-manyrelationship between customers and sales representatives, and
theremay be several policies drawn up between a particular rep/customer
pair.
Anyrow in the Customer table may need to be shared with none, one, or
severalsales representatives.
Solving the problem
Tosupport this case, you need to write triggers to build a comma-delimited
listof values to store in a redundant subscription-list column of the
Customertable, and include this column as the subscription column when
addingthe Customer table to the publication. Therow is shared with any
subscriptionfor which the subscription value matches any of the values in
thesubscription-list column.
Thedatabase, with the subscription-list column included, is as follows:
157