This tutorial assumes that you have installed the Order Entry (OE) and Human Relations (HR) sample schemas. The OE schema is used as the consolidated database. It encapsulates information about employees, orders, customers, and products. For this tutorial, you are primarily interested in the OE schema. However, you must refer to the EMPLOYEES table in the HR schema to get information about each individual salesperson. Here is a brief description of the relevant tables in the OE schema:
Table | Description |
---|---|
CUSTOMERS | Customers whose information is kept on record. |
INVENTORIES | How much of each product is stored in each warehouse. |
ORDER_ITEMS | List of products included in each order. |
ORDERS | Record of a sale between a salesperson and a customer on a specific date. |
PRODUCT_DESCRIPTIONS | Descriptions of each product in different languages. |
PRODUCT_INFORMATION | A record of each product in the system. |
The first step is to design a remote schema. It is unnecessary and inefficient for each salesperson to have a copy of the entire consolidated database. The remote schema is designed so that it only contains information relevant to one particular salesperson. To achieve this, the remote schema is designed in the following way:
Consolidated table | Remote table |
---|---|
CUSTOMERS | Includes all rows. |
INVENTORIES | Not included on remote. |
ORDER_ITEMS | Filter by sales_rep_id. |
ORDERS | Includes all rows. |
PRODUCT_DESCRIPTIONS | Not included on remote. |
PRODUCT_INFORMATION | Includes all rows. |
Each salesperson needs to keep records of all customers and products, so that any product can be sold to any customer. This tutorial assumes that a salesperson always speaks the same language as the customer, so you do not need the PRODUCT_DESCRIPTIONS table. Each salesperson needs information about orders, but not orders related to other salespeople. This is achieved by filtering rows based on salesperson identifier.
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 specific salesperson needs a list of products and customers, but never entered a new product into the system. You are making the restriction that products and customers always enter the system from the consolidated database at headquarters. However, a salesperson needs to be able to record new orders on a regular basis. These factors lead to the following decisions about the synchronization in each table:
Table | Synchronization |
---|---|
CUSTOMERS | Download to remote only. |
ORDER_ITEMS | Download and upload. |
ORDER | Download and upload. |
PRODUCT_INFORMATION | Download to remote only. |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |