iAnywhere.com - The Policy example

Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Remote » SQL Remote Installation Design » 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.

The Policy table has foreign keys to both the Customers and SalesReps tables.

Each sales representative sells to several customers, and some customers deal with more than one sales representative. In this case, the relationship between Customers and SalesReps is thus a many-to-many relationship.

The tables in the database

The three tables are described in more detail as follows:

Table	Description
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 SQL statement creating this table is as follows: CREATE TABLE SalesReps ( Rep_key CHAR(12) NOT NULL, Name CHAR(40) NOT NULL, PRIMARY KEY (rep_key) );
Customers	All customers that do business with the company. The Customers 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 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 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 Customers ( cust_key ) FOREIGN KEY ( rep_key ) REFERENCES SalesReps (rep_key ), PRIMARY KEY ( policy_key ) );

Table

Description

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 SQL statement creating this table is as follows:

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

Customers

All customers that do business with the company. The Customers 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 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 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 Customers ( cust_key )
   FOREIGN KEY ( rep_key )
   REFERENCES SalesReps (rep_key ),
   PRIMARY KEY ( policy_key )
);

Replication goals

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

The entire SalesReps table.
Those rows from the Policy table that include sales relationships involving the sales rep subscribed to the data.
Those rows from the Customers table listing customers that deal with the sales rep subscribed to the data.

New problems

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

There is a table (in this case the Customers table) that has no reference to the sales representative value that is used in the subscriptions to partition the data.

Again, this problem is 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.

Put another way, the rows of the Contacts table in Partitioning tables that do not contain the subscription expression were partitioned into disjoint sets by the publication. In the present example there are overlapping subscriptions.

To meet the replication goals, you need one publication and a set of subscriptions. In this case, you can use two triggers to handle the transfer of customers from one sales representative to another.

The publication