User's Manual

Chapter 8. SQL Remote Design for Adaptive Server Enterprise
Sharing rows among several subscriptions
There are cases where a row may need to be included in several
subscriptions. For example, if instead of the many-to-one relationship
between customers and sales representatives that we had above, we may
have a many-to-many relationship.
The Policy example
The Policy database illustrates why and how to partition tables when there is
a many-to-many relationship in the database.
Example database Here is a simple database that illustrates the problem.
Policy
policy_key
cust_key
rep_key
SalesRep
rep_key
name
Customer
cust_key
name
The Policy table has a row for each of a set of policies. Each policy is drawn
up for a customer by a particular sales representative. There is a
many-to-many relationship between customers and sales representatives, and
there may be several policies drawn up between a particular rep/customer
pair.
Any row in the Customer table may need to be shared with none, one, or
several sales representatives.
Solving the problem
To support this case, you need to write triggers to build a comma-delimited
list of values to store in a redundant subscription-list column of the
Customer table, and include this column as the subscription column when
adding the Customer table to the publication. The row is shared with any
subscription for which the subscription value matches any of the values in
the subscription-list column.
The database, with the subscription-list column included, is as follows:
157