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 » Partitioning tables that do not contain the subscription expression

Partitioning tables that do not contain the subscription expression Next Page

The Contacts example

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

Example

Here is a simple database that illustrates the problem.

The Contacts table has a foreign key to the Customers table. The Customers table has a foreign key to the SalesReps table.

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

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    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.

The SQL statement creating this table is as follows:

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

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.

The SQL statement creating this table is:

CREATE TABLE Contacts (
   Contacts_key  CHAR(12) NOT NULL,
   Name  CHAR(40) NOT NULL,
   Cust_key  CHAR(12) NOT NULL,
   FOREIGN KEY  REFERENCES Customers,
   PRIMARY KEY  (contact_key)
)
Replication goals

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