Sybase DC38133-01-0902-01 manual Sharing rows among several subscriptions, Solving the problem

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.

Customer

Policy

SalesRep

cust_key

policy_key

rep_key

name

cust_key

name

rep_key

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