Using disjoint data partitions

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 Contacts table has a foreign key to the Customers table. The Customers table has a foreign key to the SalesReps table.

Description of Contacts, Customers, and SalesReps tables

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:

contact_key An identifier for each contact. This is the primary key.
name The name of each contact.
cust_key An identifier for the customer to which the contact belongs. This is a foreign key to the Customers table.

CREATE TABLE Contacts (
   contact_key  CHAR(12) NOT NULL,
   name  CHAR(40) NOT NULL,
   cust_key  CHAR(12) NOT NULL,
   FOREIGN KEY  REFERENCES Customers,
   PRIMARY KEY  (contact_key)
);

Customers

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

cust_key An identifier for each customer. This is the primary key.
name The name of each customer.
rep_key An identifier for the sales representative in a sales relationship. This is a foreign key to the SalesReps table.

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

SalesReps

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

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

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

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

Using BEFORE UPDATE triggers

Discuss this page in DocCommentXchange.