Data partitioning overlaps when the remote databases share data. For example, sales representatives share customers amongst themselves.
Suppose three tables store information about the interactions between sales representatives and customers: Customers, Policy, and SalesReps. Each sales representative sells to several customers, and some customers deal with more than one sales representative. The Policy table has foreign keys to both the Customers and SalesReps tables. There is a many-to-many relationship between Customers and SalesReps.
The following table describes Customers, Policy, and SalesReps database tables as discussed in Overlap partitions.
Table | Description | ||
---|---|---|---|
Customers |
All customers that do business with the company. The Customers table has the following columns:
The following statements create this table:
|
||
Policy |
A three-column table that maintains the many-to-many relationship between customers and sales representatives. The Policy table has the following columns:
The following statements create this table:
|
||
SalesReps |
All sales representatives that work for the company. The SalesReps table has the following columns:
The following statements create this table:
|
The many-to-many relationship between customers and sales representatives introduces new challenges for sharing information properly.
Sales representatives must subscribe to a publication that provides the following information:
The entire SalesReps table There are no qualifiers to this article, so the entire SalesReps table is included in the publication.
... TABLE SalesReps, ... |
Those rows from the Policy table that include sales relationships involving the sales representative subscribed to the data This article uses a SUBSCRIBE BY subscription expression to specify a column used to partition the data among the sales representatives:
... TABLE Policy SUBSCRIBE BY rep_key, ... |
The subscription expression ensures that each sales representative receives only those rows in the table for which the value of the rep_key column matches the value provided in the subscription.
The Policy table partitioning is disjoint: there are no rows that are shared with more than one subscriber.
Those rows from the Customers table listing customers that deal with the sales representative subscribed to the data The Customers table has no reference to the sales representative value that is used in the subscriptions to partition the data. This problem can be addressed by using a subquery in the publication.
Each row in the Customers table may be related to many rows in the SalesReps table, and shared with many sales representatives' databases. That is, there are overlapping subscriptions.
A subscription expression with a subquery is used to define the partition. The article is defined as follows:
... TABLE Customers SUBSCRIBE BY ( SELECT rep_key FROM Policy WHERE Policy.cust_key = Customers.cust_key ), ... |
The Customers partitioning is non-disjoint: some rows are shared with more than one subscriber.
The following statement creates the complete publication:
CREATE PUBLICATION SalesRepData ( TABLE SalesReps, TABLE Policy SUBSCRIBE BY rep_key, TABLE Customers SUBSCRIBE BY ( SELECT rep_key FROM Policy WHERE Policy.cust_key = Customers.cust_key ) ); |
The subquery in the Customers article returns a single column (rep_key) in its result set, but may return multiple rows, corresponding to all the sales representatives that deal with the particular customer. When a subscription expression has multiple values, the row is replicated to all subscribers whose subscription matches any of the values. This ability to have multiple-valued subscription expressions allows overlapping partitioning of a table.
Referential integrity maintenance when reassigning rows among subscribers
subscribe_by_remote option with many-to-many relationships
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |