Adding a subscription-list column to the Contact table

Partitioning the Customer table in the Contact example

Maintaining the subscription-list column

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

Adding a subscription-list column to the Contact table

The Contact table must also be partitioned among the sales representatives, but contains no reference to the sales representative rep_key value.

Add a subscription-list column

To solve this problem in Adaptive Server Enterprise, you must add a column to the Contact table containing a comma-separated list of subscription values to the row. ( In the present case, there can only be a single subscription value.) The column can be maintained using triggers, so that applications against the database are unaffected by the presence of the column. We call this column a subscription-list column.

When a row in the Customer table is inserted, updated or deleted, a trigger updates rows in the Contact table. In particular, the trigger updates the subscription-list column. As the Contact table is marked for replication, the before and after image of the row is recorded in the log.

Log entries are values, not subscribers

Although in this case the values entered correspond to subscribers, it is not a list of subscribers that is entered in the log. The server handles only information about publications, and the Message Agent handles all information about subscribers. The values entered in the log are for comparison to the subscription value in each subscription. For example, if rows of a table were divided among sales representatives by state or province, the state or province value would be entered in the transaction log.

A subscription-list column is a column added to a table for the sole purpose of holding a comma-separated list of subscribers. In the present case, there can only be a single subscriber to each row of the Contact table, and so the subscription-list column holds only a single value.

For a discussion of the case where the subscription-list column can hold many values, see Sharing rows among several subscriptions.

Contact table definition

In the case of the Contact table, the table definition would be changed to the following:

CREATE TABLE Contact (
   contact_key CHAR( 12 ) NOT NULL,
   name CHAR( 40 ) NOT NULL,
   cust_key CHAR( 12 ) NOT NULL,
   subscription_list CHAR( 12 ) NULL,
   FOREIGN KEY ( cust_key )
   REFERENCES Customer ( cust_key ),
   PRIMARY KEY ( contact_key )
)
go

The additional column is created allowing NULL, so that existing applications can continue to work against the database without change.

The subscription_list column holds the rep_key value corresponding to the row with primary key value cust_key in the Customer table. A set of triggers handles maintenance of the subscription_list column.

For an Adaptive Server Anywhere consolidated database, the solution is different. For more information, see Partitioning tables that do not contain the subscription expression.