Designing Synchronized Applications
Page 4 of 7

Steps for Designing a Distributed System

Before creating the databases or writing code:

  1. State the purpose of the application.
  2. List the logical operations that the application will perform.

    Represents the application's work units

  3. For each operation, list the data that the application needs.

    Determines the source database design and the tables in each publication

  4. For each operation, list the data that an individual target needs.

    Determines the parameters passed to the publication

  5. List the inputs to each operation.

    Represents the parameters passed to the work units

  6. List the possible outcomes of each operation.

    Determines the status information stored in the database

  7. Describe how to represent each possible outcome in the database.

NOTE: If you can design your application so that no conflicts are possible, you may not need to perform steps 5, 6, and 7. If an update cannot cause a conflict, and if the only action you want to perform is updating the same data at target and source, you do not need to write a work unit for it.

Step 1: Defining the Application

This chapter uses a very simple toy sales order scenario. There are two sales representatives, each at her own target, and two types of toys available. Each salesperson does a refresh to see how many of each toy are in stock, and to get the latest price information. The salesperson places an order for a single item, entering the name of the customer, the item she wants, and the quantity. Each salesperson has access to her own orders only. The salesperson can also send a lead to another salesperson. The central office keeps track of valid salespeople, and updates from unapproved salespeople are rejected.

Please note that, for simplicity's sake, this application puts the source and target directories under the same directory on the same machine. A real deployed application would probably put the source database into a server framework.

Step 2: Listing the Operations

The two operations needed for this simple scenario are:

  • place an order
  • send a lead (the name of a potential customer)

You will write a work unit for each operation. The work unit code is shown in Work Unit Examples.

Step 3: Describing the System-Wide Data

This application requires the following data about each toy:

  • name of toy
  • unit price
  • quantity available

The application also needs the following data about each order:

  • customer name
  • name of toy
  • sales representative ID
  • status

Since salespeople (and people at the central office) can send each other leads, the application uses the following data about each lead:

  • the salesperson who receives the lead
  • the salesperson who sends the lead
  • the name of the customer

The application needs access to a list of valid salespeople to determine whether a lead is deliverable and whether the salesperson sending an update is valid. For security purposes, it would be useful to refuse update permission to a salesperson who has been let go. Therefore, the application maintains the following information about each salesperson:

  • the sales representative's ID
  • last name
  • first name

The TOYS, ORDERS, LEADS, and SALESREPS tables will contain this data. They are created as follows:

CREATE TABLE toys (
    toy VARCHAR(30) CONSTRAINT toys_pk PRIMARY KEY,
    price DECIMAL(5,2),
    quantity INT)

CREATE TABLE orders (
    orderID BIT VARYING(128) CONSTRAINT orders_pk PRIMARY KEY,
    salesRepID INT,
    customer VARCHAR(50),
    toy VARCHAR(50),
    quantity INT,
    status VARCHAR(15))

CREATE TABLE leads (
    leadID BIT VARYING(128) CONSTRAINT leads_pk PRIMARY KEY,
    sentToRepID INT,
    sentFromRepID INT,
    customer VARCHAR(50))

CREATE TABLE salesReps (
    salesRepID INT CONSTRAINT reps_pk PRIMARY KEY,
    lastName VARCHAR(20),
    firstName VARCHAR(20))

NOTE: See Designing Universal Keys for an explanation of the BIT VARYING column used as a primary key in some of these tables.

Step 4: Describing the Target Data

Each target is restricted to one salesperson's orders and leads. Therefore, the publication uses parameters to restrict each target accordingly.

The publication to create a target is:

CREATE PUBLICATION toySales (salesRepID INT)
ADD TABLE toys
ADD TABLE orders
    WHERE salesRepID = ?salesRepID
ADD TABLE leads
    WHERE sentToRepID = ?salesRepID
ADD METHOD ALIAS getKey
ADD WORK ALIAS placeOrder
ADD WORK ALIAS sendLead

NOTE: The getKey method alias is shorthand for the universal key generator described in Designing Universal Keys. The getKey method alias and the two work alias definitions are shown in the section Work Unit Examples.

Step 5: Listing the Inputs

The parameters in the placeOrder work unit are:

  1. a universal key for the orderID (see Designing Universal Keys)
  2. the sales representative's ID
  3. the customer name
  4. the toy name
  5. the number of toys ordered

The parameters to the sendLead work unit are:

  1. a universal key for the lead
  2. the ID of the salesperson to whom the lead is being sent
  3. the ID of the salesperson sending the lead
  4. the customer name

Step 6: Listing the Outcomes

This application represents provisional (pre-successful-refresh) outcomes differently from durable (post-successful-refresh) ones. This gives the user a better sense of the actual status of her request. The possible outcomes of placing an order are:

  1. Provisional
    • There appear to be items available, so we expect to order one.
    • There appear to be no items available, so we expect to back-order one.
    • We want to order a toy that is not carried.
  2. Durable
    • The item is available and has been ordered.
    • The item is not available and has been back-ordered.
    • The ordered item is not carried.

For simplicity, the application does not track the status of leads.

Step 7: Representing the Outcomes in the Database

The STATUS column of the ORDERS table represents the possible outcomes described in step 5. Since there are six possible statuses of an order, this column can have one of six values:

  • WILL ORDER
  • WILL BACKORDER
  • ORDERED
  • BACKORDERED
  • TOY UNKNOWN
  • TOY UNAVAILABLE

Although in this example the values in the STATUS column represent the outcomes of only one work unit, status values typically represent the outcomes of more than one work unit. For example, if there were also a cancelOrder work unit, STATUS might also permit the values WILL CANCEL and CANCELED.

Note also that there are many ways of representing status in a database. You do not necessarily need a STATUS column. You might instead have an EXPECTED_DELIVERY_DATE column that is assigned a later date if the product is backordered. Doing this would not overtly show the user whether an order is provisional or durable, but this may not be necessary in some applications.