Using overlap partitions

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 Policy table has foreign keys to both the Customers and SalesReps tables.

Description of Customers, Policy, and SalesReps tables

The following table describes Customers, Policy, and SalesReps database tables as discussed in Using overlap partitions.

Table

Description

Customers

All customers that do business with the company. The Customers table has the following columns:

cust_key A primary key column containing an identifier for each customer.
name A column containing the name of each customer.

The following statements create this table:

CREATE TABLE Customers (
   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:

policy_key A primary key column containing an identifier for the sales relationship.
cust_key A column containing a foreign key for the customer representative in a sales relationship.
rep_key A column containing a foreign key for the sales representative in a sales relationship.

The following statements create this table:

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 Customers ( cust_key )
   FOREIGN KEY ( rep_key )
   REFERENCES SalesReps (rep_key ),
   PRIMARY KEY ( policy_key )
);

SalesReps

All sales representatives that work for the company. The SalesReps table has the following columns:

rep_key An identifier for each sales representative. This is the primary key.
name The name of each sales representative.

The following statements create this table:

CREATE TABLE SalesReps (
   rep_key CHAR(12) NOT NULL,
   name CHAR(40) NOT NULL,
   PRIMARY KEY (rep_key)
);

Partitioning data

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
   )
);

Multiple-valued subqueries in publications

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.

Maintaining referential integrity when reassigning rows among subscribers
Using the subscribe_by_remote option with many-to-many relationships

Discuss this page in DocCommentXchange.