This tutorial assumes that the pubs2 sample schema is installed on an Adaptive Server Enterprise server. The Adaptive Server Enterprise server may be installed locally on your computer or accessed remotely using Sybase Open Client.
The pubs2 sample schema is used as the consolidated database schema. It contains information about stores, titles, authors, publishers, and sales. The following table provides a description of each table in the Adaptive Server Enterprise database:
Table | Description |
---|---|
au_pix | Pictures of the authors. |
authors | The authors of the various TITLES in the system. |
discounts | Records of various discounts at particular STORES. |
sales | Each sale record is one sale made by a particular store. |
salesdetail | Contains information about the different TITLES that were included in a particular sale. |
stores | Each store record is one store or branch office in the system. |
titleauthor | Contains information about which TITLES were written by which AUTHORS. |
titles | Records of all the different books in the system. |
blurbs, publishers, and roysched | Contains information that is not needed in this demonstration. |
It is unnecessary and inefficient for each store to have a copy of the entire consolidated database. The remote schema uses the same table names, but only contains information relevant to one particular store. To achieve this, the remote schema is designed as a subset of the consolidated database in the following way:
Consolidated table | Remote table |
---|---|
au_pix | Includes all rows. |
authors | Includes all rows. |
discounts | Filter by stor_id. |
sales | Filter by stor_id. |
salesdetail | Filter by stor_id. |
stores | Filter by stor_id. |
titleauthor | Includes all rows. |
titles | Includes all rows. |
blurbs | Not included on remote. |
publishers | Not included on remote. |
roysched | Not included on remote. |
Each store needs to keep records of all titles and authors so customers can search their inventory. However, a bookstore does not need information about publishers or royalties, so this information is not synchronized to each store. Each store needs information about sales and discounts, but not about sales and discounts related to other stores. This is achieved by filtering rows based on a store identifier.
You can also take a subset of columns from a table if certain columns are not required on the remote databases.
The next step is to choose the synchronization direction of each table. You should consider what information a remote database needs to read and what information a remote database needs to create, change, or remove. In this example, a bookstore needs access to the list of authors and titles, but never enters a new author into the system. This places a restriction that authors and titles must always enter the system from the consolidated database at headquarters. However, a bookstore needs to be able to record new sales on a regular basis. These factors lead to the following synchronization directions for the tables:
Table | Synchronization |
---|---|
titleauthor | Download to remote only. |
authors | Download to remote only. |
au_pix | Download to remote only. |
titles | Download to remote only. |
stores | Download to remote only. |
discounts | Download to remote only. |
sales | Download and upload. |
salesdetail | Download and upload. |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |