Sybase DC38133-01-0902-01 manual Sharing rows among several subscriptions, Policy example

Sharing rows among several subscriptions

There are cases where a row may need to be included in several subscriptions. For example, we may have a many-to-many relationship. In this section, we use a case study to illustrate how to handle this situation.

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.

Customer

Policy

SalesRep

cust_key

policy_key

rep_key

name

cust_key

name

			rep_key
	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	The three tables are described in more detail as follows:
database
	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)
		);

112