Contents Index Partitioning tables that do not contain the subscription column Territory realignment in the Contact example

SQL Remote User's Guide
  SQL Remote Design for Adaptive Server Enterprise
    Partitioning tables that do not contain the subscription column

The Contact example

The Contact database illustrates why and how to partition tables that do not contain the subscription column.

Example 

Here is a simple database that illustrates the problem. We call this database the Contact database, because it contains a Contact table in addition to the two tables described earlier in this chapter.

Each sales representative sells to several customers. At some customers there is a single contact, while other customers have several contacts.

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)
)
go
Customer All customers that do business with the company. The Customer 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 SalesRep table.

The SQL statement creating this table is as follows: 
CREATE TABLE Customer (
   cust_key CHAR(12) NOT NULL,
   name CHAR(40) NOT NULL,
   rep_key CHAR(12) NOT NULL,
   FOREIGN KEY ( rep_key )
   REFERENCES SalesRep,
   PRIMARY KEY (cust_key)
)
go
Contact All individual contacts that do business with the company. Each contact belongs to a single customer. The Contact 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 Customer table.

The SQL statement creating this table is: 
CREATE TABLE Contact (
   contact_key  CHAR(12) NOT NULL,
   name  CHAR(40) NOT NULL,
   cust_key  CHAR(12) NOT NULL,
   FOREIGN KEY (cust_key)
   REFERENCES Customer,
   PRIMARY KEY  (contact_key)
)
go
Replication goals 

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

Contents Index Partitioning tables that do not contain the subscription column Territory realignment in the Contact example