Data partitioning is disjoint when the remote databases do not share data. For example, each sales representative has their own set of customers and they do not share customers with other sales representatives.
In the following example, three tables store information about the interactions between sales representatives and customers: Customers, Contacts, and SalesReps. Each sales representative sells to several customers. For some customers, there is a single contact, and for other customers there are multiple contacts.
The following table describes the Customers, Contacts, and SalesReps database tables as described in Using disjoint data partitions.
Table | Description | Table definition | ||
---|---|---|---|---|
Contacts |
All individual contacts that do business with the company. Each contact belongs to a single customer. The Contacts table includes the following columns:
|
|
||
Customers |
All customers that do business with the company. The Customers table includes the following columns:
|
|
||
SalesReps |
All sales representatives that work for the company. The SalesReps table includes the following columns:
|
|
A sales representative must subscribe to a publication that provides the following information:
A list of the all the sales representatives working for the company The following statement creates a publication that publishes the entire SalesRep table:
CREATE PUBLICATION SalesRepData ( Table SalesReps ...) ); |
A list of customers assigned to them This information is available in the Customers table. The following statement creates a publication that publishes the Customers table, which contains the rows that match the value of the rep_key column in the Customers table:
CREATE PUBLICATION SalesRepData ( TABLE Customers SUBSCRIBE BY rep_key ... ); |
A list of the contact information for their assigned customers This information is available in the Contacts table. The Contacts table must be partitioned among the sales representatives, but there is no reference to the rep_key value in the SalesRep table. To solve this problem, you can use a subquery in the Contacts article that references the rep_key column of the Customers table.
The following statement creates a publication that publishes the Contacts table, which contains the rows that reference the rep_key column of the Customers table.
CREATE PUBLICATION SalesRepData ( ... TABLE Contacts SUBSCRIBE BY (SELECT rep_key FROM Customers WHERE Contacts.cust_key = Customers.cust_key ) ); |
One row in the Customers table has the cust_key value in the current row of the Contacts table; the WHERE clause in the SUBSCRIBE BY statement ensures that the subquery only returns a single value.
The following statement creates the complete publication:
CREATE PUBLICATION SalesRepData ( TABLE SalesReps TABLE Customers SUBSCRIBE BY rep_key TABLE Contacts SUBSCRIBE BY (SELECT rep_key FROM Customers WHERE Contacts.cust_key = Customers.cust_key ) ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |