Example Database Design for Data Synchronization
Example Database Design for Data Synchronization
Understanding Database Design Considerations for Data Synchronization
An effective way in which to understand how to design a database for Data Synchronization is to study an example. Although Pervasive DataExchange offers replication solutions for both backing up and synchronizing your databases, this chapter details an example Data Synchronization design using Tracker, the sample database provided with Pervasive DataExchange. We’ll use this database to illustrate the available functionality in Pervasive DataExchange Data Synchronization Edition.
This chapter discusses the following sections:
*
Data Synchronization Design Considerations
*
Basic Data Synchronization Design
*
Security
*
Sample Tracker Database
Data Synchronization Design Considerations
This section discusses database design considerations for creating a new database schema using Data Synchronization. These considerations can also be used for replicating an existing database. The sample database, Tracker, is used for this design.
Note If the Tracker database has been deleted from your computer you can recreate it with the sample Tracker.SQL script. See Sample Tracker Database.
Application Overview
A reseller company uses a tracking program, called Tracker, to store customer and product information and to track sales through purchase orders. The business rules are as follows:
*
The company has a number of geographically distributed sales regions.
*
Each employee of the company is associated with a specific sales region.
*
Employees can edit customer entries and assign customers to themselves.
*
Employees are responsible for creating purchase orders.
*
A single list of products is shared among all regions.
Based on the above business rules, the following database schema was used.
The Purchase and PurchDet tables help avoid many-to-many relationships. A single purchase can contain many purchase details, which are essentially line items on a single purchase order. That is, a purchase will exist for a given day, and all the items bought on that purchase order are listed in the PurchDet table.
The tables contain the following fields (asterisks denote primary key fields, double asterisks denote foreign key fields). Note that this is the first attempt at the structure, and changes can be made after considering design factors that could be desirable for replication.
Figure 6-1 Tracker Schema
Region Table
Field
Type
Description
RegionID*
Long Integer
Primary key for region table
NameStr
Char[100]
Name of the region
Employee Table
Field
Type
Description
EmpID*
Long Integer
Primary key for employee table
FName
Char[30]
First name
Initial
Char[5]
Middle Initial
LName
Char[30]
Last name
RegionID **
Long Integer
Index to the region name
Customer Table
Field
Type
Description
CustID*
Long Integer
Primary key for customer table
NameStr
Char[100]
Name of the company
Street
Char[100]
Street name
City
Char[30]
City name
State
Char[50]
State, province or county name
ZIP
Char[10]
ZIP or postal code
Phone
Char[20]
Phone number
CntEmail
Char[100]
E-mail address of the primary company contact
CntName
Char[50]
Name of the primary company contact
Comments
Char[255]
Any comments regarding the company
SlsRepID **
Long Integer
Index to the employee who works with the company
Purchase Table
Field
Type
Description
PurID*
Long Integer
Primary key for purchase table
PurDate
Date/Time
Date and time of purchase order
CustID **
Long Integer
Index to the name of the customer who ordered
EmpID **
Long Integer
Index to the employee who took the order
DelInst
Char[50]
Delivery instructions
Purchase Detail Table
Field
Type
Description
PurDetID*
Long Integer
Primary key for purchase detail table
PurchID **
Long Integer
Index to the purchase order
ProdID **
Long Integer
Index to the product ordered
PurchQty
Integer
Amount ordered
Cost
Char[12]
Total cost of the purchase detail
 
Product Table
Field
Type
Description
ProdID*
Long Integer
Primary key for product table
ProdName
Char[50]
Name of the product
ProdDesc
Char[248]
Description of the product
Price
Char[12]
Price of the product
Basic Data Synchronization Design
Pervasive DataExchange Data Synchronization Edition provides you the functionality to easily configure your database designs to mirror the distributed nature of your business. Your data synchronization design can be as simple or as complex as your data and business needs demand.
The DataExchange Deployment Wizard guides you through the design for data synchronization. The Deployment Wizard is a step-by-step wizard for deploying replication at First and Partner Sites and includes a means of creating a replication schedule. See Replication Deployment Using the Deployment Wizard.
In brief, a data synchronization design involves identification of the project, network, and release for the design and the data files that you want replicated.
Project, Network, and Release
A project represents a single replicated database and can be associated with more than one replication network. A replication network is the grouping of replication sites that will participate in the replication. The release is like a version number for the design of the replication database.
The project, network, and release are added to the system database used by the Replication Engine. The replication utilities use them to allow or disallow certain actions. For example, databases that differ only by release are allowed to replicate together. Replication project names and network names must match to allow replication.
Data Files To Replicate
The Deployment Wizard also allows you to select the data files that you want to replicate. Any data files not identified for replication are excluded from data synchronization.
Security
Pervasive DataExchange automatically encrypts all data transmitted between two sites. The Replication Engine controls the encryption for you.
For information on Pervasive PSQL Security, see What's New in Pervasive PSQL included with your Pervasive PSQL database engine.
Sample Tracker Database
If the Tracker database is not listed in Pervasive PSQL Control Center, you can create it from the file Tracker.sql. Tracker.sql is included in the Pervasive DataExchange installation at <Installation Directory >\Replication\Demodb). This file contains the SQL statements to recreate the Tracker demo database.
*
To create the Tracker database using Tracker.sql
1
Start PCC if it is not already running. (Select Control Center & Documentation from the operating system Start menu or Start screen.)
2
Right-click Databases and select New Database.
The New Database dialog box appears.
3
Fill in the options for the database name and the database directory as listed:
*
Database Name: Tracker
*
Location: C:\Program Files\Pervasive Software\PSQL\Replication\DemoDB
You may specify a different directory so long as the folder already exists and does not contain any Pervasive PSQL database files (.ddf or .mkd file extensions).
4
Click Finish.
The empty database is created and ready to run the SQL statements in Tracker.sql.
5
In PCC, select File Open.
An Open dialog box appears.
6
Browse to C:\Program Files\Pervasive Software\PSQL\Replication\DemoDB and open the file tracker.sql.
SQL Editor starts.
The system asks you to select a database in which to run the SQL statements.
7
Click OK.
SQL Editor displays the SQL statements in Tracker.sql.
8
Select SQL Execute All SQL Statements.
The statements create the Tracker database tables and populate them with data.
9
After all SQL statements have run, close SQL Editor.
10
Right-click Databases and select Refresh to redisplay the list of databases, including their tables.