Sharing rows among several subscriptionsThereare cases where a row may need to be included in several
subscriptions. For example, we may havea many-to-manyrelationship. In
thissection, we use a case study to illustrate how to handle this situation.
The Policy example
ThePolicy database illustrates why and how to partition tables when there is
amany-to-many relationship in the database.
Exampledatabase Hereis a simple database that illustrates the problem.
Policy
policy_key
cust_key
rep_key
SalesRep
rep_key
name
Customer
cust_key
name
Eachsales representative sells to several customers, and some customers
dealwith more than one sales representative. In this case, the relationship
betweenCustomer and SalesRep is thus a many-to-many relationship.
Thetables in the
database Thethree tables are described in more detail as follows:
Table Description
SalesRep All sales representativesthat work for the company. The
SalesReptable has the following columns:
♦rep_key An identifier for each sales representative.
Thisis the primary key.
♦name Thename of each sales representative.
TheSQL 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