Contents Index Sharing rows among several subscriptions The publication

SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Anywhere
    Sharing rows among several subscriptions

The Policy example


The Policy database illustrates why and how to partition tables when there is a many-to-many relationship in the database.

Example 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 tables in the database 

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:
  • rep_key    An identifier for each sales representative. This is the primary key.

  • name    The name of each sales representative.

The SQL statement creating this table is as follows:
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:
  • cust_key    A primary key column containing an identifier for each customer

  • name    A column containing the name of each customer

The SQL statement creating this table is as follows:
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:
  • policy_key    A primary key column containing an identifier for the sales relationship.

  • cust_key    A column containing an identifier for the customer representative in a sales relationship.

  • rep_key    A column containing an identifier for the sales representative in a sales relationship.

The SQL statement creating this table is as follows.
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 )
);
Replication goals 

The goals of the replication design are to provide each sales representative with the following information:

New problems 

The many-to-many relationship between customers and sales representatives introduces new challenges in maintaining a proper sharing of information:

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.


The publication

Contents Index Sharing rows among several subscriptions The publication