Tables in the Contact databases

The table definitions for the Contact database are located in the following files, all under your samples directory:

MobiLink\Contact\build_consol.sql
MobiLink\Contact\build_remote.sql

Both the consolidated and the remote databases contain the following three tables, although their definition is slightly different in each place.

SalesRep

Each sales representative occupies one row in the SalesRep table. Each remote database belongs to a single sales representative.

In each remote database, SalesRep has the following columns:

rep_id A primary key column that contains an identifying number for the sales representative.
name The name of the representative.

In the consolidated database only, there is also an ml_username column holding the MobiLink user name for the representative.

This table holds one row for each customer. Each customer is a company with which a single sales representative does business. There is a one-to-many relationship between the SalesRep and Customer tables.

In each remote database, Customer has the following columns:

cust_id A primary key column holding an identifying number for the customer.
name The customer name. This is a company name.
rep_id A foreign key column that references the SalesRep table. Identifies the sales representative assigned to the customer.

In the consolidated database, there are two additional columns, last_modified and active:

last_modified The last time the row was modified. This column is used for timestamp-based synchronization.
active A BIT column that indicates if the customer is currently active (1) or if the company no longer deals with this customer (0). If the column is marked inactive (0) all rows corresponding to this customer are deleted from remote databases.

Contact

This table holds one row for each contact. A contact is a person who works at a customer company. There is a one-to-many relationship between the Customer and Contact tables.

In each remote database, Contact has the following columns:

contact_id A primary key column holding an identifying number for the contact.
name The name of the individual contact.
cust_id The identifier of the customer for whom the contact works.

In the consolidated database, the table also has the following columns:

last_modified The last time the row was modified. This column is used for timestamp-based synchronization.
active A BIT column that indicates if the contact is currently active (1) or if the company no longer deals with this contact (0). If the column is marked inactive (0) the row corresponding to this contact is deleted from remote databases.

Product

Each product sold by the company occupies one row in the Product table. The Product table is held in a separate publication so that remote databases can synchronize the table separately.

In each remote database, Product has the following columns:

id A primary key column that contains a number to identify the product.
name The name of the item.
size The size of the item.
quantity The number of items in stock. When a sales representative takes an order, this column is updated.
unit_price The price per unit of the product.

In the consolidated database, the Product table has the following additional columns:

supplier The company that manufactures the product.
last_modified The last time the row was modified. This column is used for timestamp-based synchronization.
active A BIT column that indicates if the product is currently active (1). If the column is marked inactive (0), the row corresponding to this product is deleted from remote databases.

In addition to these tables, a set of tables is created at the consolidated database only. These include the product_conflict table, which is a temporary table used during conflict resolution, and a set of tables for monitoring MobiLink activities owned by a user named mlmaint. Scripts to create the MobiLink monitoring tables are in the file %SQLANYSAMP12%\MobiLink\Contact\mlmaint.sql.

Discuss this page in DocCommentXchange.