SQL Remote User's Guide
SQL Remote Design for Adaptive Server Anywhere
Sharing rows among several subscriptions
The Policy database illustrates why and how to partition tables when there is a many-to-many relationship in the database.
Here is a simple database that illustrates the problem.
Each sales representative sells to several customers, and some customers deal with more than one sales representative. In this case, the relationship between Customer and SalesRep is thus a many-to-many relationship.
The three tables are described in more detail as follows:
Table | Description |
---|---|
SalesRep |
All sales representatives that work for the company. The SalesRep table has the following columns:
CREATE TABLE SalesRep ( Rep_key CHAR(12) NOT NULL, Name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) ); |
Customer |
All customers that do business with the company. The Customer table includes the following columns:
CREATE TABLE Customer ( Cust_key CHAR(12) NOT NULL, Name CHAR(40) NOT NULL, PRIMARY KEY (cust_key) ); |
Policy |
A three-column table that maintains the many-to-many relationship between customers and sales representatives. The Policy table has the following columns:
CREATE TABLE Policy ( policy_key CHAR(12) NOT NULL, cust_key CHAR(12) NOT NULL, rep_key CHAR(12) NOT NULL, FOREIGN KEY ( cust_key ) REFERENCES Customer ( cust_key ) FOREIGN KEY ( rep_key ) REFERENCES SalesRep (rep_key ), PRIMARY KEY ( policy_key ) ); |
The goals of the replication design are to provide each sales representative with the following information:
The entire SalesRep table.
Those rows from the Policy table that include sales relationships involving the sales rep subscribed to the data.
Those rows from the Customer table listing customers that deal with the sales rep subscribed to the data.
The many-to-many relationship between customers and sales representatives introduces new challenges in maintaining a proper sharing of information:
We have a table (in this case the Customer table) that has no reference to the sales representative value that is used in the subscriptions to partition the data.
Again, this problem is addressed by using a subquery in the publication.
Each row in the Customer table may be related to many rows in the SalesRep table, and shared with many sales representatives databases.
Put another way, the rows of the Contact table in Partitioning tables that do not contain the subscription expression were partitioned into disjoint sets by the publication. In the present example there are overlapping subscriptions.
To meet the replication goals we again need one publication and a set of subscriptions. In this case, we use two triggers to handle the transfer of customers from one sales representative to another.