Lesson 2: Preparing the consolidated database

This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.

This lesson guides you through the following steps to set up your SQL Anywhere consolidated database:

Connect to the consolidated database.
Create the CustomerProducts tables and alter the Customers table to include regional information.

Prepare the consolidated database

Click Start » Programs » SQL Anywhere 12 » Administration Tools » Sybase Central.
Click Connections » Connect With SQL Anywhere 12.
Perform the following tasks in the Connect window:
1. In the Action dropdown list, choose Connect With An ODBC Data Source.
2. In the ODBC Data Source Name field, type SQL Anywhere 12 Demo.
3. Click Connect.
Connect to your consolidated database in Interactive SQL.

At a command prompt, run the following command:
dbisql -c "DSN=SQL Anywhere 12 Demo"

In Interactive SQL, execute the following statements to create and insert data in the CustomerProducts table:

CREATE TABLE CustomerProducts
    (ID int default AUTOINCREMENT PRIMARY KEY,
    SalesOrderID int NOT NULL, 
    CustomerID int NOT NULL, 
    ProductID int);

INSERT INTO CustomerProducts (SalesOrderID,CustomerID,ProductID)
    SELECT SalesOrders.ID, SalesOrders.CustomerID, SalesOrderItems.ProductID
    FROM SalesOrders, SalesOrderItems
    WHERE SalesOrders.ID = SalesOrderItems.ID;

In Interactive SQL, execute the following statements to add regional information for each customer to the Customers table:

ALTER TABLE Customers
    ADD Region VARCHAR(255);

UPDATE Customers
    SET Region = (SELECT TOP 1 SalesOrders.Region
    FROM SalesOrders 
    WHERE Customers.ID = SalesOrders.CustomerID
    ORDER BY Region);

Adding unique primary keys

In a synchronization system, the primary key of a table is the only way to uniquely identify a row in different databases and the only way to detect conflicts. Every table that is being mobilized must have a primary key. The primary key must never be updated. You must also guarantee that a primary key value inserted at one database is not inserted at another database.

In a later lesson, the remote schema is created from the consolidated schema. This means that the remote schema has the same primary keys as the consolidated schema.

Columns were specifically chosen to ensure unique primary keys for all databases. For the Customers table, the primary key consists of the ID column. Any value inserted into the remote Customers table must have a unique customer ID number (the Region value is always the same). This practice ensures uniqueness in each remote Customers table. The primary key in the consolidated Customers table prevents conflicts if multiple salespeople upload data. Each upload from a region is unique from another region because their Region values are different.

Proceed to Lesson 3: Creating a synchronization model.