Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 » MobiLink - Getting Started » MobiLink Tutorials » Tutorial: Using MobiLink with an Oracle 10g consolidated database


Lesson 1: Design the schemas

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.
Designing the remote schema

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.