IBuySpy Delivery 2005 Application Design

  • Article

The IBuySpy Delivery 2005 application uses Microsoft SQL Server 2005, Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition), Microsoft Internet Information Services (IIS), and the Microsoft .NET Compact Framework. The application on the smart device provides a user interface for a delivery driver to enter and modify information. The application stores and retrieves this information in a local SQL Server Compact Edition database. The device communicates with SQL Server by using IIS, permitting data from the local database to be synchronized with the server database by using replication or remote data access (RDA). The IBuySpy Store Web site stores and retrieves information from the SQL Server database and lets a user to enter and view the synchronized information.

IBuySpy Company Scenario

IBuySpy distributes its products directly to consumers with a company-owned fleet of delivery trucks. Handheld devices are used in the delivery process to verify and record delivery quantities. They are a key component of the IBuySpy supply-chain process. The handheld systems run Windows Mobile 2003 Software for Pocket PC and SQL Server Compact Edition. When delivering an order to a customer, an IBuySpy delivery driver unloads the merchandise, and then records the customer's signature in the handheld device.

The IBuySpy Delivery 2005 application can download delivery data from a server onto a device, work with the data while disconnected from the server, and then synchronize the changes back up to the server when a connection is restored. Excess inventory in the delivery truck can also be sold during a delivery route. Order processing functionality is required on the handheld application in addition to inventory tracking and customer listings. Wherever a network connection is available, the driver's device communicates with the computer that is running IIS and SQL Server. The device sends the completed orders and receives any updates.

IBuySpy Delivery 2005 Web Site

The IBuySpy Store Web site is included with the IBuySpy Delivery 2005 application. You can create orders at the Web site. These orders are transferred onto the smart devices for the delivery drivers. On the Web site, you can also see the results of work performed on the device, such as viewing a signature submitted for an order, or products added to an order.

IBuySpy Delivery 2005 Database Schema

The IBuySpy Delivery 2005 application uses a modified version of the IBuySpy database schema to support additional features of the application. Besides the seven tables in the IBuySpy schema, which are Orders, OrderDetails, Customers, Products, Categories, ShoppingCart, and Reviews, the IBuySpy Delivery schema also includes the Settings table.

The IBuySpy Delivery 2005 application also modifies two tables and adds a foreign key relationship:

  • The Signature column is added to the Orders table to store bitmap images of customer signatures recorded by delivery drivers through their handheld devices. The Status column is added to track the delivery status of orders. This status value is used to determine whether there is excess inventory for additional point of delivery sales. For more information, see Inventory Control.
  • Six columns are added to the Customers table: Address, City, State, Zip, Phone, and DriverID. DriverID is used to determine which driver is scheduled to deliver a specific customer's order.
  • A foreign key relationship is added between the OrderDetails table and the Products table to ensure that orders are not created for products that do not exist.

The Settings table is not synchronized with the SQL Server data store. This table exists only on the handheld device and contains information necessary for the device to connect to the server.

The Reviews table is not downloaded to the device. Reading and entering reviews of products is of minimal relevance at the point of delivery. Not downloading the table minimizes the database size on the device. The ShoppingCart table is also excluded from the device because drivers cannot create new orders on the handheld application. They can add items only to existing orders.

The remaining five tables, Customers, Order, OrderDetails, Products, and Categories, are downloaded to the device. To keep database size on the device to a minimum, and to ensure that drivers see only relevant data, each delivery driver receives information specific only to his or her customers. During synchronization between the device and the server database, the DriverID column in the Customers table filters the information. The records in each table on the local database contain a targeted subset of the records in the whole database.